PDA

View Full Version : Problems with Workbook_Beforesave



ldench
09-25-2008, 12:55 AM
Hi All,

I have a problem, looking for some help. I'll break it down in steps so its easier to read.

I have Userform (saveform) in my workbook.
Saveform has a save button on it where it will save the workbook using a predetermined name.
I want the saveform to show when the user tries to save (either save or save as)

That all works fine, only problem is that every time they click the save button on the saveform, the workbook_beforesave kicks back in and it reopens the form, which is still open from before, so it crashes my excel.

I need workbook_beforesave to be exited once they click the save button on the userform, is this even possible?

Thanks

Lee

xld
09-25-2008, 01:03 AM
Cretae a public variable flag, and set it when your form is up, unset it when it is down.

ldench
09-25-2008, 01:12 AM
Cretae a public variable flag, and set it when your form is up, unset it when it is down.

That's way beyond my level of VBA, not a clue what you mean. Could you please provide example code?

GTO
09-25-2008, 01:16 AM
I feel it safe to say that I type slower than xld. If the coding is rather simple, presuming your Before_Save procedure looks akin to:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
UserForm1.Show vbModal

End Sub


...and the code for the command bttn could be...

xld's would be better if there are events occurring that you would like to keep, but it didn't sound like it.

Option Explicit
Private Sub cmdSave_Click()
Application.EnableEvents = False
ThisWorkbook.Save
'or
'ThisWorkbook.SaveAs [Full path and FileName]
Application.EnableEvents = True
Unload Me
End Sub