Disable MsgBox permanently after "OK"

[cizzett]

OK excel guru's, I have a MsgBox that runs when I open the workbook template telling the user to paste previous days data in the prev day worksheet before proceeding.

What I want is to permanently disable this MsgBox after selecting OK so once the user has saved the file under the new name it no longer pop's up this message.

In other words, I only want the message to run when the template is used to create a new workbook and after the user clicks OK it no longer shows the MsgBox.

Any Idea's?

Here's the MsgBox I'm using

Private Sub Workbook_Open()

MsgBox "Please paste previous days data into the Prev Day sheet before proceeding", vbOKOnly + vbExclamation, "Before Proceeding"


End Sub

 

[Joe4]

If you can define a condition on which it should run, you can make the MsgBox conditional.

So, what we would be looking for?
A file containing a specific name?
Something else?
If you can define the condition for us, we can probably help you build it into your code.

 

[cizzett]

Hmmm, Not sure what to use as conditions.

Basically this is a template that the user opens, pastes in the previous days data, then the workbook is script driven to draw new data from a GUI script. It then aligns, cross references the information and preps the workbook for distribution.

After the workbook has done its thing the user saves the workbook in a file they keep daily for quality control tracking.

Currently the message is in the VBA sheet for This Workbook to launch upon opening which works great but after that initial opening I dont need the MsgBox anymore because they will have already added the data the box is intended to remind them of.

When they first open it the Sheet "Prev Day" would be blank in cell A2, maybe that could be the key?

 

[Joe4]

Another option is to have a cell that tracks when the macro was last run.
So, have the VBA code update the cell, and check the cell, i.e.

Private Sub Workbook_Open()

'   Check to see if macro has already been run today
    If Sheets("Sheet1").Range("Z1") = Date Then
        Exit Sub
    Else
'       Prompt user
        MsgBox "Please paste previous days data into the Prev Day sheet before proceeding", vbOKOnly + vbExclamation, "Before Proceeding"
'       Update tracking date
        Sheets("Sheet1").Range("Z1") = Date
'       Save workbook (to make sure date update is saved)
        ActiveWorkbook.Save
    End If

End Sub

 

[cizzett]

Thanks great Idea, Got it working off your code although I changed it to reflect > 1 rather than date

 

[Joe4]

Got it working off your code although I changed it to reflect > 1 rather than date

But then how will that work tomorrow? The number won't reset/clear itself.

 

[cizzett]

RIght but remember this is an excel template so the user opens the template and then saves the file to the proper location.

Tomorrow they open the template and do it again for the new file.

 

[Joe4]

Yes, but that template is looking at itself for the value that was last saved to it.

Maybe I am not quite clear on how you adapted it. Can you post your version of the code, so I can see what you did?

 

[cizzett]

So Basically, when it was checking for =Date it was not working but when I made it look for >1 it now just checks the cell for a value and makes the decision from there.

Private Sub Workbook_Open()

'   Check to see if macro has already been run today
    If Sheets("Pivot").Range("I1") > 1 Then
    Sheets("Pivot").Range("I1") = Date
     Exit Sub
    Else
'       Prompt user
        MsgBox "Please paste previous days data into the Prev Day sheet before proceeding", vbOKOnly + vbExclamation, "Before Proceeding"
'       Update tracking date
        Sheets("Pivot").Range("I1") = Date
    End If


End Sub

 

[Joe4]

I don't really see how that is going to work, unless there is something else that clears C1.
Is this a read-only file (meaning, can they save changes to it)?

If they can save changes made to it, then if I1 is populated with a date and saved (and nothing else is clearing C1), then every time they open that file afterwards, it it going to see there is a date in I1 and exit the sub before the message box.

If they cannot save changes to it, then the change to I1 will not be saved, and next time they open the file, it will see I1 is blank and prompt the message box, even if they open it the same day and have already completed the task for the day.