Before Save Event
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Before Save Event

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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??

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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" ?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com