Results 1 to 8 of 8
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Using Workbook_BeforeSave

    XL97, W95, xlt file, in the module

    Option Explicit
    Public boolFileSaved As Boolean
    Public strFN As String

    and function SuggFName() works fine, this sub works when called from an e-mail_on_buttonclick sub in the module:

    Public Sub procFileSaver()
    strFN = Application.GetSaveAsFilename( _
    InitialFilename:=SuggFName(Worksheets("Input").Ran ge("Customer_Name").Text, _
    Worksheets("Input").Range("Contract_Date")), _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    If strFN <> "False" Then ' user has not cancelled file save
    On Error Resume Next
    ThisWorkbook.SaveAs FileName:=strFN, FileFormat:=xlNormal, AddtoMru:=True
    If Err = 0 Then boolFileSaved = True ' user didn't cancel when filename exists
    End If
    End Sub

    But when I'm "suggesting an appropriate filename" for users who click on the Save Icon or select File Save from the menu with the following at ThisWorkbook level

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If (ThisWorkbook.Name = "<Template Name & 1>") Then
    boolFileSaved = False
    Call procFileSaver
    If Not boolFileSaved Or Cancel Then MsgBox "File not saved!", vbExclamation
    End If
    End Sub

    I get the dialog and Suggested File Name, but no matter whether the user saves or cancels out, they are bounced back to another file save dialog, twice if the filename already exists. I don't want that to happen, and I don't understand what "ByVal SaveAsUI As Boolean" means, does, or if I can manipulate it. What do I need to do here?
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Workbook_BeforeSave

    John,

    By calling procFileSaver() in the BeforeSave event you are in effect making two calls to save the file, the initial save and the procedure call. If the procedure saves the file ok, you should set Cancel = True immediately after the procedure returns control to BeforeSave.

    See if something like the following helps :

    Call procFileSaver
    If Not boolFileSaved Then
    MsgBox "File not saved!", vbExclamation
    <font color=448800>'Handle Not Saved eventuality here</font color=448800>
    Else
    Cancel = True
    End If


    Andrew

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Workbook_BeforeSave

    Try something like the following:

    <pre>Option Explicit
    Public boolFileSaved As Boolean
    Public strFN As String


    Public Sub procFileSaver()
    strFN = Application.GetSaveAsFilename( _
    InitialFilename:=SuggFName(Worksheets("Input").Ran ge("Customer_Name").Text, _
    Worksheets("Input").Range("Contract_Date")), _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    If strFN <> "False" Then ' user has not cancelled file save
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs FileName:=strFN, FileFormat:=xlNormal, AddtoMru:=True
    If Err = 0 Then boolFileSaved = True ' user didn't cancel when filename exists
    Application.DisplayAlerts = True
    End If
    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If (ThisWorkbook.Name = "<Template Name & 1>") Then
    boolFileSaved = False
    Call procFileSaver
    If Not boolFileSaved Then MsgBox "File not saved!", vbExclamation
    Cancel = True
    End If
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Workbook_BeforeSave

    Andrew and Legare, thanks for your help.

    I'm now getting it half right. Using Application.DisplayAlerts doesn't seem to make a difference. The following at least doesn't repeat the Save As dialog if the user cancels, but does show Save As dialog twice if the user proceeds with the save under the suggested name, and shows Save As dialog three times if the user proceeds with the save and the file name already exists.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If (ThisWorkbook.Name = "<Template & 1>") Then
    boolFileSaved = False
    Call procFileSaver
    If Not boolFileSaved Then
    MsgBox "File not saved!", vbExclamation
    Cancel = True
    End If
    End If
    End Sub

    If I have Cancel = True anywhere outside the "If Not boolFileSaved Then ..." the dialogs don't repeat BUT the file isn't saved!

    I also learned that "SaveAsUI As Boolean" is True if the user has selected "Save As" from the menu, else False if the user has selected "Save" (from Ctrl-S, Save Icon, or menu Save). But because this is a template, the Save As dialog gets automatically brought up by Excel; so I'm wondering if that causes any weirdness.

    My brain is fried, but I'll keep working on this, and further suggestions are welcome. If work doesn't keep interrupting (!), I'll post a spreadsheet with most of the code in it so you can hammer on it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Workbook_BeforeSave

    Nice to see you got it working. I assumed that if the save procedure failed, you might want to take some other action , hence the <'Handle Not Saved eventuality here>, in which you could also set Cancel = true.

    However all is well if you got the desired results. SaveAsUI does as you describe and I don't think there is any gain in playing around with it. (Maybe somebody can make good use of it).

    Anyway good luck with the project and remember No Pain, No Gain.

    Andrew

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Workbook_BeforeSave

    Well ... , as I said, it's only
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Workbook_BeforeSave

    John,

    1. Do you need procFileSaver at all. Can BeforeSave not handle all that ?

    2. Maybe if you imposed a file name rather than suggest one, and have BeforeSave name the file.

    Two ideas without reading the initial post and code again, so if I missed something, sorry.

    Andrew

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Workbook_BeforeSave

    Re your points, and considering I'm a VBA newby:

    1. OK, that leads me to a dumb question; can I call a ThisWorkbook event from a Sub? That is (spur of the moment pseudo-code), can I:

    sub emailbuttonclick()
    msgbox "wanna save that file?", OKCancel
    if OK then
    call ThisWorkbook.Before_Save
    if boolFileSaved then ThisWorkbook.SendMail
    else msgbox "You're outa here"
    end sub

    if so, I can invert my whole approach. Eeha! This could lead to a whole new bunch of pesky questions.

    2. The filename I'm trying to get suggest is dynamic, a combination of Customer name and Contract date.

    Thanks for your continued patience.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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