First Post! VBA. Hiding unhiding rows based on a cell from a different sheet. Please Help!!

[zpsale01]

First time poster and very excited and grateful to have found a place with such genorous and talented members.

My Question.

Ok, I have a combo box list in Sheet1. When a specific division in that list is selected a number that corresponds to that division is changed in a cell in a different tab. For example when I select Div 1 in the drop down, cell A1 in Sheet2 changes to "1". When nothing is selected in the drop down (Blank) the cell is changed to 0.

I would like to create a VBA in Sheet1 that when a specific division is selected the VBA looks at cell A1 in sheet2 and then hides a specific number or rows in sheet1 based on that specified number.

For example.

If Cell Sheet2!$A$1 = "2" then hide rows 2-10 in Sheet1
If Cell Sheet2!$A$1 = "3" then hide rows 11-20 in Sheet1
If Cell Sheet2!$A$1 = "0" then hide rows 21-30 in Sheet1

Please help!!!

thank you.

Also, each time a new selection occurs in the dropdown and the Sheet2 cell A1 is changed to a different number then the previous rows that were hidden should unhide.

 

[Andrew Poulsom]

Welcome to MrExcel.

What is causing A1 on Sheet2 to change exactly? If it's a macro why don't add some row hiding code to it?

 

[zpsale01]

Thanks for the prompt response.

I have a drop down on sheet1 that is a combo box and based on what is selected there, a number is assigned in sheet 2.

 

[Andrew Poulsom]

Yes, but what causes A1 on Sheet2 to change?

 

[zpsale01]

when creating a combobox, if you right click it -> Format -> control tab, you can assign a link cell. My link cell is in Sheet2, and if the second selection in the combo box is selected then the cell in sheet2 ='s 2, if the third selection is chosen then the cell in sheet2 gets a 3.

 

[zpsale01]

my thought process on what the code should be but the ElseIf is not recognized

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "List!$C$2" Then
If Target.Value = "1" Then
Rows("7:294").EntireRow.Hidden = True
ElseIF

 

[Andrew Poulsom]

Assign this macro to your dropdown:

Sub Hiding()
    With ActiveSheet
        Select Case .DropDowns(Application.Caller).ListIndex
            Case 0
                .Rows("2:10").Hidden = False
                .Rows("11:20").Hidden = False
                .Rows("21:30").Hidden = True
            Case 2
                .Rows("2:10").Hidden = True
                .Rows("11:20").Hidden = False
                .Rows("21:30").Hidden = False
            Case 3
                .Rows("2:10").Hidden = False
                .Rows("11:20").Hidden = True
                .Rows("21:30").Hidden = False
        End Select
    End With
End Sub