BeforeClose doesn't close my VBA Excel sheet

Question

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

+3

vba excel-vba excel if-statement

Siroffinance 21 Mar 17 at 8:43

source to share

1 answer

Macro Man · Accepted Answer · 2017-03-21T08:54:23+0000

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 ...

BeforeClose doesn't close my VBA Excel sheet

More articles: