CODEWITHSUNDEEP
vbaexcel
aaa
aaa

Reputation: 858

VBA - Unable to close excel

I'm new to VBA, I had this simple code that cause some cells to be mandatory and highlight the cells if they are empty when the workbook is opened. However, I'm unable to close my excel with the codes I have. Can anyone please tell me why?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Cells(2, 6).Value = "" Then
            MsgBox "Cell F2 requires user input", vbInformation, "Please filled up the mandatory cells"
        ElseIf Cells(2, 9).Value = "" Then
            MsgBox "Cell I2 requires user input", vbInformation, "Please filled up the mandatory cells"
        ElseIf Cells(4, 4).Value = "" Then
            MsgBox "Cell D4 requires user input", vbInformation, "Please filled up the mandatory cells"
        End If
        Cancel = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        If Cells(2, 6).Value = "" Then
            MsgBox "Cell F2 requires user input", vbInformation, "Please filled up the mandatory cells"
        ElseIf Cells(2, 9).Value = "" Then
            MsgBox "Cell I2 requires user input", vbInformation, "Please filled up the mandatory cells"
        ElseIf Cells(4, 4).Value = "" Then
            MsgBox "Cell D4 requires user input", vbInformation, "Please filled up the mandatory cells"
        End If
        Cancel = True
End Sub

    Private Sub Workbook_Open()
    If Cells(2, 6).Value = "" Then
            Range("F2").Interior.ColorIndex = 6
    ElseIf Cells(2, 9).Value = "" Then
            Range("I2").Interior.ColorIndex = 6
    ElseIf Cells(4, 4).Value = "" Then
            Range("D4").Interior.ColorIndex = 6
    End If
End Sub

Upvotes: 1

Views: 149

Answers (1)

jsotola
jsotola

Reputation: 2278

the cancel = true at the end of the sub cancels the exit process, so the workbook stays open

try this

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim aaa As Variant
    For Each aaa In Array("F2", "I2", "D4")
        If Range(aaa).Value = "" Then
            MsgBox "Cell " & aaa & " requires user input", vbInformation, "Please filled up the mandatory cells"
            Cancel = True
            Exit Sub
        End If
    Next aaa
    Cancel = False
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim aaa As Variant
    For Each aaa In Array("F2", "I2", "D4")
        If Range(aaa).Value = "" Then
            MsgBox "Cell " & aaa & " requires user input", vbInformation, "Please filled up the mandatory cells"
            Cancel = True
            Exit Sub
        End If
    Next aaa
    Cancel = False
End Sub

Private Sub Workbook_Open()
    Dim aaa As Variant
    For Each aaa In Array("F2", "I2", "D4")
        If Range(aaa).Value = "" Then Range(aaa).Interior.ColorIndex = 6
    Next aaa
End Sub

Upvotes: 4

Related Questions