How to capture Excel Save As dialog box?

[Aruna Akella]

My application is addin to Excel. I am trying to capture Excel Save and
Cancel buttons on Save As dialog box - so that I can call methods relevant to
my app depending on what user pressed. Is there a way to capture those events?

Thanks in advance!

 

[JW]

My application is addin to Excel. I am trying to capture Excel Save and
Cancel buttons on Save As dialog box - so that I can call methods relevant to
my app depending on what user pressed. Is there a way to capture those events?

Thanks in advance!

Something like this what you're after?

Sub foo()
Dim saveName As String
saveName = Application.GetSaveAsFilename(fileFilter:="Excel Files
(*.xls), *.xls")
If saveName = False Or saveName = "" Then
MsgBox "No name entered or selected or canel button pressed"
Else
MsgBox saveName
End If
End Sub

HTH
-Jeff-

 

[Aruna Akella]

this is showing Save (or Save As dialog box 2 times)... any idea on how to
disable them? I did try disabling events & also set cancel = true at the end
- so that it will show only one time, but this didn't help - it didn't save
the workbook...

 

[Jon Peltier]

The posted code will not display the dialog twice.

GetSaveAsFileName does not save the file, just asks the user for a file
name. The programmer has to handle saving the file.

- Jon

 

[Chip Pearson]

One way would be the capture the BeforeSave event, test the SaveAsUI
parameter and if true, set Cancel to True and put up your own dialog.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Adrian Johnson, CA]

I've been working on a similar procedure, and have used the method suggested
by Chip below.

I created a class and include the declaration in that class:
'Public WithEvents xlapp As Application'

I then create an instance of that class when ThisWorkbook is opened - the
technique Chip outlines on his very useful website.

It all works fine, unless I click on the stop button in the VBA editor, or
something similar, which destroys the object set up to capture the
application events.

Is there anyway around this?

Thanks.

Adrian.

 

[Jon Peltier]

I've never found a satisfactory way around this. I tend to insert calls to
the routine that creates the xlApp object in a lot of places, so the object
is assured of being around "most of the time". Then I also put a button that
explicitly recreates the object somewhere in the menu.

Generally this is less of a problem when my users are using the programs
than when I am, since they don't go messing about in the VB Editor, and they
don't go crashing lots of things. (My development environment is a mess.)

- Jon