PDA

View Full Version : [SOLVED] How to get a dropdown box working



74Pumpkin
11-21-2004, 07:53 PM
I currently have the following to hide and unhide the areas I want to view. I only need to see 14 or so of the 400+ rows. I would like to be able to do this with a dropdown box if possable.

My failed attempt was to make a dropdown box put the values into cell G3. But this would not activate the code.


The following works good when you type the number in cell G3


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$3" Then
Select Case Target.Value
Case 1
Rows("26:405").EntireRow.Hidden = True
Rows("12:25").EntireRow.Hidden = False
Range("H12").Select
Range("G3").Select
Case 2
Rows("12:25").EntireRow.Hidden = True
Rows("26:39").EntireRow.Hidden = False
Rows("40:405").EntireRow.Hidden = True
Range("G3").Select
Case 3
Rows("12:39").EntireRow.Hidden = True
Rows("40:53").EntireRow.Hidden = False
Rows("54:405").EntireRow.Hidden = True
Range("G3").Select
Case 4
Rows("12:53").EntireRow.Hidden = True
Rows("54:67").EntireRow.Hidden = False
Rows("68:405").EntireRow.Hidden = True
Range("H54").Select
Range("G3").Select
Case 5
Rows("12:67").EntireRow.Hidden = True
Rows("68:81").EntireRow.Hidden = False
Rows("82:405").EntireRow.Hidden = True
Range("H68").Select
Range("G3").Select

' This continues on to #27

Any ideas would help.

BTW. I am a newbie to VBA.( If it wasn;t obvious that is)

Jacob Hilderbrand
11-21-2004, 08:13 PM
Use a drop down from the Control Toolbox and put the code in the DropDown Change sub. Or use Validation (Data | Validation | List).

74Pumpkin
11-21-2004, 08:44 PM
I guess I need an example to get me started. I tried to put the code into the dropdown but it just doesnt work.

Jacob Hilderbrand
11-21-2004, 09:48 PM
Add a listbox from the Control Toolbox then add the code like this:


Option Explicit

Private Sub ListBox1_Change()
Select Case lisbox1.Value
Case 1
Rows("26:405").EntireRow.Hidden = True
Rows("12:25").EntireRow.Hidden = False
Range("H12").Select
Range("G3").Select
Case 2
Rows("12:25").EntireRow.Hidden = True
Rows("26:39").EntireRow.Hidden = False
Rows("40:405").EntireRow.Hidden = True
Range("G3").Select
Case 3
Rows("12:39").EntireRow.Hidden = True
Rows("40:53").EntireRow.Hidden = False
Rows("54:405").EntireRow.Hidden = True
Range("G3").Select
Case 4
Rows("12:53").EntireRow.Hidden = True
Rows("54:67").EntireRow.Hidden = False
Rows("68:405").EntireRow.Hidden = True
Range("H54").Select
Range("G3").Select
Case 5
Rows("12:67").EntireRow.Hidden = True
Rows("68:81").EntireRow.Hidden = False
Rows("82:405").EntireRow.Hidden = True
Range("H68").Select
Range("G3").Select
' This continues on to #27
End Sub

74Pumpkin
11-22-2004, 03:06 PM
Almost there. I can get the drop down to work if I type in the # into the Combo box. How do I populate the Combo Box with information so it drops down and shows the selections avaliable? I thought there was an option in the properties box but I cannot find it.

Thanks again!

Jacob Hilderbrand
11-22-2004, 03:52 PM
It is the ListFillRange Property.

74Pumpkin
11-22-2004, 03:57 PM
That did it. It works great. Thanks for the help.

Zack Barresse
11-22-2004, 04:44 PM
Hi 74Pumpkin,

Glad you got it working! :yes I'll go ahead and mark this thread Solved. You can do so for your own posts also by clicking 'Thread Tools' (top of the thread) --> 'Mark Solved' --> 'Perform Action'.

74Pumpkin
11-22-2004, 05:31 PM
Thanks firefytr, Now that I know, I'll remember to do that.