Setting Cancel=True in Workbook.BeforeSave subroutine doesn't appear to work in compiled. Any workaround ideas?

S

StevegHI

Member
I'm on 2025.2 with Windows/10 and have a complex Excel spreadsheet. However, when I try to save, I need the option to cancel it in the Workbook.BeforeSave subroutine.

Setting Cancel=True end exiting the subroutine doesn't cancel the save. It works in the xlsm version but not in the compiled version. Tracing the program shows it executes the Workbook.BeforeSave routine with Cancel=True and goes into the AfterSave Workbook.AfterSave subroutine. After exiting that routine, it brings up a window titled "Save local workbook." For your information, I use XLSPadlock_OnAfterSave but even disabling that makes no difference.

I still get the save window (attachment) and XLSPadlock tries to save the file. Need to get this going as soon as possible or will have to reprogram a different save mechanism. (Can upload a zip file with sample source if anybody's interested.)

Anybody have an idea of a work around? Hate to make a separate button for Save.
 
Solution
S
Okay, got some excellent support from André C. from GDGSupport!

' Manual: 2025.2 Section 10.2.3 has some information on this particular call except for a different reason: 'Programmatically Hide Wait/Loading Dialogs

1. In the Workbook_BeforeSave:
  1. Disable [Call XLSPadlock_ControlSaveDialog("disable")].
  2. Put out MSGBOX asking type of save or cancel.
  3. Cancel: set Cancel toTrue and exit sub
  4. Traditional: enable the dialog [Call XLSPadlock_ControlSaveDialog("enable")] and exit.
  5. Supersave (specialized): disable the dialog [Call XLSPadlock_ControlSaveDialog("disable")]. Then go to our specialized save process. After it is completed, cancel and exist Workbook_BeforeSave

Private Sub XLSPadlock_ControlSaveDialog(Control As...
Sort by date Sort by votes
Okay, got some excellent support from André C. from GDGSupport!

' Manual: 2025.2 Section 10.2.3 has some information on this particular call except for a different reason: 'Programmatically Hide Wait/Loading Dialogs

1. In the Workbook_BeforeSave:
  1. Disable [Call XLSPadlock_ControlSaveDialog("disable")].
  2. Put out MSGBOX asking type of save or cancel.
  3. Cancel: set Cancel toTrue and exit sub
  4. Traditional: enable the dialog [Call XLSPadlock_ControlSaveDialog("enable")] and exit.
  5. Supersave (specialized): disable the dialog [Call XLSPadlock_ControlSaveDialog("disable")]. Then go to our specialized save process. After it is completed, cancel and exist Workbook_BeforeSave

Private Sub XLSPadlock_ControlSaveDialog(Control As String)
Dim XLSPadlock As Object
On Error Resume Next
' Control is either enable or disable. Simply exits if invalid

Select Case LCase$(Control)
Case "enable"
Set XLSPadlock = Application.COMAddIns("GXLS.GXLSPLock").Object
If Not XLSPadlock Is Nothing Then XLSPadlock.setOption Option:="1", Value:="0"

Case "disable"
Set XLSPadlock = Application.COMAddIns("GXLS.GXLSPLock").Object
If Not XLSPadlock Is Nothing Then XLSPadlock.setOption Option:="1", Value:="1"
End Select
End Sub
 
Upvote 0 Downvote
Solution
You must log in or register to reply here.
Back
Top