Call UserForm from Data Validation entry

marley

Board Regular
Joined
Mar 18, 2002
Messages
94
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

Board Regular
Joined
Mar 18, 2002
Messages
94
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

Board Regular
Joined
Mar 18, 2002
Messages
94
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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.
 

Forum statistics

Threads
1,147,675
Messages
5,742,546
Members
423,737
Latest member
tom_xls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top