Take2
01-20-2016, 12:13 PM
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
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