VBA - Open a msgbox when cell value = 1

Question

I need help with a very basic vba macro. When the value in A6 is equal 1 a msgbox needs to appear in the workstation. I developed the below code but the problem is that when I add any other information in the spreadsheet (for example, if I write "i love pizza" in B6) the msgbox will prompt again and I need it to prompt just one time, just when I set the value of A6 = 1. Could you please help me?

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A6").Value = 1 Then
    MsgBox "THIS IS A MSGBOX."
End If

End Sub

@edit I forgot one very important thing... the value "1" is getted with a VLOOKUP so its not insert manually by the user, i'm sorry about this. I tried the codes you people answered and it worked just when I put the vlue manually and as I said in the edit start, the value is set with a VLOOKUP. Any other tips, please?

score 2 · Answer 1 · answered Mar 10 '17 at 19:42

You need to check if the change is due to the cell A6 being changed, rather than a different cell. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 And Target.Row = 6 Then
    If Target.Value = 1 Then
      MsgBox "THIS IS A MSGBOX."
    End If
  End If
End Sub

score 2 · Answer 2 · answered Mar 10 '17 at 19:44

You can use this code instead of the previous one

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Me.Range("A6").Address And Me.Range("A6").Value = 1 Then
        MsgBox "THIS IS A MSGBOX."
    End If
End Sub

Target is the changed cell in Excel. so for every change event, I check if the target is Range("A6").

score 2 · Answer 3 · answered Mar 10 '17 at 19:45

Pertaining to the statment : and I need it to prompt just one time, you need to save the oldvalue. So the prompt is displayed only once, when you set the value to 1. If A6 is already 1 and then you type 1 again, no prompt.

Option Explicit

Dim oldVal
Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Address = "$A$6") Then
        If Target.Value = 1 And Target.Value <> oldVal Then
            oldVal = Target.Value
            MsgBox "Test"
        End If
    End If
End Sub

Rather than use a module-scope variable here, it may make sense to use a local-scope Static variable, i.e. declare the variable using "Static oldVal" within the subroutine. — M1chael, Mar 11 '17 at 13:28

score 1 · Answer 4 · answered Mar 10 '17 at 19:39

You need to check inside the Worksheet_Change event, that only if Cell "A6" is pressed, then continue. And afterwards, check if the value of the cell equals 1.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A6")) Is Nothing Then
    If Target.Value = 1 Then MsgBox "THIS IS A MSGBOX."
End If

End Sub

pokemon_Man · Answer 5 · 2017-03-10T19:42:10.967

1

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$6" Then
        If Target.Value = 1 Then
            MsgBox "THIS IS A MSGBOX."
        End If
    End If    
End Sub

edited Mar 10 '17 at 19:42

answered Mar 10 '17 at 19:41

pokemon_Man

902
1
9
25

No, you mean `Target.Address= "$A$6"` – Shai Rado Mar 10 '17 at 19:41

score 0 · Answer 6 · answered Mar 12 '17 at 02:38

I forgot one very important thing... the value "1" is getted with a VLOOKUP so its not insert manually by the user, i'm sorry about this. I tried the codes you people answered and it worked just when I put the vlue manually and as I said in the edit start, the value is set with a VLOOKUP. Any other tips, please?

VBA - Open a msgbox when cell value = 1

6 Answers6