CODEWITHSUNDEEP
vbaexcelmsgbox
Gabriel Lima
Gabriel Lima

Reputation: 21

VBA - Open a msgbox when cell value = 1

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?

Upvotes: 2

Views: 1777

Answers (6)

Gabriel Lima
Gabriel Lima

Reputation: 21

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?

Upvotes: 0

cyboashu
cyboashu

Reputation: 10433

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

Upvotes: 2

Ahmed AbdelKhalek
Ahmed AbdelKhalek

Reputation: 192

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

Upvotes: 2

M1chael
M1chael

Reputation: 251

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

Upvotes: 2

pokemon_Man
pokemon_Man

Reputation: 902

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

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 1

Related Questions