Macro vs Manual save event

  • Thread starter Thread starter David Sedberry
  • Start date Start date
D

David Sedberry

Is there a way to tell if a save event is generated by a macro rather than
by a user pressing the save button?

I have an add-in using the appl_workbookbeforesave event to display the
Document Properties dialog box anytime a user saves a workbook (my company
is big on using Document Properties). This works great, the document
properties dialog box shows when a user clicks 'Save' or 'Save As' or clicks
'yes' when given the '...do you want to save changes' dialog. The document
properties dialog does not show when a macro saves a workbook (this is the
desired behavior).

I added a form with instructions on filing in the document properties. A
checkbox on the form allows the user to disable the instruction form.
However, when the Instruction form is enabled, it shows up when a macro
saves a workbook. I don't want this to happen.

I need a way to determine if a save event is generated by a macro or by a
user pushing a button.

Thanks in advance

David Sedberry
 
David,

There is no way to determine whether a Save was caused by coded or the user.
You could set up a flag variable, set it to True prior to any VBA save, and
test its value in the BeforeSave event. E.g.,

Public VBASave As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If VBASave = True Then
' special code here
End If
VBASave = False
End Sub
 
I have discovered something that works for me using Win2k and Excel 97.

I wrote an add-in to display the Document Properties Prompt every time the
user saves a workbook. I trapped the application level beforesave event in
order to display the dialog box. I created a form with instruction for the
user regarding the "company" way to input the Document Properties. A check
box allows the user to disable the form from showing before the properties
dialog box displays. If the instruction form was enabled, it would also show
up when a save was generated from a macro even though the Properties Dialog
would not display.

I found the xlDialogEditionOptions and I don't know what its function is,
but I found a way to use it to determine if the xlDialogProperties will show
or not.

''' The following code is in the appl_workbookbeforeclose event handler

If Applicaton.Dialogs(xlDialogEditionOptions).Show Then
If ShowInst = True Then InstForm.Show
End If

Application.Dialogs(xlDialogProperties).Show

'''''''''''''''''''''''''

Application.Dialogs(xlDialogProperties).Show will show if the save was
manual but not if the save was called from a macro.
Applicaton.Dialogs(xlDialogEditionOptions).Show will not actually "show"
anytime, but it's properties are the same as
Application.Dialogs(xlDialogProperties).Show and can be tested to determine
if the save was called from a macro or a user.

Now, can anyone tell what xlDialogEditionOptions actually is or does and
wether or not I might encounter problems with my code as listed above?

David Sedberry
 
Back
Top