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
– GSerg, Commented Jan 18, 2016 at 10:53
Sorry - I have tried declaring messageshown in the Workbook_Activate event and the Workbook_Open event. Thanks — Jonny Wright
– Jonny Wright, Commented Jan 18, 2016 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 stackoverflow.com/questions/2722146/… — Nathan_Sav
– Nathan_Sav, Commented Jan 18, 2016 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
– Carl Colijn, Commented Jan 18, 2016 at 12:07
Setting Require Variable Declaration within the VBE's Tools ► Options ► Editor property page will put Option Explicit 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'. — user4039065
– user4039065, Commented Jan 18, 2016 at 12:27

SierraOscar · Accepted Answer · 2016-01-18 12:12:05Z

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, 2016 at 12:12

answered Jan 18, 2016 at 12:01

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SierraOscar Over a year ago

Downvoter care to explain in what way this answer is unclear or unhelpful?

SilentRevolution Over a year ago

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.

SierraOscar Over a year ago

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 Over a year ago

@SilentRevolution In actual fact, I've upvoted 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.

SilentRevolution Over a year ago

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 · Accepted Answer · 2016-01-18 12:45:59Z

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, 2016 at 12:45

answered Jan 18, 2016 at 11:08

SilentRevolution

1,5132 gold badges16 silver badges31 bronze badges

Collectives™ on Stack Overflow

VBA Nested IF statement

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related