Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    Aurora, Ontario
    Posts
    10

    Question Unanswered: Code runs but doesn't save the file


    I have a file which opens in one directory and when closed the user is presented with a different directory to save in.

    The code attached lets me present the user with the proper name, allows you to click all of the right buttons but doesn't save the file.

    If I click on an existing name it will let ask me to replace the file.

    Any thoughts would be greatly appreicated.

    ***********Form Code Starts Here***********
    Option Explicit

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sQ As String
    ThisWorkbook.Saved = True
    sQ = MsgBox("Do you want to save this workbook ?", vbYesNoCancel)
    If sQ = vbCancel Then Cancel = True
    If sQ = vbYes Then
    If FSaveAs = False Then
    Cancel = True
    End If
    End If

    End Sub


    *******Module Code Starts Here***********

    Option Explicit
    Function FSaveAs() As Boolean

    Dim sFileName As String
    Dim InitialDir As String
    Dim SaveAsDialog As FileDialog
    Dim FileDest As Variant

    ChDrive "F"
    ChDir "F:\Public\Training Reports"

    sFileName = Sheet1.[b5]


    Set SaveAsDialog = Application.FileDialog(msoFileDialogSaveAs)
    FileDest = "F:\Public\Training Reports"

    With SaveAsDialog
    .InitialFileName = sFileName

    .Show

    End With

    On Error GoTo FileError

    FSaveAs = True
    Exit Function

    FileError:
    MsgBox "You forgot to select a Subject Name for this report.", vbRetryCancel

    FSaveAs = False
    End Function

    ********* All Code Ends Here*****************

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Post

    I don't do a whole lot of event programming in Excel, but...

    I think you just need to move your .Saved

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sQ As String
    sQ = MsgBox("Do you want to save this workbook ?", vbYesNoCancel)
    If sQ = vbCancel Then Cancel = True
    If sQ = vbYes Then
      If FSaveAs = False Then
        Cancel = True
      else
         ThisWorkbook.Saved = True
      end if
    End If
    End Sub
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2006
    Posts
    163
    Could the reason that nothing is saved be because there isn't actually any code that saves anything?

Posting Permissions

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