Call UserForm from Data Validation entry

[marley]

Hello all,
It has been about six months since I have had to use VBA and my brain is numb. I am trying to call up a particular UserForm based on the Data Validation choice that is made in a particular Cell. I can't seem to think this through.

The Cell is C14. When you land on it, you get a Data Validation drop down menu with three choices ( lets call them Larry, Curly and Moe ). If the user chooses "Larry", I want UserForm1 to pop up, "Curly" results in UserForm2, and a choice of "Moe" would result in UserForm3. So far, I have this:

Sub GetStooges()
If Range("C14").Value = Larry Then
UserForm1.Show
ElseIf Range("C14").Value = Curly Then
UserForm2.Show
ElseIf Range("C14").Value = Moe Then
UserForm3.Show
End If
End Sub

Any suggestions?

Thank you.
Marley

 

[Tom Urtis]

If you are not using Excel97 (that is, you are using Excel2000 or later), see if this helps. Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$14" Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Select Case Target.Value

Case "Larry"
UserForm1.Show

Case "Curly"
UserForm2.Show

Case "Moe"
UserForm3.Show

End Select
End Sub

 

[marley]

It still isn't working. ( BTW, I am using Office98 on a Mac ) Does it matter that the cell has a defined name? I would think that the absolute value of $C$14 would work even if the cell has a defined name, but maybe I am wrong?

marley

 

[Tom Urtis]

The code should work, I know it does on a PC but maybe something regarding Macs interfere with the sheet change event trigger.

The fact that cell C14 is assigned a name has no bearing on this code.

Also keep in mind that nothing will happen if the values in your data validation list are not exactly as they are in the code (which I took from your post). That means, case sensitivity, spelling, no spaces or characters other than exactly what the code says. Take a close look at your data validation list elements and make sure they are precisely the same as the code; if not change one or the other.

Also, make sure you put the code where I said, which is in the sheet module of the sheet where cell C14 is the one with data validation.

 

[marley]

The list and the code are identical. However, the cell is "Blank", or empty, by default. Nothing is going to be in that cell (C14) unless the user selects one of the three options from Data Validation. I am wondering, is this line in your code:
If IsEmpty(Target) Then Exit Sub
. . .causing the subroutine to stop before I get a chance to even select an item from Data Validation?

Thank you for your help on this one,
marley

 

[Tom Urtis]

No. That line means that no code shall be triggered when someone hits the delete key to clear the contents of cell C14.

When a change to the cell's value is made, such as a value is entered or edited, or when data validation in postExcel97 is used to enter a new value, the code will be triggered, again, assuming you placed it in the correct location (sheet module of sheet hosting the C14 data validated cell), and that it is really C14 we are talking about.

Other than that, if someone reading this who also uses a Mac, maybe they can test this code on their system, because it works on mine. I cannot duplicate the problem you are experiencing.