Before Save Event

GeorgeB

Board Regular
Joined
Feb 16, 2002
Messages
239
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Youre the man Dreamboat.
Nice piece of code that. Just what the doc
ordered. Thanks
Have a great day
George
 
Upvote 0
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.)
 
Upvote 0
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??
 
Upvote 0
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
 
Upvote 0
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" ?
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top