Expand cells when choosing dropdown option

[Nick70]

Hi,

I have an excel template which can be used by different users.

I would like to have a macro that expands or collapses a group of cells when a specific user-type is chosen in a "user" dropdown.

Details:

Dropdown is found in cell A4 and has option of choosing user A or user B.

If user A is chosen in dropdown then grouped cells A142 to A158 are expanded.
If user B is chosen in dropdown then grouped cells A142 to A158 are collapsed.

How would I do that?

Thanks
Nic

 

[trunten]

if you stick this in the sheet module for that sheet it should do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$4" Then
        Me.Range("A142:A158").EntireRow.Hidden = (StrComp(Target.Value, "User A", vbTextCompare) <> 0)
    End If
End Sub

 

[Nick70]

Thanks!
Nic