VBA code to show a save as dialog does not work

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hi all,

I created a macro to intercept the save and save as in excel.
Then I want to force people to fill in the properties, which works and
then I want to show the save as dialog.
The funny thing is that I see the dialog when I start it inside the vb
editor, however it never appears on the screen when I run the code
from the workbook.
I used the following codes:
ThisWorkBook.Application.Dialogs(xlDialogSaveAs).Show
Excel.Application.Dialogs(xlDialogSaveAs).Show
Application.Dialogs(xlDialogSaveAs).Show
All of the three appear inside the vb editor but none of them inside
my workbook(that is when the user click on the save/save as button)
That code does work because it intercepts the save/save as and shows
the userform i created.

Does anyone know why this is happening??

Thank you.
 
FN = Application.GetSaveAsFilename()


Read all about GetSaveAsFilename in help as its well
explained

Patrick Molloy
Microsoft Excel MVP
 
Patrick Molloy said:
FN = Application.GetSaveAsFilename()
Click to expand...
Thx, but the result is the same. It does not work when I use the
change file menu. I shall include the full code:
in thisworkbook i have the following function
Sub Workbook_Open()
Dim mnu As Menu
Set mnu = MenuBars("worksheet").Menus("File")
With mnu.MenuItems("Save")
.OnAction = "MySave1()"
End With
With mnu.MenuItems("Save As...")
.OnAction = "MySave1()"
End With
With mnu.MenuItems("Save as Web Page...")
.OnAction = "MySave2()"
End With
End Sub
Then in the module I have the following code:
Sub MySave1()
FN = Application.GetSaveAsFilename
'UserForm1.Show
End Sub
Which I altered to test whether or not I get the dialog.
Do I overwrite the dialog somehow because I overwrite the menu or
something???
Because this is actually the problem. The funny thing however is that
if i run it normal(with the userform) he shows the userform twice en
then disappears(after twice clicking ok of course, so if i click ok
the first time it reappears)
 
Kevin,

Me thinks your OnAction assignments are not working.
Make Save and Save As .visible = false
Create two new buttons and assign your routine to them.
The new buttons should have a different caption, so just add a couple of
spaces, like... "Save " and "Save As... ".
Make sure you delete the new buttons and make the old buttons visible when
you exit.

Regards,
Jim Cone
San Francisco, CA
****************
Kevin said:
"Patrick Molloy" <[email protected]> wrote in message
Thx, but the result is the same. It does not work when I use the
change file menu. I shall include the full code:
in thisworkbook i have the following function
Sub Workbook_Open()
Dim mnu As Menu
Set mnu = MenuBars("worksheet").Menus("File")
With mnu.MenuItems("Save")
.OnAction = "MySave1()"
End With
With mnu.MenuItems("Save As...")
.OnAction = "MySave1()"
End With
With mnu.MenuItems("Save as Web Page...")
.OnAction = "MySave2()"
End With
End Sub
Then in the module I have the following code:
Sub MySave1()
FN = Application.GetSaveAsFilename
'UserForm1.Show
End Sub
Which I altered to test whether or not I get the dialog.
Do I overwrite the dialog somehow because I overwrite the menu or
something???
Because this is actually the problem. The funny thing however is that
if i run it normal(with the userform) he shows the userform twice en
then disappears(after twice clicking ok of course, so if i click ok
the first time it reappears)
Click to expand...
- snip -
 
Hi,

if the onaction is not working then why does he show me the form when i
click on it(the normal code). I just changed this code to test if I see
the dialog. Preceding this procedure is a form, everytime i click on
save or save as this form is shown.
Thx for your time.
 
Back
Top