Hi-jack SaveAs Command

[Guest]

When a user selects the SaveAs command with any spreadsheet they have open,
is there a way to run a macro before excel's built-in SaveAs dialog box
shows? 90% of our files are saved in a client's directory, and we want to
run a macro to help verify that the user has the correct client's directory.
In MS Word, I just added a function in the normal.dot named SaveAs. Is there
anyway to do something similar in Excel?

 

[Dick Kusleika]

Granny

Yes. You need a class module in a workbook that opens at startup (like an
add-in or personal.xls) that hooks the application level events. You can
read about app level events here

http://www.cpearson.com/excel/AppEvent.htm
http://www.dicks-blog.com/excel/2004/06/classes_applica.html

Once you have your class module set up, you can use the
class_WorkbookBeforeSave event to intercept the user saving any open
workbook. This event has an argument called SaveAsUI that you can test.
It's TRUE if the event was fired because the user clicked File > SaveAs.
Your code might look like this

Private Sub app_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If SaveAsUI Then
'Do your checking here
End If

End Sub

The app in app_WorkbookBeforeSave is the name of a variable declared in the
class module. Yours may be different depending on what you name your
variable.

It's not quite as easy as Word, is it? If you get stuck, be sure to post
back.

 

[Guest]

Thanks, Ill check it out!

Granny

Yes. You need a class module in a workbook that opens at startup (like an
add-in or personal.xls) that hooks the application level events. You can
read about app level events here

http://www.cpearson.com/excel/AppEvent.htm
http://www.dicks-blog.com/excel/2004/06/classes_applica.html

Once you have your class module set up, you can use the
class_WorkbookBeforeSave event to intercept the user saving any open
workbook. This event has an argument called SaveAsUI that you can test.
It's TRUE if the event was fired because the user clicked File > SaveAs.
Your code might look like this

Private Sub app_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If SaveAsUI Then
'Do your checking here
End If

End Sub

The app in app_WorkbookBeforeSave is the name of a variable declared in the
class module. Yours may be different depending on what you name your
variable.

It's not quite as easy as Word, is it? If you get stuck, be sure to post
back.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com