Disable one macro when saving a macro-enabled workbook while other macros continue.

[QC_Guy]

I have a 2-worksheet Excel workbook (Excel 2007 and 2010) that serves as a template for users to enter collected data into. Each data set requires a unique file name (for tracking), so I wrote the macro below. Each time a user collects data, they open the template workbook and it directs them to immediately save the file into a specific directory. The code for that is as follows:



Private Sub Workbook_Open()
' Written by Authors
' Last updated 02/23/2011

' Open a Message Box informing the user that they MUST save the workbook before use.
MsgBox ("This workbook MUST be saved to the I:\ drive before use.")

Dim Show_Box As Boolean
Dim Response As Variant

' Set the Show_Dialog variable to True.
Show_Box = True

' Begin While loop.
While Show_Box = True

' Show the Save File Instructions Input Bbox.
Response = InputBox("User Instructions", _
"Save File Formatting Instructions")

' Check to ensure the user entered a file name.
If Response = "" Then
Else
' Test to make sure an entry was made.
If Response <> "" Then
' Set the path on the I:\ drive to save the file to
MyPath = "Specified Path"

' Set the format of the saved file as a macro-enabled workbook
ActiveWorkbook.SaveAs Filename:=MyPath & "\" & Response, FileFormat:=51
Show_Box = False
Else
End If
End If

' End the While loop.
Wend

End Sub


I know the FileFormat should be set to 52 for a macro-enable workbook, but I have it set to 51 for now because if the saved file is macro-enabled (as I need it to be), when it is opened later it will run the macro again asking for a file name. There are other macros I plan to insert into the template workbook for use in the saved macro-enabled workbook, so I need to disable the macro above once the new workbook is saved.

I thought there may be code that could be inserted before the Message Box appears (perhaps an If Then statement) that would check the Active Workbook file name against the name of the template workbook. If they are the same, the macro will run because the filename match means the template was opened. If the Active Workbook does not match, then the macro is skipped. I tried a couple things but had no luck. Any suggestions or guidance would be greatly appreciated.

Thank you.

[mdmackillop]

Is your template a "real" template, or just a normal workbook. If the latter then use a proper template which is programmed to show a FileSave dialog


Private Sub Workbook_Open()
If Not ActiveWorkbook.Saved Then
Application.Dialogs(xlDialogSaveAs).Show
End If
End Sub

[QC_Guy]

What I called a template is not saved as a template, just a macro-enabled workbook.

I finally had a breakthrough last night as I was helping my daughter with her science fair project (always good to step away to get a better perspective). The code below does exactly what I want it to do.

Thank you.


Private Sub Workbook_Open()
' Written by Authors
' Last updated 02/24/2011
' Check to see if the opened workbook is the template workbook or a saved workbook.
' If the opened workbook is the template, the SaveAs code below will run.
' If the opened workbook is a previously saved workbook, the code below is skipped.
If ActiveWorkbook.Name = "TemplateFileName.xlsm" Then

' Open a Message Box informing the user that they MUST save the workbook before use.
MsgBox ("This workbook MUST be saved to the I:\ drive before use.")
Dim Show_Box As Boolean
Dim Response As Variant
' Set the Show_Dialog variable to True.
Show_Box = True
' Begin While loop.
While Show_Box = True
' Show the Save File Instructions Input Box.
Response = InputBox("Save File Instructions", _
"Save File Formatting Instructions")
' Check to ensure the user entered a file name.
If Response = "" Then
Else
' Test to make sure an entry was made.
If Response <> "" Then
' Set the path on the I:\ drive to save the file to
MyPath = "I:\MyPath"

' Set the format of the saved file as a macro-enabled workbook
ActiveWorkbook.SaveAs Filename:=MyPath & "\" & Response, FileFormat:=52
Show_Box = False
Else
End If
End If

' End the While loop.
Wend
Else
Exit Sub
End If

End Sub

[mdmackillop]

Is there a reason not to use a template?

[QC_Guy]

Is there a reason not to use a template?

One word...consistency.

The template is needed because there are numerous other calculations, data, and automated checks present in the workbook. To help satisfy accrediting auditors (ISO and such) during inspections, it is best to have a blank template that is used each time data is entered. During an inspection, an inspector normally wants to see the "behind the scenes" calculations and checks to ensure the results going out on reports are accurate, which I can do by removing the protections I put in place. To keep individual analysts and technicians from inventing their own way of doing things, a protected template is used.

Hope that answers your questions. I do have another question I'll post on another thread. It concerns code to "monitor" certain data entry cells for format and such.

Thank you.

[mdmackillop]

I would think that a template would offer exactly the same, but no big issue. For the future, you should make clear when you are using a worbook as the starting document. "Template" could be misleading and provide you with the wrong solution.