Disable button until cell edition is finished?

[zico8]

hI,

I have a file with data matrix table in one of the sheets.
There is also button that does the calculation and presents the report placed in other sheet, but only if user change some data in matrix (recalculation takes some time, so I do not want to do it, if no changes in data matrix have been done).

So I have created Worksheet_Change event in matrix sheet with setting 'matrix_update' variable to TRUE

Private Sub Worksheet_Change(ByVal Target As Range)    
     matrix_update = True
End Sub

The macro assigned to the button checks out at first if variable 'matrix_update' is sets to TRUE.
If yes - then do the calculations.

Sub do_calculations()    
     If matrix_update = True Then
          'my macro
          'my macro
          'my macro
          matrix_update = False
    End If
End Sub

It works good, but...
If the user change some data in matrix, without accepting this by Enter key (or other), the variable 'matrix_update' is not changed to YES and the macro do not do the calculations. But the data has been changed.

How to handle this?
Maybe I am able to disable button if the cell is in edition mode?

 

[Jaafar Tribak]

If the user change some data in matrix, without accepting this by Enter key (or other), the variable 'matrix_update' is not changed

I am not sure I understand.

Are you saying that the data changes even when the cell is still in edit mode ?!

 

[zico8]

I mean that if you are change some value in data matrix and press the button (without accepting changed value by pressing enter) the macro assigned to the button is executed firstly and the Worksheet_Change event starts when it finish.
So when my macro assigned to the button is going the matrix_update is still as FALSE.
It will change to TRUE but this is to late.

 

[Jaafar Tribak]

Ah I see.

I never ran into a similar problem.

One simple fix is to brievly delay the 'do_calculations' macro ... something along these lines :

Sub Button1_Click()

    Application.OnTime Now, "do_calculations"

End Sub


Sub do_calculations()
     If matrix_update = True Then
          'my macro
          'my macro
          'my macro
          matrix_update = False
    End If
End Sub

Where Button1 is the name of the button that you originally had the macro attached to... Change Button1_Click to suit.

 

[zico8]

Hi Jaafar Tribak,
it looks like it works!
But I do not really know how

I used some macros with Application.OnTime with added period of time but never with Now only.

Could you please tell something what are the steps in this case? The macro does the pause to finish all macros launched in background and then come back to finish it?
Are there any dangerous - for example slower machines that can mess something?
I need to be sure that the summary presents everything, what has been changed (even if it was not accepted by pressing enter key) and what the user will se when come back to the data matrix.

 

[Jaafar Tribak]

The Ontime call ensures that the worksheet_change event code is executed before the "do_calculations" macro is ran therefore the matrix_update flag should always evaluate to True regardless of whether the user pressed the enter key or not.

Here is the code execution flow:

1- User edits the cell and leaves it in edit mode.
2- User clicks the button.
3- Code in the button Click macro is executed.
4- worksheet_change event code is executed.
5- do_calculations code is executed.

I used some macros with Application.OnTime with added period of time but never with Now only.

You don't need to add a period of time to the OnTime Method in our scenario... When requesting the execution of a macro via OnTime, the macro is not executed until the caller routine exits regardless of the set delay.

 

[zico8]

OK,

Now it is clear for me,

Many, many thanks Jaafar Tribak for your help!