If...Then

[McRead]

Hi,

I have a column (H) that contains the state names.

Could you please help me with a macro that does the following.

If any of the cells in column H has either "AL", "NJ", or "CA" then msgBox "Send Letter."

Thanks a lot in advance.

Regards,

McRead

 

[NateO]

Hello,

What would be the purpose of that Message Box, other than to irritate the end-user? Surely you don't want to loop through a long list and inundate yourself or someone else with a plethora of Message Boxes?

 

[McRead]

Hi NateO,

Sending that letter is very critical. We don't want people to miss it whatsoever.

This is one of the ways of ensuring a letter is sent.

A person sends, on an average, about 3 letters per day. So, you see how rare the occurence is.

Thanks

 

[Smitty]

Do you only want a single MsgBox, or one for every instance, which could get pretty old pretty fast?

What's the trigger going to be? Will the user need to send a letter at that point, or does that need to be addressed in the code as well?

Smitty

EDIT: Too late...Don't try feeding a 1-year old lunch and expect to make a timely response. :wink:

 

[phantom1975]

What event will trigger the message box? Do you want the message box to appear if someone has clicked in a cell in column H that meets that criteria? Do you want a message box to come up for each and every occurance in column H? What do you want the message box to say? Would it be better to just highlight the rows a special color so that it is visible that letters need to be sent out? Keep in mind that if you want message boxes for each occurance, what will happen is a barrage of message boxes. For instance, MESSAGE BOX, click OK, MESSAGE BOX, click OK, MESSAGE BOX, click OK......the user won't be able to do anything except click OK a bunch of times.

 

[McRead]

Hi PennySaver,

The idea here is to just alert the person who is updating the worksheet to send the letter every time these states come up.

Once he is alerted, he goes to a different workflow and sends the letter and that is the end of it.

If you have a better idea, please do let me know.

Thanks a lot

 

[Smitty]

Based on your response to Nate, how's this for a start:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Macro1()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Range([H2], [H65536].End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> c = "AL" <SPAN style="color:#00007F">Or</SPAN> c = "NJ" <SPAN style="color:#00007F">Or</SPAN> c = "CA" <SPAN style="color:#00007F">Then</SPAN>
                MsgBox "Send a letter to " & c.Value
                <SPAN style="color:#007F00">'  You can further identify the recipient</SPAN>
                <SPAN style="color:#007F00">'   based on your other cells in each row</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

EDIT: this would probably be better if your users are entering that state in H.

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
     <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = Range([H2], [H65536].End(xlUp))
    <SPAN style="color:#007F00">'   Only look at that range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target
            <SPAN style="color:#00007F">Case</SPAN> "AL", "NJ", "CA"
                MsgBox "Please send a letter to " & Target.Value
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[NateO]

Hello again,

Sending that letter is very critical. We don't want people to miss it whatsoever.

This I follow, so copying these records (rows) to a new sheet or formatting or something makes a lot of sense to me versus a popup.

With more than 1 message box, the user will have to memorize the entry (they might not even know what entry the message box pertains to). Might make the task even more tedious than nothing at all.

If I gave you a spreadsheet with 10,000 rows of data and it then gave you 5,000 popups, without scrolling, what would you make of that?

 

[McRead]

Hey PennySaver,

The message box is not showing.