Help with worksheet_change

[Take2]

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:

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

[Take2]

Found a solution myself. Added this around the expression:

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

EndIf

[SamT]

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.

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

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

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

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

[mancubus]

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

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

[Take2]

Thank you guys for the replies!

[mancubus]

@SamT

it seems, this time you posted while i was typing

[SamT]