Changing the Character of a Confirmation

EE Pros, I have a very nifty data store and retrieve macro that displays a msgbox when it is fired.  What I'm curious about is, is there a way to use a shape or text box instead of a msg box so I can control the size, shape, location, color, etc. of the confirmation?  Also, can I get it to show confirmation, wait 4 seconds and then disappear?

I've attached the WB that runs the macro for your review.

Thank you in advance.

B.
ConfirmationBox.xlsm
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
I can't download a macro enabled file at the minute but the simple answer is yes you can.....

Draw your shape/text box and format as required. With the object selected there will a Drawing Tools tab on the Tool bar, one of the options on that ribbon is Selection Pane. Click that and a pane will show at the right hand side showing all objects and their visibility status (the eye symbol next to each). Clicking the eye will show/hide so you can record a line of code with the VB Recorder to show and hide the object.

I am not sure of the syntax for the timer bit, but you can set a loop running that records the start time and loops until the current time is 4 seconds later than the start time and then continue.
0
Rob HensonFinance AnalystCommented:
Downside, the shape would be static on the sheet so would rely on the section of the sheet being visible and positioned correctly for the shape to show where you are expecting it.

Another option would be a User Form.

In the VB Editor, use the Insert menu to insert a User Form into the workbook. You can change the format of the User Form (size, shape, colour etc, add pictures if you want) and you can specify where it appears on screen and it is not dependent on the sheet position.

The VBA for making the user Form appear is then very simple:

UserForm1.Show
Timer
UserForm1.Hide

If you change the name of the User Form, adjust the VBA accordingly.
0
Bright01Author Commented:
Rob,   Thanks for jumping in.  Static is ok because it will still be confirmation and I can place it in the right place.  I do not know how to use the VB Recorder.  Any chance you could doctor up my attached file?

B.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
Can you do the design work for the shape and upload a new file?

I can then look at it and apply the macro but won't be until back home this evening.
0
Bright01Author Commented:
Rob,  Did you take a look at the model I sent?  If you fire off the macro you will see the Message Box appear.  That's what I'm  trying to replace with something more flexible and better.   If you replace it with a shape and put the timing in, with a designated color, I can use that.

Hope that helps.

B.
0
Rob HensonFinance AnalystCommented:
Did you read my first comment? I can't download xlsm at the moment.

I will be able to look later this evening so thought you could design the "message" how you want it and I can then do the macro for hiding/showing your message.
0
Bright01Author Commented:
Rob,

Very sorry.  I thought it to be a moment thing. I'm more then happy to wait for you when it is more appropriate for any help.

I could mock up a design but it's already in the example WS and is already embedded in the Macro that Roy Cox helped me create.

I'll look forward to hearing back from you when it's appropriate.

B.
0
Ejgil HedegaardCommented:
Really a problem not to be able to download xlsm files.

No problem for me, so here is a method using a simple userform with only a Label, where the colours change for the 2 types.
And the userform is displayed for 4 seconds.
ConfirmationBox.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
I was at work and our firewall does not allow download of xlsm for security reasons.
0
Rob HensonFinance AnalystCommented:
Had issues this evening but got there in the end.

See attached with Shape that appears for 4 seconds. You will need to check the data as it will have got messed up with flicking back and forth between the two cases.

If you want to change the format of the message, right click on your macro button to get the Drawing tools tool-bar to show. on that tool-bar there is the Selection Pane option which will show the "Message" shape; use the eye icon next to it to make it visible. Once formatted and positioned as required use the eye icon again to hide it.
ConfirmationBox.xlsm
0
Bright01Author Commented:
Rob and Ejgil,

Thank you guys for the code!  Here's where we are;

Rob, I could not get the macro to work.  Firing the macro sent it into some kind of loop where nothing changed but it was processing something.

Eigil, Your forms approach worked but this needs to be relative to a Shape not a Form.  The form adds another degree of complexity.

Hope to hear from you guys later today.  No hurry.

B.
0
Bright01Author Commented:
Hey guys, I am using the code but am posting another question.  I think it would be simpler to have text and color and timing as simply posting it to a cell instead of a form or a shape.

Hope one of you will pick it up.

Thanks again for the help!

B.
0
Rob HensonFinance AnalystCommented:
The code worked fine on my end; don't know why it wouldn't work for you.
0
Ejgil HedegaardCommented:
I tried the sheet, and also got into an endless loop.
If unlucky this is never true
Loop Until Time = MyTime + TimeValue("00:00:04")
Change both statements to
Loop Until Time > MyTime + TimeValue("00:00:04")
0
Rob HensonFinance AnalystCommented:
I agree with the logic, I must have been very lucky every time I tried it; maybe I should buy a lottery ticket as well!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.