Search code examples
vbaexcelworksheet-function

call worksheet event change when user doesn't deselect cell


I have a very simple Worksheet_Change event running so that if any cell on the worksheet changes a cell on another sheet changes to 1. I am using that cell as a flag to know if any changes have been made to the worksheet. I have a shape that I assigned a macro to when selected. The macro checks to see if any changes have been made (if that cell is set to 1), and if it is my code fires.

The problem is if for example

  • cell A1 contains the name Bob.
  • A user selects cell A1 and changes the name from Bob to Steve
  • but then instead of first clicking out of the cell and then clicking the shape they directly hit the shape without deselecting the cell.

The problem with this is that the worksheet_change event doesn't trigger until after my macro completes. So while the macro runs the flag is set to 0, but once it's done the flag gets set to 1.

Any ideas on how to stop this?

Private Sub Worksheet_Change(ByVal Target As Range)
    sheets("Cond For").Range("A1").Value = 1
End Sub

Sub saveData()

if sheets("Cond For").Range("A1").Value = 1 Then
    'my code
End if

End Sub  

Solution

  • Use two routines like so (rename Sheet1.Reallysave to your sheet's codename and routine name):

    Sub SaveData()
    Application.Ontime Now, "Sheet1.ReallySave"
    End Sub
    
    Sub ReallySave()
    'Your current code
    End Sub