Help with worksheet

Help with worksheet_change

I just want to start by saying that i'm not very experienced in VBA (which you might notice) so I would really appreciate help on a not-too-advanced level.

To my problem: I am trying to create this excel sheet in which the user is prompted to choose what kind of role/title they have from a dropdown list. Depending on the chosen role in this list, different rows further down in the sheet should be hidden or visible. I have gotten this to work by using the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) If ActiveSheet.Range("E14").Value = "Role1" Then Range("19:21, 23:23, 25:25, 27:28, 30:30, 32:33").Select Selection.EntireRow.Hidden = True Range("18:18, 22:22, 24:24, 26:26, 29:29, 31:31").Select Selection.EntireRow.Hidden = False ElseIf ActiveSheet.Range("E14").Value = "Role2" Then Range("19:21, 23:23, 24:28, 30:33").Select Selection.EntireRow.Hidden = True Range("18:18, 22:22, 29:29").Select Selection.EntireRow.Hidden = False End If End Sub

The code goes on with a few more roles for selection in cell E14 that i left out here but you get the point. However, when a role is chosen and the relevant cell rows are hidden/visible as described in the code, all other changes in the worksheet that are done after that seems to trigger the worksheet_change again. I want the choice of roles to just trigger when the choice is made and then make it possible to keep working in the document without it checking the value of that cell.

It should however be possible to change the role if, for lets say, you chose the wrong role in your first try.

Was looking into Application.EnableEvents but once it was set to False i could not get it enabled again when choosing a new role.

Hope you understand my problem even though the confusing explanation.

//Take2

Found a solution myself. Added this around the expression:

If Target.Address = "$E$14" Then

EndIf

In the sub below, I pointed to the relevant line.

The rest of the code below is just a suggestion on how to make your code easier to read ,write, and maintain.

These Constants should be in a Standard Module so they can be used in any code anywhere in the Workbook.

Code:

Option Explicit Const Role1HiddenRange As String = "19:21, 23:23, 25:25, 27:28, 30:30, 32:33" Const Role1VisibleRange As String = "18:18, 22:22, 24:24, 26:26, 29:29, 31:31" Const Role2HiddenRange As String = "19:21, 23:23, 24:28, 30:33" Const Role2VisibleRange As String = "18:18, 22:22, 29:29"

This is how I would use them in your sub

Code:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim HideRange As String Dim ShowRange As String If Not Target.Address = "$E$14" Then Exit Sub '<----------- Select Case Target.Value Case "Role1" HideRange = Role1HiddenRange ShowRange = Role1VisibleRange Case "Role2" HideRange = Role2HiddenRange ShowRange = Role2VisibleRange Case Else: Exit Sub End Select Range(HideRange).EntireRow.Hidden = True Range(ShowRange).EntireRow.Hidden = False End Sub

Notice that the Case codes are identical, except for the numbers? Write the first one, then copy and paste it as many times as you have "Roles." Then just change the numbers in each line. I R such a LAzy Typise :D

BUT: This is the way I would write a Worksheet Event Sub, This way I can write code for the same Event with different outcomes

Code:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$14" Then HideShow Target If Target.Address = "$D$14" Then OtherSub Target End Sub Sub HideShow(Target) Dim HideRange As String Dim ShowRange As String ' Edit: Not needed in this sub: If Not Target.Address = "$E$14" Then Exit Sub Select Case Target.Value Case "Role1" HideRange = Role1HiddenRange ShowRange = Role1VisibleRange Case "Role2" HideRange = Role2HiddenRange ShowRange = Role2VisibleRange Case Else: Exit Sub End Select Range(HideRange).EntireRow.Hidden = True Range(ShowRange).EntireRow.Hidden = False End Sub

target is the range that triggers the event code when its value is changed.

Code:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$E$14" Then Exit Sub Select Case Target.Value Case "Role1" Range("19:21, 23:23, 25:25, 27:28, 30:30, 32:33").EntireRow.Hidden = True Range("18:18, 22:22, 24:24, 26:26, 29:29, 31:31").EntireRow.Hidden = False Case "Role2" Range("19:21, 23:23, 24:28, 30:33").EntireRow.Hidden = True Range("18:18, 22:22, 29:29").EntireRow.Hidden = False End Select End Sub

Thank you guys for the replies!

@SamT

it seems, this time you posted while i was typing :D

:D