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]

You're welcome & thanks for the feedback