Adding a dialogue box/prompt to a macro assigned button

[Clarkit]

Hello all,

First off, thanks for helping out up front! Gratitude.

So, I have a few buttons in my excel template that are assigned macros. These buttons can do dangerous things if accidentally pressed. So as a fail safe I would like a warning prompt/dialogue box to pop up when these buttons are pressed asking "Are you sure you want to do that?". Then I'd like to be able to click "Continue" or "No". The macros in question are named "ClearAQData" and "RefreshData" without the quotes.

Also, if this is not possible, is it possible to change the color of the buttons so they look more scary?

Any suggestions?

Thanks!

[jonboy6257]

I would try to call a message box and then if the user presses ok to continue call the function. So when the button is called maybe add:

Please Login or Register  to view this content.

Of course change it as you need with messages etc.

[Clarkit]

Hello,

Thanks for giving me something to try. I put your bit of code at the top of my macro in VBA, and I'm not sure if that was how you intended on me using it. I received a syntax error/compilation error.

Thanks for your help!

Please Login or Register  to view this content.

[jonboy6257]

I would place it on the button click then place your refresh data in its own function. Once the button is clicked it will call the confirmation method and then if the user selects ok to continue, the if statement will call the refresh query. If you have a sample workbook you can upload I can try and insert it for ya. Happy New Year

[Clarkit]

Hello,
I tried to do what you suggested, but I'm not sure exactly how the Call function works. Here is the template I'm working with:
https://dl.dropboxusercontent.com/u/...functions.xlsm

When I assign the function to the button, and run it the code hangs up and gives an error on the strPrompt = "Message here". I shows up in red in VBA

Please Login or Register  to view this content.

Thanks

Edit: Happy New Year!

[jonboy6257]

Okay, I changed it a bit because I added an active X button and assign the checking function to it. It then asks the user to click ok or cancel. I added message boxes just to verify they work but once the user clicks on the button, it calls the verify function. Then once that is done it calls the ClearAQ Module/Function. I have attached the sheet. You may need to change appropriately.

[Clarkit]

Okay, I changed it a bit because I added an active X button and assign the checking function to it. It then asks the user to click ok or cancel. I added message boxes just to verify they work but once the user clicks on the button, it calls the verify function. Then once that is done it calls the ClearAQ Module/Function. I have attached the sheet. You may need to change appropriately.

Oh, OK. That's pretty cool. I have never used the ActiveX functions before. I'm actually going to change the function/call to the RefreshData macro, as that is the function which clears both tabs (which is the most dangerous). I'm assuming I can just delete the old button and exclusively use the ActiveX one you created for me.

You sir, are a virtual Super Hero. Thank you! Happy New Year!

[jonboy6257]

Yea you can delete the old button. Then click design mode and insert. Insert allows you to insert a lot of form controls. Then the buttons button_click event can call your functions etc.

[Clarkit]

I'll have to look more into those functions sometime.

Thanks again!