VBA Message Box

[BankingExcel]

HI,

I want a message box to automatically appear conditional on a checklist being completed. I am currently using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B3").Value = 0 Then
MsgBox "Checklist Completed- When XXXXX"
End If
End Sub

B3 contains a countif, i.e. when it reaches 0 all items in checklist have been ticked off. This is when I want a message box to appear. Currently I have to go up and click into B3 to get the message and then when I click any other random cell it also pops up. I want the message box to come up once automatically once B3 reaches 0. IS that possible?

Many thanks in advance.

 

[offthelip]

put it in the worksheet calculate event like this:

Private Sub Worksheet_Calculate()
If Range("B3").Value = 0 Then
MsgBox "Checklist Completed- When XXXXX"
End If
End Sub

 

[dmt32]

Hi
welcome to forum

Place following in your sheets code page & see if does what you want

Private Sub Worksheet_Calculate()
    With Me.Range("B3")
        If Val(.ID) <> .Value Then
            .ID = .Value
            If .Value = 0 Then MsgBox "Checklist Completed- When XXXXX", 64, "Completed"
        End If
    End With
End Sub

Msgbox should only appear once when cell value changes to 0

Dave

 

[BankingExcel]

Hi Dave,

Many thanks for your reply- I have copied your code as suggested but no message box is now appearing. Do you know what the issue might be?

Kind regards,
Maria

Private Sub Worksheet_Calculate()
With Me.Range("B3")
If Val(.ID) <> .Value Then
.ID = .Value
If .Value = 0 Then MsgBox "Checklist Completed- When XXXXX", 64, "Completed"
End If
End With
End Sub

 

[dmt32]

Have placed code in the sheets code page?

Right click tab > View Code

Dave