okay, this is my last question
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: okay, this is my last question

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    What is the correct way to call a macro when an empty cell is no longer empty?

    In cells C6:C10 and C13:C40 are empty right now, what I need to do is call my macro when one of those cells is no longer empty. I've tried using:
    If Target.Address <> "C6:C10" Then Exit Sub
    If Target.Address <> "C13:C40" Then Exit Sub
    If Target.Address <> "" Then Call MacroName
    And I've tried using:
    If Cells("6,C") <> "" Then Call MacroName
    So I don't use up space I did the above for each cell that is currently empty.

    I used this code to call a macro before:
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$G$11" Then Exit Sub
    If UCase (Target.Value) "Word" Then Call MacroName
    Which worked well as long as I entered the specified word in the specified cell. But I don't want the user to have to enter the data and then enter the word to get the macro to activate. I prefer to have the macro to be called as soonafter the new data is entered.
    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Viper

    Try this simple code, it will only run IF the cell that is changed WAS empty AND is within the range C6:C10,C13:C40



    Dim RWatch As Range
    Dim strWatch As String

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C6:C10,C13:C40")) Is Nothing Then Exit Sub
    If Not RWatch Is Nothing Then MsgBox "L"
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set RWatch = Nothing
    If IsEmpty(Target) Then Set RWatch = Target
    End Sub


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sorry Tab,Enter always messes me up.

    Thanks for the code it worked great. But as I was trying it out and then cleared the data I realized that I need the macro to activate when any cell that is occupied becomes empty. I can extend the range in the code to also include the cells that are presently occupied but like I said I didn't think about it but I do need the macro to activate when any of the cells become vacant again.

    Different subject: Is there a limit to how many worksheet open events?

    I currently have one that disables the Tool>Macro and then another one that displays a splash screen(when this spreadsheet goes company wide I want my 15 min., don't worry I have added all the help I have gotten from this board and you people (just so you get yours too))but anyway, after starting my file it takes it a long time to display the splash screen. I have the userform.show code first before the other but is there any way to increase the opening?

    Thanks,

    I appreciate the help from everyone at Mr. Excel.

    viper

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com