How to get a dropdown box working

[74Pumpkin]

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]

Use a drop down from the Control Toolbox and put the code in the DropDown Change sub. Or use Validation (Data | Validation | List).

[74Pumpkin]

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]

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]

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]

It is the ListFillRange Property.

[74Pumpkin]

That did it. It works great. Thanks for the help.

[Zack Barresse]

Hi 74Pumpkin,

Glad you got it working! 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]

Thanks firefytr, Now that I know, I'll remember to do that.