Pop up message box on certian input text

[springbrook]

What I want to do is:

Whenever the text "Rib Roof" is typed or entered into a cell or a merged cell, i need a message box to pop up saying "Do you wish to reveres roll the colour ?"

This is only required in 1 certain file, ie: not all other excel files.

Please help
Cheers and Merry Christmas

Thanks
Mark

 

[Zack Barresse]

Hi there,

Can you get rid of your merged cells? I highly recommend it. You could try this in your worksheet code pane ...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Compare</SPAN> <SPAN style="color:#00007F">Text</SPAN>

<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> Prompt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Value = "Rib Roof" <SPAN style="color:#00007F">Then</SPAN>
        Prompt = "Do you wish to reveres roll the colour?"
        Title = "Reverse Colour?"
        <SPAN style="color:#00007F">If</SPAN> MsgBox(Prompt, vbYesNo, Title) = vbYes <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#007F00">'... code here</SPAN>
            <SPAN style="color:#007F00">'example:</SPAN>
            MsgBox "Action performed on " & Target.Address & "."
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Right click sheet tab name, select View Code, paste on right. Alt + Q will return to Excel and this will fire when you change/enter a cell value. It will exit (not fire) if you have more than one cell selected (does not pertain to merged cells).


HTH

 

[springbrook]

Thanks, that worked well but how do I make it work for different spelling ? ie: Rib Roof or RIB ROOF.

Why is it advisable to not use merged cells ? as my small program has quite a few merged cells?

Cheers and thanks

 

[Zack Barresse]

If you use the entire code I posted, it will work for all type of case sensitivity; specifically the Option Compare Text.

Merged cells generally present large problems, especially when dealing with VBA. Formulas aren't too bad, but they're still a pain to deal with. You'll save yourself some major headaches if you get rid of them now, especially if you're going to start using VBA.