Before Save Event

GeorgeB · Mar 24th, 2002, 12:00 PM

Good day all
Trying to remind the user to save a template as something else. This must be basic VBA but I can't put my finger on the problem.
I get run time error 91 object variable not set with this.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyWkbk As Workbook

If MyWkbk.Name = Book3.xls Then

MsgBox prompt:="Don't step on the template"

End If
End Sub

Anne Troy · Mar 24th, 2002, 12:03 PM

Why not just make it read-only so that they cannot?

Tom Schreiner · Mar 24th, 2002, 01:27 PM

Hi
Copy this code into your workbook class module.
Make sure the dim statement is put in the general declarations part of the module(The very top)

This will force the user to save the file with a different name by having the save as dialog pop up and prompt for a filename.
It will not allow the active workbook to be saved with the same name anywhere on your computer.

Option Explicit
Public SavedAlready As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fName
If SavedAlready = True Then GoTo SaveNot
ReSave:
Do Until fName <> False And fName <> ActiveWorkbook.Path & "" & ActiveWorkbook.Name
fName = Application.GetSaveAsFilename("SaveNewName", Filefilter:="Excel Worksheet (*.xls), *.xls")
Loop
If LCase(Right(fName, Len(ActiveWorkbook.Name))) = LCase(ActiveWorkbook.Name) Then
MsgBox "Cannot save with that name. Please choose another"
fName = False
GoTo ReSave
End If
SavedAlready = True
SaveNot:
ActiveWorkbook.SaveAs fName
End Sub

GeorgeB · Mar 24th, 2002, 01:56 PM

Thanks for your replies gentlemen. Perhaps I should have explained it better. I have a large template to do construction bidding on. It resides on the company server so saving it as a template on each computer is not an option for maintenance reasons. Once in a while an estimator starts a job and saves it without renaming it thereby "stepping on the template" and I have to remove the data to return it to a usable state. I want to check it before save for a specific name and remind them not to "step on the template" but allow standard save after it is renamed.
Thanks for your help.
George

Anne Troy · Mar 24th, 2002, 03:37 PM

Hello, George.

Try this. It *almost* forces your guys to give the file a name right away. This can keep them from forgetting.

Private Sub Workbook_Open()

Dim Msg, Style, Title, Response, MyString
Msg = "Please save the file to a new name." ' Define message.
Style = vbOKCancel + vbExclamation ' Define buttons.
Title = "Save File" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbOK Then ' User chose Ok.
Application.Dialogs(xlDialogSaveAs).Show ("*.xls")

Else: Application.ActiveWorkbook.Close False

End If
End Sub

Hope it helps.

GeorgeB · Mar 24th, 2002, 03:49 PM

Youre the man Dreamboat.
Nice piece of code that. Just what the doc
ordered. Thanks
Have a great day
George

Anne Troy · Mar 24th, 2002, 05:27 PM

Youre the man Dreamboat.

My husband might be surprised to find that out!!

Happy to help. Have to tell you, that's the first piece of code I did virtually on my own. (That means without stealing from somebody else first.)

GeorgeB · Mar 24th, 2002, 06:29 PM

Sorry Dreamboat
You're the WOman then. One more ? if you care to respond. Any way to test for a specific name (the original name) and not run the code if the workbook has already been saved with a new name and put into use??

GeorgeB · Mar 24th, 2002, 07:34 PM

Re: My last post. This is the code I'm trying but get a "type mismatch" error on the .Name part of the Set line.

Private Sub Workbook_Open()
'Display plan log message
UserForm1.Show
On Error Resume Next
Dim wBook As Workbook
Set wBook = (Application.ActiveWorkbook.Name)
If wBook.Name <> ("Nevada Estimating 5.0.xls") Then
End If
Exit Sub
Dim Msg, Style, Title, Response, MyString
Msg = "PLEASE SAVE THE FILE TO A NEW NAME." ' Define message.
Style = vbOKCancel + vbExclamation ' Define buttons.
Title = " DONT STEP ON THE TEMPLATE" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbOK Then ' User chose Ok.
Application.Dialogs(xlDialogSaveAs).Show ("*.xls")

Else: Application.ActiveWorkbook.Close False

End If
End Sub

Dave Hawley · Mar 24th, 2002, 08:05 PM

Hi George

I am a bit lost in what you are doing but try this:

Private Sub Workbook_Open()
Dim wBook As Workbook
Dim Response As Integer
'Display plan log message
UserForm1.Show

Set wBook = ActiveWorkbook
If wBook.Name <> "Nevada Estimating 5.0.xls" Then
End If

Exit Sub 'SHOULD THIS BE BEFORE THE ABOVE "END IF"?

Response = MsgBox("PLEASE SAVE THE FILE TO A NEW NAME.", _
vbOKCancel + vbExclamation, " DONT STEP ON THE TEMPLATE")

If Response = vbOK Then ' User chose Ok.
Application.Dialogs(xlDialogSaveAs).Show ("*.xls")

Else: Application.ActiveWorkbook.Close False

End If
End Sub

I think you really want the "Exit Sub" moved before the "End If" ?

Mar 24th, 2002, 12:03 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Why not just make it read-only so that they cannot? __________________ ~Anne Troy

Mar 24th, 2002, 03:37 PM	#5
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Hello, George. Try this. It almost forces your guys to give the file a name right away. This can keep them from forgetting. Private Sub Workbook_Open() Dim Msg, Style, Title, Response, MyString Msg = "Please save the file to a new name." ' Define message. Style = vbOKCancel + vbExclamation ' Define buttons. Title = "Save File" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbOK Then ' User chose Ok. Application.Dialogs(xlDialogSaveAs).Show ("*.xls") Else: Application.ActiveWorkbook.Close False End If End Sub Hope it helps. __________________ ~Anne Troy

Mar 24th, 2002, 05:27 PM	#7
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Youre the man Dreamboat. My husband might be surprised to find that out!! Happy to help. Have to tell you, that's the first piece of code I did virtually on my own. (That means without stealing from somebody else first.) __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 24th, 2002, 12:00 PM	#1
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Good day all Trying to remind the user to save a template as something else. This must be basic VBA but I can't put my finger on the problem. I get run time error 91 object variable not set with this. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim MyWkbk As Workbook If MyWkbk.Name = Book3.xls Then MsgBox prompt:="Don't step on the template" End If End Sub

Mar 24th, 2002, 01:27 PM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Copy this code into your workbook class module. Make sure the dim statement is put in the general declarations part of the module(The very top) This will force the user to save the file with a different name by having the save as dialog pop up and prompt for a filename. It will not allow the active workbook to be saved with the same name anywhere on your computer. Option Explicit Public SavedAlready As Boolean Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim fName If SavedAlready = True Then GoTo SaveNot ReSave: Do Until fName <> False And fName <> ActiveWorkbook.Path & "" & ActiveWorkbook.Name fName = Application.GetSaveAsFilename("SaveNewName", Filefilter:="Excel Worksheet (.xls), .xls") Loop If LCase(Right(fName, Len(ActiveWorkbook.Name))) = LCase(ActiveWorkbook.Name) Then MsgBox "Cannot save with that name. Please choose another" fName = False GoTo ReSave End If SavedAlready = True SaveNot: ActiveWorkbook.SaveAs fName End Sub

Mar 24th, 2002, 01:56 PM	#4
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Thanks for your replies gentlemen. Perhaps I should have explained it better. I have a large template to do construction bidding on. It resides on the company server so saving it as a template on each computer is not an option for maintenance reasons. Once in a while an estimator starts a job and saves it without renaming it thereby "stepping on the template" and I have to remove the data to return it to a usable state. I want to check it before save for a specific name and remind them not to "step on the template" but allow standard save after it is renamed. Thanks for your help. George

Mar 24th, 2002, 03:49 PM	#6
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Youre the man Dreamboat. Nice piece of code that. Just what the doc ordered. Thanks Have a great day George

Mar 24th, 2002, 06:29 PM	#8
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Sorry Dreamboat You're the WOman then. One more ? if you care to respond. Any way to test for a specific name (the original name) and not run the code if the workbook has already been saved with a new name and put into use??

Mar 24th, 2002, 07:34 PM	#9
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Re: My last post. This is the code I'm trying but get a "type mismatch" error on the .Name part of the Set line. Private Sub Workbook_Open() 'Display plan log message UserForm1.Show On Error Resume Next Dim wBook As Workbook Set wBook = (Application.ActiveWorkbook.Name) If wBook.Name <> ("Nevada Estimating 5.0.xls") Then End If Exit Sub Dim Msg, Style, Title, Response, MyString Msg = "PLEASE SAVE THE FILE TO A NEW NAME." ' Define message. Style = vbOKCancel + vbExclamation ' Define buttons. Title = " DONT STEP ON THE TEMPLATE" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbOK Then ' User chose Ok. Application.Dialogs(xlDialogSaveAs).Show ("*.xls") Else: Application.ActiveWorkbook.Close False End If End Sub

Mar 24th, 2002, 08:05 PM	#10
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi George I am a bit lost in what you are doing but try this: Private Sub Workbook_Open() Dim wBook As Workbook Dim Response As Integer 'Display plan log message UserForm1.Show Set wBook = ActiveWorkbook If wBook.Name <> "Nevada Estimating 5.0.xls" Then End If Exit Sub 'SHOULD THIS BE BEFORE THE ABOVE "END IF"? Response = MsgBox("PLEASE SAVE THE FILE TO A NEW NAME.", _ vbOKCancel + vbExclamation, " DONT STEP ON THE TEMPLATE") If Response = vbOK Then ' User chose Ok. Application.Dialogs(xlDialogSaveAs).Show ("*.xls") Else: Application.ActiveWorkbook.Close False End If End Sub I think you really want the "Exit Sub" moved before the "End If" ?