Printable View
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
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.Quote:
Originally Posted by 大灰狼1976
Can these be done using Msgbox?
Something like this maybe
Userform
Code: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
Code: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
Code:
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.
Quote:
Originally Posted by Paul_Hossler
I can live with this error. The main issue is solved. Thanks a lot Paul! Appreciate your help .
Quote:
Originally Posted by sgre
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
Code: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!
Quote:
Originally Posted by Paul_Hossler
This disables the X :
Code: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 :
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
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 :)
You're Welcome... Happy New Year! :thumb