Inadvertent macro

[Dean]

I have come to love these buttons you can make for users, so that they can easily run a macro. However, I have noticed that it is all too easy for some users to inadvertently put one's mouse button over the button and run a macro that one didn't mean to.

Can someone tell me the code to copy into the beginning of each macro so that it will ask the user if he or she really wants to run this macro? If the user chooses "No" it will not run the macro.

Thanks much!
Dean

 

[Guest]

sub DoStuff

if msgbox("do you wnat to do stuff?", vbyesno, "Do Stuff?) = vbno then
msgbox "Stuff was not done"
else
'do your stuff
end if

end sub

 

[Dean]

Sorry for my novicity (is that a word), but I need a little more guidance,
please (and thank you). By writing this as a subroutine, you seem to be
suggesting that its text doesn't need to be copied into every macro, which
would be nice. If you didn't mean to imply that, please let me know.

If you did mean to imply that, then I assume the 'do your stuff statement
really needs to be some way for the Do Stuff subroutine to hand control back
over to the subroutine that calls it (assuming the user chooses to
continue). Can you rewrite your macro to do that? Perhaps it's some
statement like "return" or continue (with calling subroutine).

Also, can you please be careful to get all spaces and syntax exact as I
would like to be able to copy your text in directly.

Thanks so much! Despite the holiday, I am not quite "independent" from
needing trivial help, yet!

Dean

 

[Guest]

The code that I posted is intended to be added to the code for each button
that you have. (You can tailor the message box to the specifics of each
button this way if you wish.) Right now (I assume that you are attaching the
button to code which you have stored in a module) your button is attached to
say "Macro1". Take the code that I posted and insert it into "Macro1"...
something like this...

sub Macro1

if msgbox("do you want to do stuff?", vbyesno, "Do Stuff?) = vbno then
msgbox "Stuff was not done"
else
'Your existing macro1 code...
end if

end sub

 

[Chip Pearson]

Dean,

Try some code like the following at the top of each sub:

If MsgBox("Are You Sure?",vbYesNo) = vbNo Then
Exit Sub
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


I have come to love these buttons you can make for users, so that
they can easily run a macro. However, I have noticed that it is
all too easy for some users to inadvertently put one's mouse
button over the button and run a macro that one didn't mean to.

Can someone tell me the code to copy into the beginning of each
macro so that it will ask the user if he or she really wants to
run this macro? If the user chooses "No" it will not run the
macro.

Thanks much!
Dean

 

[Dean]

Thanks Chip (and Jim). That works really well for a simple mind like mine!

Dean