How to disable Workbook_Open Msgboxes & userforms after SaveAs

[SWHarmon79]

I have a workbook for internal estimating that several people in my company use. I am adding VBA to make the workbook more user friendly and efficient.

1. User opens workbook and is prompted by a Y/N Textbox. "Would you like to start an estimate"
a. User presses "No", then estimate opens without any changes.
b. User presses "Yes", Then Userform "Estimate_Setup" opens.
2. User fills out the form and presses the "Start Estimate" button on the userform.
3. The info in the userform is then saved in the workbook in various sheets depending on the info.
4. The userform Calls the "Save_As" sub
5. User finishes and closes the workbook.
6. Later the user reopens the saved estimate workbook and the process starts over. I would like for the entire process of steps 1 - 4 to not be performed on any workbooks after the "Save_As" sub runs. So that later when a user reopens a saved estimate to work on they aren't prompted to start an estimate that they are already in the middles of and have filled out the "Estimate_Setup" userform.

I have added the relevant code that is responsible for the above.

I appreciate any and all help!!! Thanks in advance.


1

Private Sub Workbook_Open()




'Allow Outline functionality during Protection in all Sheets
Dim sht As Worksheet


'Loop through each Worksheet in ActiveWorkbook
  For Each sht In ActiveWorkbook.Worksheets
  'Password Protect Current Sheet
      sht.Protect Password:="W3lcome2019", UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
  'Enable Group Collapse/Expand Capabilities
      sht.EnableOutlining = True


Next sht


Call Start_Estimate_Msgbox




End Sub

2

Sub Start_Estimate_Msgbox()


Dim Result As Long


    Result = MsgBox("Would you like to start an estimate?", vbYesNo + vbQuestion, "Estimate Set-Up")


    If Result = vbYes Then
        f_Estimate_Setup.Show
    End If
    
    If Result = vbNo Then
        Sheet1.Columns("A:BM").Hidden = False
        
        Call Unhide_Multiple_Sheets


    End If
    


End Sub

3

Sub Estimate_Save_As()


Dim Path As String
Dim filename As String


Path = "C:\Users\shawn.harmon\Desktop\Material list test\Test Copies\"
filename = f_Estimate_Setup.txt_PrNm.Text & " " & f_Estimate_Setup.cbx_CustNm.Text & " " & Format(Now, "mmddyy")


ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm"


End Sub

 

[Fluff]

If you are happy to get rid of all code in the workbook, simply save as an xlsx file.
If you have other code that you need to keep, what is the initial name of the Workbook?

 

[SWHarmon79]

I have other code that needs to still work after save_as. The workbook name initially is "Estimate Template" this workbook isn't actually a saved Template that is just the name.

 

[Fluff]

Ok, try adding this line

Private Sub Workbook_Open()
[COLOR=#ff0000]If ThisWorkbook.Name <> "Estimate Template.xlsm" Then Exit Sub[/COLOR]
'Allow Outline functionality during Protection in all Sheets
Dim sht As Worksheet

 

[SWHarmon79]

Ok, try adding this line

Private Sub Workbook_Open()
[COLOR=#ff0000]If ThisWorkbook.Name <> "Estimate Template.xlsm" Then Exit Sub[/COLOR]
'Allow Outline functionality during Protection in all Sheets
Dim sht As Worksheet

@Fluff the above works for the current workbook but not the Save_As workbook that gets created after the save because it is saved with a new name based on user input from the userform. I would like something that works the exact way as the above suggestion except on the newly created workbook.

I'm thinking that I could take the userform info that is used to create the new workbook name and enter that in a cell on a sheet in the workbook. Then reference that cell as the current workbook name in the above suggested line of code. Then the code would exit the sub if the name matched. The cell referenced would only have a name in any of the saved workbooks and never in the "Estimate Template" workbook.

 

[Fluff]

I may have misunderstood what you wanted.
If you saveas then the workbook will no longer be called "Estimate Template.xlsm" and so the workbook_open event will quit.
Is that not what you want?

 

[SWHarmon79]

@Fluff Yes that is what I want but that isn't what is happening.

What is happening is

After I put in that line of code in the workbook and save then reopen the same workbook with the name "Estimate Template" the prompt doesn't open. I need the prompt and subsequent userform to run every time the "Estimate Template" is opened.
The user is unable to create a new estimate if the userform doesn't run upon Workbook_Open.

Then if I go to the VBE and the userform module and run the userform from there in open "Estimate Template" workbook and input some data and press "Start Estimate" button. A new workbook is created with the new name as needed. When that workbook is opened the prompt and userform do not run. This is what I needed but I do need it to run on the "Estimate Template" workbook everytime. It currently isn't with that line of code. I also tried changing the "<>" to "=" but that didn't work.

 

[Fluff]

Below the code window in the VBE there may be another window called "Immediate", if this isn't visible Ctrl G should bring it up.
Run this code

Sub Chk()
   Debug.Print "|" & ThisWorkbook.Name & "|"
End Sub

and then copy paste the contents of the Immediate window to the thread.

 

[SWHarmon79]

|Estimate Template.xlsm|

 

[SWHarmon79]

@Fluff It is working like a champ now. I think I fat fingered an extra space. When I copied and pasted directly from the Immediate Window it fixed the issue. Thanks for the help!!!