VBA Nested IF statement

vba excel nested-if
Jonny Wright picture Jonny Wright · Jan 18, 2016 · Viewed 17.7k times · Source

I want to show a message box when a specific cell has a particular value in it. I have done this with the following code;

If Range("P8") = "Y" Then
        MsgBox "Message here"
End If

This is within the Worksheet_Change sub so shows the message box everytime another cell value changes. I have tried to get around this by adding a boolean variable, set to true when the messagebox has been shown the first time;

If Range("P8") = "Y" Then
    If messageshown = False Then
        messageshown = True
        MsgBox "Message here"
    Else
    End If
Else
End If

However the message box still shows every time I change a cell in the worksheet. I have a feeling it';s to do with the way I have written the nested if statement but have tried various different ways and orders of where I place else and end if but to no avail.

Answer

SierraOscar picture SierraOscar · Jan 18, 2016

Use the Target argument instead - this refers to the actual cell being changed, which is what you are interested in. Test the address of the Target to see if it's the cell you need and then act accordingly. This will stop the message showing when another cell is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = "$P$8" And .Value = "Y" Then MsgBox "Message here"
    End With
End Sub

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More