Changing out a Cell with Text for a msg confirmation

EE Pros,

I'm simply trying to change out a msgbox reference for confirmation of data being saved and restored to an Input WS to a Data WS to a cell reference.  I've attached a complete mockup to include the major macro that maps the data to the two WSs as well as issues the msg.

Thank you in advance.

B.
Data-Store-Master.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.

NorieAnalyst Assistant Commented:
Try this.
Sub CopyRestore()

''This Sub copies the initial basecase and retrieves it from the datastore.

Const sTitle1 As String = "Retrieve Initial Case"
Const sTitle2 As String = "Save Initial Case"
    ''///INPUTSHT is the code name for the worksheet named Input_Screen.
    ''///DATASHT is the code name for the worksheet named Data_Store.
    ''///the sheets can referred to by using the Code Names.
    ''///change the sheet code names in their Properties Tab as you would any Object

    INPUTSHT.Shapes.Range(Array("Shape Save Retrieve Initial Case")).Select
    ''/// determine action based on "button's" text
    Select Case Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text
        Case sTitle1
            ''///INPUTSHT.Range("Case_Initial") refurs to the named range on Inut_Screen
            With INPUTSHT.Range("Case_Initial")
                .Cells(1).Value = DATASHT.Range("Case0").Cells(1)
                .Cells(2).Value = DATASHT.Range("Case0").Cells(2)
                .Cells(4).Value = DATASHT.Range("Case0").Cells(3)
                .Cells(6).Value = DATASHT.Range("Case0").Cells(4)
                .Cells(10).Value = DATASHT.Range("Case0").Cells(5)
                .Cells(11).Value = DATASHT.Range("Case0").Cells(6)
                .Cells(12).Value = DATASHT.Range("Case0").Cells(7)
                .Cells(13).Value = DATASHT.Range("Case0").Cells(8)
                .Cells(15).Value = DATASHT.Range("Case0").Cells(9)
                .Cells(18).Value = DATASHT.Range("Case0").Cells(10)
                .Cells(20).Value = DATASHT.Range("Case0").Cells(11)
                .Cells(21).Value = DATASHT.Range("Case0").Cells(12)

            End With

            With INPUTSHT.Range("Case0Profiles")
                .Cells(1).Value = DATASHT.Range("Case0").Cells(13)
                .Cells(2).Value = DATASHT.Range("Case0").Cells(14)
                .Cells(3).Value = DATASHT.Range("Case0").Cells(15)
                .Cells(4).Value = DATASHT.Range("Case0").Cells(16)
                .Cells(5).Value = DATASHT.Range("Case0").Cells(17)
                .Cells(6).Value = DATASHT.Range("Case0").Cells(18)
                .Cells(7).Value = DATASHT.Range("Case0").Cells(19)
                .Cells(8).Value = DATASHT.Range("Case0").Cells(20)
                .Cells(9).Value = DATASHT.Range("Case0").Cells(21)
                .Cells(10).Value = DATASHT.Range("Case0").Cells(22)
                .Cells(11).Value = DATASHT.Range("Case0").Cells(23)
                .Cells(12).Value = DATASHT.Range("Case0").Cells(24)
                .Cells(13).Value = DATASHT.Range("Case0").Cells(25)


            End With

            Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = sTitle2
            ''/// clear entries
            'DATASHT.Range("Case0").ClearContents
            Range("F8").Value = "Success, initial Case Restored!"

        Case sTitle2
            INPUTSHT.Range("Case_Initial").Copy
            DATASHT.Range("Case0").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                         :=False, Transpose:=False
            INPUTSHT.Range("Case0Profiles").Copy
            DATASHT.Range("Case0").Cells(13).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                                   :=False, Transpose:=False
            Application.CutCopyMode = False
            Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = sTitle1
            ''/// clear entries
            'INPUTSHT.Range("Case_Initial").ClearContents
            Range("F8").Value = "Success, initial Case Stored"
    End Select
    ''/// remove selection from button
    INPUTSHT.Range("A1").Select
End Sub

Open in new window

0
Bright01Author Commented:
Norie,  Almost!  I added Application Screen Updating = False to stop the flickering which worked.  I need you to add several lines that control how long the text remains before disappearing.  In other words, the confirmation shows up, stays for 4 seconds and then clears the cell.

That's it!  Great work.

B.
0
NorieAnalyst Assistant Commented:
I can't recall seeing any code in the workbook that did what you described.

Yep, just checked - I had to manually dismiss the message boxes.
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.

Bright01Author Commented:
Norie,

Here is the initial sample I was trying to incorporate into the larger Macro that you did.  You can see how it was constructed to give it time to render then vanish.  I used Conditional Formatting on yours to change the color.
Macro-for-Displaying-Text-in-cell.xlsm
0
Roy CoxGroup Finance ManagerCommented:
It's not actually a message box, it displays text in the sheet. I don't think you can auto dismiss a messagebox. You could add a TextBox and delete it.

Is this part of what we were working on at the weekend. I used a msgbox but the user would need to dismiss it.

I'll add an example later
0
Bright01Author Commented:
Roy,

Good morning.  Answering your question, "yes".  I was trying to replace the message "confirmation box" with a text output to a particular cell, have it wait 4 seconds and disappear.  It's a minor issue but like a hangnail, I thought it would be easy to fix.  I sent Norie a WS that has a sample of the text notification with the timer, but don't know how to incorporate it into the code from the posted WB .... the WB that transfers the data from the Input Screen to the  Data Store.  I'm reattaching the WB with Norie's code added.  All I need is the timer that changes cell F8 to blank after 4 seconds.

B.
Data-Store-Master.xlsm
0
Roy CoxGroup Finance ManagerCommented:
Try this. You can call the "Main" macro within any code
Macro-for-Displaying-Text-in-cell.xlsm
0
Bright01Author Commented:
Roy,

I incorporated your + Norie's code into the Master WB and I have one problem.  Fire the macro several times on the "Save/Restore Button".  You will see the first pass works perfectly.  However, upon the 3rd "firing" you will see a rather bad flicker.  I think it's simply a tweek in the code somewhere that will eliminate the flicker.

I've attached the mocked up model with the code embedded.

Thank you and the team for troubleshooting this.

B.
Data-Store-Retrieve-v1.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I can't see any code except mine in there.

I ran it several times. I did briefly see the other data but I've added code to switch off screen updating.

The code will need some changing looking at how you store the data. Maybe we should discuss how this project is going to develop. I might be able to save some time if I know what your aims are.
Data-Store-Retrieve-v1.xlsm
0
Bright01Author Commented:
Roy, that's probably a good idea.  This project is over my head beyond the confirmation aspect.

What's your availability and rate?  

BTW;  You did stop the flickering, but now the confirmation doesn't appear.

B.
0
Roy CoxGroup Finance ManagerCommented:
I think this works now.

I wasn't touting for business, I just wanted to have an idea what you are working towards. I believe you can post a project on EE and specify you want me to accept. I.m around more at the weekend, just bout to finish for today.
Data-Store-Retrieve-v1--1-.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
Roy CoxGroup Finance ManagerCommented:
I'm going to have a look at tidying your amended code up today, I'm not really sure that the Named Ranges are much help based on the changes you are making. I'll take a look at make some suggestions.
0
Bright01Author Commented:
Roy...before you do that..... let me get the latest copy to you.  Can you tell me how to tunnel directly to you?  Really appreciate your help.

B.
0
Bright01Author Commented:
Norie, thank you for getting this started!  Great job in getting it moved along.  Roy, thanks for picking Norie's work up and tweeking it to the model.

Great job; both of you!

B.
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help. I don't think it's allowed to direct questions to individuals, you can gigs though.

If you post a question I'll take a look.
0
Bright01Author Commented:
Not familiar with "gigs".  I need to add to the model to give you a broader picture.  I should have a better model for you later today.  I got the notifications working thanks to you and Norie.
0
Roy CoxGroup Finance ManagerCommented:
No problem. I'll watch out for it.
0
Bright01Author Commented:
Hey Roy, is there any way to keep the second firing of the macro from disappearing the shape?  Cycle through it a couple of times and you will see what I'm talking about.  The shape disappears for a split second and then returns.

B.
Data-Store-Retrieve-v2.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I'll take a look.
0
Roy CoxGroup Finance ManagerCommented:
The only thing that I see is the message changes as I would expect
0
Roy CoxGroup Finance ManagerCommented:
I tidied up the code so that there is less by adding a Function to show the Confirmation Box. Also, the the box now shows to the right of the button so it is more noticeable.

I've removed unnecessary code.
Data-Store-Retrieve-v2.xlsm
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.