Pop-up message for certain options in a drop-down menu

[highlandhouse]

Hi,

I'm wondering if there's a way to have a pop-up message if certain options are selected from a drop-down menu. I'm using Excel 2010 to create a form for information regarding medications. I've used Data Validation to create drop-down menus of all the possible medications that the form could be used for, but certain medications require an additional form, so I want a pop-up message if any of those meds are selected to remind the user that they must also include the additional form. I work in human services, and while I'm fairly computer savvy (and quite good at following directions!), I am far from being an IT person or a programmer, so please bear with me if this requires more explaining than usual!

Thanks for your help!
Kim

 

[MickG]

Say you had a Validation List in "G1" and the list consisted of Medications:- "Med 1", Med 2", "Med 3" etc.
Then by Selecting any of the medication shown in code, A msgbox will appear:- See code:-
To enter code
Righ Click the sheet tab, Select "View Code", VB Window appears.
Paste code below into VBWindow, Close Vb window.
To run code:- Select cell "G1" and Change the value.
If you select a value in the code the Msgbox will show.
NB:- Alter values to suit your need !!.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "G1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Target
        [COLOR="Navy"]Case[/COLOR] "Med 1", "Med 5", "Med 8", "Med 10": MsgBox "Please Use Special form with this selection"
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[highlandhouse]

Say you had a Validation List in "G1" and the list consisted of Medications:- "Med 1", Med 2", "Med 3" etc.
Then by Selecting any of the medication shown in code, A msgbox will appear:- See code:-
To enter code
Righ Click the sheet tab, Select "View Code", VB Window appears.
Paste code below into VBWindow, Close Vb window.
To run code:- Select cell "G1" and Change the value.
If you select a value in the code the Msgbox will show.
NB:- Alter values to suit your need !!.

Private [COLOR=Navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=Navy]As[/COLOR] Range)
[COLOR=Navy]If[/COLOR] Target.Address(0, 0) = "G1" [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]Select[/COLOR] [COLOR=Navy]Case[/COLOR] Target
        [COLOR=Navy]Case[/COLOR] "Med 1", "Med 5", "Med 8", "Med 10": MsgBox "Please Use Special form with this selection"
    [COLOR=Navy]End[/COLOR] Select
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Just to clarify... Am I pasting the code into the VB window for Sheet1 (where the drop-down menus will appear) or Sheet2 (where the lists they correspond to are)?

Thanks for your help!!
Kim

 

[highlandhouse]

I think I answered my first question... but now I'm getting an error message.

I had to put the code in a number of times (it seems like it could only manage a certain number of options, so I broke it into three groups of meds). Plus, I had to duplicate it for 6 cells because the same drop-down menu is available 6 times. Now I'm getting an error message "Compile error: Ambiguous name detected: Worksheet_Change" for the first line of the code the second time it appears.

Is there another way to change it that I don't need to break it into three sections and then duplicate it 6 times for each of the drop-downs? Or do I just need to rename that line each time??

 

[MickG]

You can only have one "change event" per sheet, but you can have the same code for a number of cell.
I think, as you are now aware the code goes in the sheet with the actual dropdown boxes.
The code below is available for cells ""B1,D1 & "G1:H1" change the cells to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1,D1,G1:H1")) Is Nothing Then
    Select Case Target
        Case "Med 1", "Med 5", "Med 8", "Med 10": MsgBox "Please Use Special form with this selection"
    End Select
End If
End Sub

Regrds Mick

 

[highlandhouse]

This still isn't working. Here's exactly what I put in:

Private Sub Worksheet_Change16(ByVal Target As Range)
If Not Intersect(Target, Range("A18:A23")) Is Nothing Then
Select Case Target
Case "Abilify*", "Clozaril*", "Fanapt*", "Fluphenazine*", "Geodon*", "Haldol*", "Invega*", "Loxitane*", "Mellaril*", "Moban*", "Navane*", "Orap*", "Perphenazine*", "Prolixin*", "Risperdal*", "Seroquel*", "Stelazine*", "Thioridazine*", "Thorazine*", "Zyprexa*": MsgBox "Please attach current Roger's monitor!"
End Select
End If
End Sub

I'm not getting any sort of error message, but it's not giving me a message box. Any other suggestions?

 

[MickG]

It won't work with the "16" in the Title !!!
Also I don't understand the necessity for the "Asterisk",this ususlly denoted a WildCard Character, in this case whats in the drop down is in the code ???
Without the "16" it worked for Me !!!
Regrds Mick

 

[highlandhouse]

Ha! I don't even know where the 16 came from! It works, thanks so much!!!! I had originally put the *s in to flag meds that required the other form... now that this works, I can probably get rid of them, though!

Thanks again

 

[MickG]

Your welcome
Regrds Mick

 

[Marlis]

Hi Mick:
How would you apply this to an ActiveControl dropbox?