PDA

View Full Version : [SOLVED] BeforeSave and Userform: Issues with saving the excel file



sgre
12-04-2019, 02:48 PM
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

sgre
12-05-2019, 07:02 AM
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

sgre
12-05-2019, 01:08 PM
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

sgre
12-06-2019, 08:26 AM
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

sgre
12-30-2019, 02:43 PM
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

sgre
12-31-2019, 10:20 AM
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?

sgre
12-31-2019, 10:35 AM
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