VBA Nested IF statement

Question

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.

Have you declared `messageshown` at the class module level, or as `static` inside the sub? — GSerg, Jan 18 '16 at 10:53
Sorry - I have tried declaring `messageshown` in the `Workbook_Activate` event and the `Workbook_Open` event. Thanks — Jonny Wright, Jan 18 '16 at 10:57
You need to declare them at the top as public, or on their own in a separate code module, to be used again, so public messageshown as boolean http://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba — Nathan_Sav, Jan 18 '16 at 11:01
A small heads up: note that while Excel formulas generally treat "Y" and "y" as identical texts, in VBA that's not always the case. So instead of using = "Y", you'd better check with "y" as well, or compare LCase(Range("P8")) with "y". Also see the VBA help on "option compare" for this, or the VBA function StrComp. — Carl Colijn, Jan 18 '16 at 12:07
Setting **Require Variable Declaration** within the VBE's Tools ► Options ► Editor property page will put **[Option Explicit](https://msdn.microsoft.com/en-us/library/y9341s4f.aspx)** statement at the top of each newly created code sheet. This will avoid silly coding mistakes like misspellings and undeclared variable use as well as influencing you to use the correct variable type in the declaration. Variables created on-the-fly without declaration are all of the variant type. Using **Option Explicit** is widely considered 'best practice'. — , Jan 18 '16 at 12:27

SierraOscar · Answer 1 · 2016-01-18T12:12:05.477

6

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

edited Jan 18 '16 at 12:12

answered Jan 18 '16 at 12:01

SierraOscar

17,507
6
40
68

Downvoter care to explain in what way this answer is unclear or unhelpful? – SierraOscar Jan 18 '16 at 12:12
As a matter of fact I would. I didn't downvote because it is unclear or unhelpful mind you. The answer you've provided is functionally identical to the answer I've provided an hour ago. I understand and accept the possibility of a fairly easy question being answered simultaneously but in my opinion that is limited to answer within a few minutes of each other. I didn't feel, but prove me wrong, your answer **added** relevant information. – SilentRevolution Jan 18 '16 at 12:21
1

I accept your premise - however firstly, this isn't a "first come, first serve" site and down votes should only be used when an answer is unclear or not useful **to the OP**. Secondly, your answer uses `Target` and then still uses `Range("P8")` which is redundant as you've already determined that the target cell is `P8` and from a coding perspective is inefficient because you have to change the address in more than one location. I didn't downvote *your* answer though based on that, because ultimately it still helps the OP. – SierraOscar Jan 18 '16 at 12:26
1

@SilentRevolution In actual fact, I've *up*voted your answer because that's how the voting system is designed to work - if it's helpul, vote up. If it's wrong or unhelpful, vote down. Don't vote down because someone posted something that looks like your answer. – SierraOscar Jan 18 '16 at 12:29
I know this isn't a first come first serve deal here. I apologize for the downvote (which I've removed), it was gut reaction. Now that I understand what the difference is I'll gladly upvote your answer. I want to end this by saying that I wouldn't have downvoted if you had explained the difference, I am not a code genius but I am learning. I will not get into a discussion about when to donwvote or not, but I have different views on that compared to yours. – SilentRevolution Jan 18 '16 at 12:43
@SilentRevolution That's fine, no need to apologise and I respect your right to your own views, just that downvotes without comments seem to be somewhat of a plague on here and ultimately the goal is to help a much wider audience so if something is worthy of a downvote it should have the reason made clear - even if just to let others know (even if the person getting the down vote is being an ass about it, which unforunately also happens a lot) – SierraOscar Jan 18 '16 at 12:51

SilentRevolution · Accepted Answer · 2016-01-18T12:45:59.823

4

Try this code, it first checks which cell is changed, if it is anything but P8, it will not pop the messagebox.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$P$8" Then
        If Range("P8") = "Y" Then
            MsgBox "This works"
        End If
    End If
End Sub

As pointed out by Macro Man, there is a more optimal, more efficient option.

edited Jan 18 '16 at 12:45

answered Jan 18 '16 at 11:08

SilentRevolution

1,495
1
16
31

VBA Nested IF statement

2 Answers2

Linked