Cancel a message box pop up action

[duugg]

Hello all,

I have this awesome code below created by mrice (thanks again mrice!) from an earlier post. Currently, after the states I choose are entered in, the options that the message box brings up are "OK" and "Cancel".

An issue that I hadn't foreseen at the time is the following...

If I press either the "OK" or "Cancel" button without entering any states in (because I changed my mind and want ALL states in there, or I pressed either button by accident), the macro eliminates ALL the rows because it needs criteria.

Can this code be modified so that the message box does the following...

1. If I press the "OK" button but I haven't entered in any criteria in the message box, bring up another message box saying "you have not entered in any states in" which brings up an "OK" box and brings you back to the original message box.

2. If I hit the "Cancel" button, bring up a message box that says "No states will be removed" with an "OK" button, essentially cancelling the "State" macro portion of the sub, but continuing with the rest of the code in the sub if there is any.

I would like to have this message box work this way even if states were entered in the input box but the user pressed the "Cancel" button anyway.

3. Create a NEW button named "Keep ALL States" which essentially does the same thing as the "Cancel" button. (I'm trying to "idiot proof" this ).

Thanks much!

Please Login or Register  to view this content.

[Leith Ross]

Hello Duugg,

The easiest way to accomplish what you want is to create a UserForm to replace the InputBox. It will also look more professional and allow you to easily customize the user interface. Difficult at best with system dialogs.

[duugg]

Hi Leith,

I haven't a clue as to how to do that yet. I guess that's the next thing to learn as I have a few things to do with these forms.

Thanks

[Phil_V]

Requirements 1 & 2 can be achieved straight off with the code below.
Requirement 3, (in the form of a custom button), would need a form creating. However in the code below, I have modified the message of the inputbox to inform the user that if they want to keep all the states then they should click Cancel, which is a 'reasonable' workaround if you want a quick fix.

Please Login or Register  to view this content.

[duugg]

Phil,

You are the hero once again!

I will eventually get this onto a form (one of my very next projects to learn in VBA). I'll probably play around with the code you gave me from another post. This is Puurrrfect though, but, yes, it appears that forms would look better, which I like anyways because I am a big believer in "GUI" to the end user. I love playing around in Powerpoint so I know I'll like forms.

But...in the meantime, this seems to be the perfect solution until that happens. I'm coming up with an awesome example to give you on my "combine worksheets" macro and I think it will answer all questions.

Love the code here!

Thanks again Phil!