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

[QC_Guy]

I have a 2-worksheet Excel workbook 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:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>

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

<o> </o>
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.<o></o>
<o> </o>
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 (Open) 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 because one of the saved files was opened. I tried a couple things but had no luck. Any suggestions or guidance would be greatly appreciated.<o></o>

Thank you.

 

[QC_Guy]

Solved: Disable one macro when saving a macro-enabled workbook while other macros continue to run.

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 - a little sleep helps too). The code below does exactly what I want it to do.

I do have another question I'll post in another thread. It concerns a macro that can "monitor" a range of data entry cells on a worksheet for user input.

Thank you.

<!-- BEGIN TEMPLATE: bbcode_code -->

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