Need to create a conditional incell comment...

[Jeffrey Green]

I have a spreadsheet with a column of pull down cells. Using INDIRECT, each cell's menu options are unique, based on the answer is in the cell above.
Each individual response gets looked up in a table and a code isretrieved for that answer.
In one particular situation, if the answer chosen in Cell B12 retrieves a code of "S", i need to ask a lengthy question in Cell B13. I can put it in an In-Cell comment, but i only want to appear if the previous cells repsonse code as "s" . . . . maybe have a modeless window open if the response code is s?????

thanks

 

[MorganO]

You could possibly use the worksheet_Change event to do what you are asking. In your particular situation, the following code might get you pointed in the right direction:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B12").Value = "S" And Range("B13") = "" Then
  Range("B13").Activate
  MsgBox ("Lengthy Question") ' or you could use:  UserForm1.Show
End If
End Sub

Take care.

Owen

 

[Peter_SSs]

.. or maybe use the Worksheet_SelectionChange event:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$B$13" And Range("B12").Value = "S" <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Long message"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>