VBA to Display Save As

[GiselleH]

Hi Everyone,

I'm trying to develop a simple VBA code which is called when the user tries to save the workbook. This has two potential outcomes:
1. They are trying to save one worksheet, and will be taken to a different worksheet (5. Checks) to review the instructions there
2. They want to save the entire workbook, in which case they should be taken to the usual Save As dialogue box and proceed as normal.

I put together the following code, which mostly works, except:
1. When I save the workbook as a new name, my entire excel crashes
2. Sometimes the save as dialogue box pops up twice for no reason (as in, I will save the workbook, and then it will ask me to save it again).

Any thoughts on what is causing those two issues?

Thanks!



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim answer As Integer

answer = MsgBox("If you are saving the final database, please extract the database first and save separatly from the consolidator tool" & vbNewLine & "To save the database, click CANCEL and extract database. To save the consolidator tool, click OK", vbOKCancel, "Save clarification")

If answer = vbCancel Then
ThisWorkbook.Activate
Sheets("5. Checks").Select
Range("C17").Select
Exit Sub

ElseIf answer = vbOK Then
Application.EnableEvents = False
Application.Dialogs(xlDialogSaveAs).Show
Application.EnableEvents = True

End If

End Sub

 

[GTO]

I'm trying to develop a simple VBA code which is called when the user tries to save the workbook. This has two potential outcomes:
1. They are trying to save one worksheet, and will be taken to a different worksheet (5. Checks) to review the instructions there
2. They want to save the entire workbook, in which case they should be taken to the usual Save As dialogue box and proceed as normal.

I put together the following code, which mostly works, except:
1. When I save the workbook as a new name, my entire excel crashes
2. Sometimes the save as dialogue box pops up twice for no reason (as in, I will save the workbook, and then it will ask me to save it again).

Greetings Giselle,

You are using the BeforeSave event, so if we are going to alter what is about to normally happen (i.e. - Save or SaveAs) then we need to set Cancel to True; does that make sense?

Guessing a bit, I think you are wanting to prevent a Save and force a SaveAs, is that correct? If so, here's a first shot:

Option Explicit
  
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Static InProcess As Boolean
  
  If Not SaveAsUI Then
    If Not InProcess Then
      Cancel = True
      InProcess = True
      Application.Dialogs(xlDialogSaveAs).Show
      InProcess = False
    End If
  End If
  
End Sub

Hope that helps,

Mark

 

[GiselleH]

Amazing! Thanks for taking a look, Mark. That helped push me in the right direction and it all seems to be working now!

 

[GTO]

You are very welcome and Welcome to MrExcel

Mark