Consulting

Results 1 to 13 of 13

Thread: BeforeSave and Userform: Issues with saving the excel file

  1. #1
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location

    BeforeSave and Userform: Issues with saving the excel file

    Hi! I am trying to capture and save comments when the user tries to save the excel through a BeforeSave function and user form. The code works fine but it saves the excel even if user cancels the userform. Here are the codes:

    The BeforeSave code:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Comments_form.Show
    If Comments_form.Cancel = True Then Cancel = True
    End Sub



    The Userform code w.r.t. cancel click is:



    Private Sub Cancel_Click()

    Comments_form.Hide
    End Sub
    Last edited by sgre; 12-04-2019 at 03:17 PM.

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    452
    Location
    Hi sgre!
    Welcome to vbax forum.
    One question, Why not use msgbox?

    --Okami

  3. #3
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location
    Quote Originally Posted by 大灰狼1976 View Post
    Hi sgre!
    Welcome to vbax forum.
    One question, Why not use msgbox?

    --Okami
    Hi! Because I wanted to do three things 1)capture comments made by users 2) save it for later reference 3)Assign a time stamp to each comment made.
    Can these be done using Msgbox?

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,883
    Location
    Something like this maybe



    Userform

    Option Explicit
    
    
    Private Sub cbLogExit_Click()
    
    
        With Worksheets("Log")
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Format(Now, "yyyy-mm-dd hh:mm:ss")
            'use (0,1) in case previous comment was blank
            .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 1).Value = Me.tbComments.Text
        End With
    
    
        bCancel = False
        
        Me.Hide
        Unload Me
    
    
    End Sub
    
    
    Private Sub cbReturn_Click()
    
    
        bCancel = True
    
    
        Me.Hide
        Unload Me
    
    
    End Sub

    Thisworkbook

    Option Explicit
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Not ThisWorkbook.Saved Then
            Load UserForm1
            UserForm1.Show
    
    
            Cancel = bCancel
        End If
    End Sub
    
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Load UserForm1
        UserForm1.Show
    
    
        Cancel = bCancel
    End Sub
    
    
    Standard module
    
    
    
    Option Explicit
    
    
    Public bCancel As Boolean
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location
    Thank you Paul! Your code helped to deal with the issue. However, now in case I close the file before saving, the user form shows and after I submit the comments, again it asks if the changes need to be saved and then again the user form shows.


    Quote Originally Posted by Paul_Hossler View Post
    Something like this maybe



    Userform

    Option Explicit
    
    
    Private Sub cbLogExit_Click()
    
    
        With Worksheets("Log")
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Format(Now, "yyyy-mm-dd hh:mm:ss")
            'use (0,1) in case previous comment was blank
            .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 1).Value = Me.tbComments.Text
        End With
    
    
        bCancel = False
        
        Me.Hide
        Unload Me
    
    
    End Sub
    
    
    Private Sub cbReturn_Click()
    
    
        bCancel = True
    
    
        Me.Hide
        Unload Me
    
    
    End Sub

    Thisworkbook

    Option Explicit
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Not ThisWorkbook.Saved Then
            Load UserForm1
            UserForm1.Show
    
    
            Cancel = bCancel
        End If
    End Sub
    
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Load UserForm1
        UserForm1.Show
    
    
        Cancel = bCancel
    End Sub
    
    
    Standard module
    
    
    
    Option Explicit
    
    
    Public bCancel As Boolean

  6. #6
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location
    I can live with this error. The main issue is solved. Thanks a lot Paul! Appreciate your help .

    Quote Originally Posted by sgre View Post
    Thank you Paul! Your code helped to deal with the issue. However, now in case I close the file before saving, the user form shows and after I submit the comments, again it asks if the changes need to be saved and then again the user form shows.

  7. #7
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,883
    Location
    Try this bit of logic

    'Save' doesn't close the WB, but just saved

    'Close' shows the Save dialog allowing you to save, but if you Cancel that, it just exits




    Option Explicit
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    
        bCloseCalled = True
    
    
        Load UserForm1
        UserForm1.Caption = "Workbook_BeforeClose"
        UserForm1.cbLogExit.Caption = "Log Comments, Save, and Close"
        UserForm1.Show
    
    
    
    
        Cancel = bCancel
    End Sub
    
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        
        If Not bCloseCalled Then
            Load UserForm1
            UserForm1.Caption = "Workbook_BeforeSave"
            UserForm1.cbLogExit.Caption = "Log Comments and Save"
            UserForm1.Show
        End If
    
    
        Cancel = bCancel
    End Sub
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location
    Thank you Paul and sorry for responding late.
    I went ahead with the 1st solution you gave with a msgbox to save the sheet before closing. It works fine.

    There is one problem left - the user form has the option of being cancelled by clicking the cross on the top right corner. For some reason, if I click that, the workbook gets saved. I would like to return to workbook without it being saved. Any suggestions please?

    Thanks a lot!


    Quote Originally Posted by Paul_Hossler View Post
    Try this bit of logic

    'Save' doesn't close the WB, but just saved

    'Close' shows the Save dialog allowing you to save, but if you Cancel that, it just exits




    Option Explicit
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    
        bCloseCalled = True
    
    
        Load UserForm1
        UserForm1.Caption = "Workbook_BeforeClose"
        UserForm1.cbLogExit.Caption = "Log Comments, Save, and Close"
        UserForm1.Show
    
    
    
    
        Cancel = bCancel
    End Sub
    
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        
        If Not bCloseCalled Then
            Load UserForm1
            UserForm1.Caption = "Workbook_BeforeSave"
            UserForm1.cbLogExit.Caption = "Log Comments and Save"
            UserForm1.Show
        End If
    
    
        Cancel = bCancel
    End Sub

  9. #9
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    This disables the X :

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        If CloseMode = vbFormControlMenu Then
            Cancel = True
            MsgBox "Sorry, this button is disabled.", vbOKOnly, "'X' not in use!"
        End If
    End Sub
    Semper in excretia sumus; solum profundum variat.

  10. #10
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location
    Hi! Thank you for your quick response. I don't know why but this is not working for me. I also tried :

    PrivateSub UserForm_QueryClose(Cancel AsInteger, CloseMode AsInteger)
    'Prevent user from closing with the Close box in the title bar.
    If CloseMode <> 1Then Cancel = 1
    MsgBox "Sorry, this button is disabled.", vbOKOnly, "'X' not in use!"

    EndSub

    Quote Originally Posted by paulked View Post
    This disables the X :

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        If CloseMode = vbFormControlMenu Then
            Cancel = True
            MsgBox "Sorry, this button is disabled.", vbOKOnly, "'X' not in use!"
        End If
    End Sub

  11. #11
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    Did you put the sub in the correct userform code module?
    Semper in excretia sumus; solum profundum variat.

  12. #12
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location
    Yep! That was the issue. I had placed it in Thisworkbook.. It works now.. Thank you so so much Paul.. You are awesome

  13. #13
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    You're Welcome... Happy New Year!
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

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