VBA - make form show up for USER change

[cmhoz]

Hi All....

I tried to ask this yesterday, but I think I explained myself very badly.

I have a cell - we'll call it A1.

Sometimes a USER will change the value in this cell.
Sometimes a MACRO will change the value in this cell.

I need to allow BOTH to happen and I cannot use 2 different cells as my zillion IF statements have already caused this file to be very large.

When a USER changes the value in this cell, I need a userform to appear that requires the user to read a message and confirm the change they've made.

When a MACRO changes the value in this cell, I do NOT want the user to have to deal with the previously mentioned form.

Currently - I have a macro that says: if A1 changes, show the form.

This is fine.. but it means the user still has to deal with the form when the MACRO has changed A1.

What I would like to do is tell the macro that changes A1 to click 'confirm' on the form that appears.

Can I do this?

 

[Trebor76]

Hi cmhoz,

Tricky one here as Excel is doing exactly what you've asked it to do.

One solution may be to have your macro firstly populate a currently unused cell (say B1) with a "Y" flag, finish running the rest of the code and then change the value in B1 back to a "N" flag. You'll then need to change your Worksheet_SelectionChange event (or however you're calling the code to run when the tab in question changes) to only open if the value in cell A1 changes and the value in B1 is equal to "N" (i.e. a user changed the value in A1 not the macro).

HTH

Robert

 

[robert phillips]

I would use a global variable (FLAG).

In the module declarations:
Public MacroCall as Boolean


In The MACRO That Changes The Cell Value:

YourMacro()
.....
Whatever preprocessing code you run before changing the cell value
.....

'SET THE FLAG FIRST
MacroCall - True

'Then Change The Cell Value
Sheets("YourSheetName").Range("A1").Value = SomeNewValue

....
Whatever postprocessing code you run after changing the cell value
....
End Sub


In Your CellChangeDetection Macro():

If MacroCall then
'RESET FLAG FOR NEXT MACROCALL
MacroCall=False
Else 'It was a user change
UserForm.Show
End if

'Do Whatever You Normally Do
...
...
...
End Sub

 

[cmhoz]

Thanks Guys... I will have a look at both options!

 

[cmhoz]

The 'yes / no' flag in another cell works perfectly... this will be stored away for future use... so simple and yet never ocurred to me!

thanks!

 

[Trebor76]

Glad it helped and thanks for the feedback

robert phillips' solution looks pretty nifty too