BeforeClose doesn't close my VBA Excel sheet
So I was trying to put together a small Excel sheet that has a record. Enter it. So whenever the sheet is closed, name, date and time are added.
So basically I have three macros, I only mentioned two. The main macro will ask if I want to close the sheet and I will have to answer "yes" or "no". This works great. If I click yes, the main macro will call the helper macro which will ask me to enter a string. If this inbox is empty or the recording is canceled, I want the main south to stop and cancel the Close process. Which doesn't seem to work. The error in the code seems pretty clear to me, but I don't know how to prevent it and find a better solution. If you could help me come up with a solution, I would really appreciate it.
This line of code seems to be a problem:
If Cancel_Button_LOG = False Then Cancel = True
Here I will add condensed versions of two macros
Public Sub Add_Entry_to_Log()
Dim i As Integer
Dim response As Variant
Cancel_Button_LOG = True
response = InputBox("Please enter your Name", "Name")
If response <> "" Then
Else
Cancel_Button_LOG = False
MsgBox "Please enter your name", vbExclamation + vbOKOnly, "Name"
End If
Worksheets("Log").Protect "secret"
ThisWorkbook.Save
End Sub
Now I want to use a variable Cancel_Button_log
to cancel the main unit:
Dim answer As Variant
answer = MsgBox("Are your sure you want to close the workbook?", vbYesNo) Cancel = False
Select Case answer
Case Is = vbYes
Worksheets("Log").Unprotect "secret"
Call Test
Call Add_Entry_to_Log
If Cancel_Button_LOG = False Then Cancel = True
Worksheets("Log").Protect "secret"
Case Is = vbNo
Cancel = True
End Select
ThisWorkbook.Save
End Sub
source to share
I think you do it the hardest way. If I understand your requirements correctly, you can replace all your code in a module with ThisWorkbook
something like this:
Const WB_LOG As String = "Log" '// name of sheet that the log is in
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Do you really want to close the workbook?", vbYesNo) = vbYes Then
With Sheets(WB_LOG)
.Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 2).Value = Array(Environ$("USERNAME"), Now)
End With
ThisWorkbook.Save
Else
Cancel = True
End If
End Sub
Private Sub Workbook_Open()
With Sheets(WB_LOG)
.Protect Password:="secret", UserInterfaceOnly:=True
.Range("A1:B1").Value = Array("USERNAME", "TIMESTAMP")
End With
End Sub
This would eliminate the need to manually insert their name manually (assuming their system username would be sufficient *), and also negate the need to unprotect the sheet every time I used the parameter UserInterfaceOnly
.
* Environment variables such as %USERNAME%
can be faked if the user wants to do it and knows how, however, someone entering their name in the text field is even easier to fake ...
source to share