PDA

View Full Version : Help with worksheet_change



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

Take2
01-20-2016, 01:00 PM
Found a solution myself. Added this around the expression:

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

End If

SamT
01-20-2016, 01:02 PM
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 :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
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
01-20-2016, 01:12 PM
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
01-20-2016, 01:18 PM
Thank you guys for the replies!

mancubus
01-20-2016, 02:19 PM
@SamT

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

SamT
01-20-2016, 06:07 PM
:D