View Full Version : [SOLVED:] 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
大灰狼1976
12-04-2019, 08:23 PM
Hi sgre!
Welcome to vbax forum.
One question, Why not use msgbox?
--Okami
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?
Paul_Hossler
12-05-2019, 08:39 AM
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
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.
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
I can live with this error. The main issue is solved. Thanks a lot Paul! Appreciate your help .
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.
Paul_Hossler
12-06-2019, 09:22 AM
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
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!
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
paulked
12-30-2019, 03:11 PM
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
Hi! Thank you for your quick response. I don't know why but this is not working for me. I also tried :
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Prevent user from closing with the Close box in the title bar.
If CloseMode <> 1 Then Cancel = 1
MsgBox "Sorry, this button is disabled.", vbOKOnly, "'X' not in use!"
End Sub
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
paulked
12-31-2019, 10:29 AM
Did you put the sub in the correct userform code module?
Yep! That was the issue. I had placed it in Thisworkbook.. It works now.. Thank you so so much Paul.. You are awesome :)
paulked
12-31-2019, 12:04 PM
You're Welcome... Happy New Year! :thumb
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.