ExcelBanter - Question "Save As" dialog box... (-:

Hi blc,

The problem, as noted by Tom, is that the BeforeSave event fires before the
user is even prompted for the save location.

Here's some code you could use that may get you started on a solution:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim vFullPath As Variant
Dim sInitName As String
Dim sNewName As String

Cancel = True

If SaveAsUI Then
'/ show own save as
sInitName = IIf(InStr(1, FullName, ".xls"), _
FullName, FullName & ".xls")
vFullPath = Application.GetSaveAsFilename(sInitName, _
"Microsoft Excel Workbook (*.xls), *.xls")
If vFullPath = False Then
'/ user cancelled
Else
sNewName = CStr(vFullPath)
Application.EnableEvents = False
SaveAs sNewName
Application.EnableEvents = True
End If
Else
sNewName = FullName
Application.EnableEvents = False
Save
Application.EnableEvents = True
End If

If Len(sNewName) Then MsgBox sNewName
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

blc wrote:
Hello all,

Anyone have any idea how I can get a reference to the "Save As"
dialog box that is instantiated when a user clicks the "Save As"
button on the Excel MAIN toolbar???

I would like to be able to determine what the new filepath and
filename will be in the Workbook_BeforeSave event before the Save
occurs...

Thank you very much,

blc