Disable MsgBox permanently after "OK"

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121
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

Code:
Private Sub Workbook_Open()

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


End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 10, 2019
Messages
121
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

Board Regular
Joined
Jan 10, 2019
Messages
121

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 10, 2019
Messages
121

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 10, 2019
Messages
121
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.

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,233
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top