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

Some videos you may like

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
  •