Turn off MsgBox from Macro 1 when Running Macro 2

[Realtreegirl75]

I have a form that is filled out by field users on a daily basis. It has a couple of message boxes but the one giving me fits is instructions on how to change the date in the form when the file is first opened. Most of my field users are not very computer savvy so I need that message box to stay in place.

The issue comes on the back end. I have another macro that opens each report and converts the form to PDF and then closes the file. Each time it opens a file, that "change the date" macro pops up and you have to select OK before the macro can continue. Not a huge deal for 5 or 6 reports, kind of a pain for 90+ reports.

Is there a way to turn off those message boxes for certain users? Or automatically select OK while the macro is running so the user doesn't have to click it each time?

 

[NdNoviceHlp]

Hi Realtreegirl75. Usually, for 1 time things, you can add a boolean to limit the occurrence. Something like this....

Dim MsgFlag As Boolean
MsgFlag = False
If Not MsgFlag Then
MsgBox "Change date instructions"
MsgFlag = True
End If

HTH. Dave

 

[Akuini]

but the one giving me fits is instructions on how to change the date in the form when the file is first opened.

1. Can you show us the code?
2. Where is the code located? in multiple workbooks that users need to open?

I have another macro that opens each report

3. Is this macro located in one specific workbook?

 

[Realtreegirl75]

1. Can you show us the code?
2. Where is the code located? in multiple workbooks that users need to open?

3. Is this macro located in one specific workbook?

1. This is what I have in each user form (called Daily Report):

Private Sub workbook_Open()
If Range("AC4").Value = "Revision 21" Then
Range("Ac4") = InputBox("Once date is entered, it cannot be changed. Please enter date:")
    Else
MsgBox "FIRST: Reset Form to be able to change the date"
    End If
End Sub

2. This code is in one template workbook that each field user opens/completes daily as a daily report.
3. The second macro is in a different workbook (called Closeout) and doesn't interact at all with the macros in Daily Report. The only hang up is the msgbox when each file opens.

 

[Realtreegirl75]

Hi Realtreegirl75. Usually, for 1 time things, you can add a boolean to limit the occurrence. Something like this....

Dim MsgFlag As Boolean
MsgFlag = False
If Not MsgFlag Then
MsgBox "Change date instructions"
MsgFlag = True
End If

HTH. Dave

I guess I'm not sure what this means...I haven't done a lot with booleans. Could you please explain further?

 

[Akuini]

2. This code is in one template workbook that each field user opens/completes daily as a daily report.

I don't understand, if the code only resides in a template workbook then why the message box appear when you open every reports?

 

[Realtreegirl75]

I don't understand, if the code only resides in a template workbook then why the message box appear when you open every reports?

The code is in the template to show every time the workbook is opened. So whoever opens it, each time, that box pops up.

 

[Akuini]

The code is in the template to show every time the workbook is opened. So whoever opens it, each time, that box pops up.

kind of a pain for 90+ reports.

Are you saying when you open 90+ reports you open the template workbook 90+ times?

 

[Akuini]

I still don't fully understand the workflow, but here's an idea:
I assume that when users open the template workbook, workbook "Closeout" is always closed.
So, how about this scenario:
If workbook "Closeout" is closed and anyone open the template workbook then the message box will appear.
When workbook "Closeout" is open and anyone open the template workbook then the message box will not appear.

Basically, you just need to amend the code by adding a check whether workbook "Closeout" is open or not.

Private Sub Workbook_Open()
Dim wb As Workbook

On Error Resume Next
Set wb = Workbooks("Closeout.xlsm")
If Err.Number > 0 Then
    If Range("AC4").Value = "Revision 21" Then
    Range("Ac4") = InputBox("Once date is entered, it cannot be changed. Please enter date:")
        Else
    MsgBox "FIRST: Reset Form to be able to change the date"
    End If
End If
On Error GoTo 0
End Sub

 

[Realtreegirl75]

Are you saying when you open 90+ reports you open the template workbook 90+ times?

Kind of. So Field User opens the Daily Report template on Monday morning, fills out the report, then saves it with Monday's details (project name, date, etc.). Tuesday morning, Field User opens the Daily Report template, fills out the report, etc. A project can be anywhere from 3 to 130 days and there will be a report for each day. Field User does not have access to Closeout.

Once the project is complete, Office User opens Closeout and runs a macro which loops through all of Field User's Daily Reports. This macro opens each Daily Report for a project, ensures all formatting is correct, converts the report to PDF, then closes the Daily Report and moves to the next one.

So the issue is, since the code in the Daily Report is set to have a msgbox pop-up every time the workbook opens, then when Office User runs the macro in Closeout to convert all the Daily Reports, the msgbox opens with every Daily Report. Office User is having to click OK on each msgbox before the macro can continue. I need a way to disable that msgbox when the Closeout macro is run.