Worksheet_SelectionChange(ByVal Target As Range)

[Rem0ram]

Hi Excelions

Have tried to use Worksheet_SelectionChange Event Funtion to hide & unhide 10 set of rows, the problem i'm having its working fine for the first two set of rows (Rows38-44 and 48to55) in Input_Sheet but its not working for remaining 8 set of rows.

Have used the nestedif to achieve but i'm sure i was wrong some where.....any help will be really helpful....

Thanks
Rem0

[Bob Phillips]

Does this work any better? if not, give an example that doesn't work.

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect "O1"

'Application.ScreenUpdating = False

If Not Intersect(Target, Me.Range("D9")) Is Nothing Then

If Target.Value = "" Then

Call RowVisibility(Me.Range("21:22,24:150"), True, True)

ElseIf Target.Value = "OFGEN User Access Removal (stop user access to OFGEN)" Then

Call RowVisibility(Me.Range("21:22,24:150"), False, True)

ElseIf Target.Value = "CLONE Existing OFGEN User" Then

Call RowVisibility(Me.Range("21:22,24:150,34:150"), True, False, True)
End If

ElseIf Not Intersect(Target, Me.Range("I38")) Is Nothing Then

'For Role#1
If Target.Value = 0 Then

Call RowVisibility(Me.Range("24:35,34:35,46:137,138:150"), True, False, True, False)

'For Role#2
ElseIf Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,34:55,56:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I48")) Is Nothing Then

'For Role#3
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,34:65,66:137,138:150"), True, False, True, False)

'For Role#3
ElseIf Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,33:65,66:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I59")) Is Nothing Then

'For Role#4
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:76,77:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I70")) Is Nothing Then

'For Role#5
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:86,87:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I80")) Is Nothing Then

'For Role#6
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:96,97:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I90")) Is Nothing Then

'For Role#7
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35106,107:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I100")) Is Nothing Then

'For Role#8
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:334,35:116,117:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I110")) Is Nothing Then

'For Role#9
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:126,127:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I120")) Is Nothing Then

'For Role#10
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:126,127:137,138:150"), True, False, False, False)
End If
End If

Me.Protect "O1"
End Sub


Private Function RowVisibility(ByRef rng As Range, ParamArray visibility())
Dim rngArea As Range
Dim idx As Long

For Each rngArea In rng.Areas

rngArea.Hidden = visibility(idx)
idx = idx + 1
Next rngArea
End Function
[/VBA]

[Rem0ram]

Hi xld

Many Thks for looking in to this.

Yes it works partially, but whenever i change the option in D9 the respective rows should hide & unhide but it is not happening, it runs only if i run some other macro in the sheet (may be the macro is not getting triggered when cell D9 is changed).

Can you look on this further?

Best
Rem0

[Rem0ram]

Hi

One more thing i noticed, when ever the flag set in Column I turns "1" it should unhide the next set of rows but this too not happening.
scenarios are below:
i38 = 1 then rows 38-55 should be visible
i48 = 1 then rows 38-66 should be visible
i59 = 1 then rows 38-77 should be visible
i70 = 1 then rows 38-87 should be visible
i80 = 1 then rows 38-97 should be visible
i90 = 1 then rows 38-107 should be visible
i100 = 1 then rows 38-117 should be visible
i110 = 1 then rows 38-127 should be visible
i120 = 1 then rows 38-137 should be visible

Best
Rem0

[Rem0ram]

Hi

Can anyone look in to this....though xls provided the revised code which partially works but its not completely working.

Best
Rem0

[Aflatoon]

The SelectionChange event is triggered by selecting a different cell, not changing the value of a cell. If that's what you want, use the Change event instead.

[Rem0ram]

Hi Aftaloon

Many Thanks...

But i'm getting error "unable to set the hidden property of the range class"

with both selectionchange and change event.

Can u look?

Best
Rem0

[Aflatoon]

If you provide an updated file with the current code and a specific example that fails, yes.

[Rem0ram]

HERE U GO...AFLATOON..

HI XLD.. CAN U TOO LOOK IN TO THE ERROR?

BEST
rEM0

[Aflatoon]

If you provide an updated file with the current code and a specific example that fails, yes.

Missed a bit.

[Rem0ram]

Hi Aflatoon

not getting u?

[Rem0ram]

Hi Aflatoon

Any Luck?

[Aflatoon]

I meant that you need to give us a specific example that fails and explain in what way it is incorrect.

[Rem0ram]

Hi

1. error - when i change the dropdown value in D9, i get the error "unable to set the hidden property of the range class"

2. error - when ever the flag set in Column I turns "1" it should unhide the next set of rows but this too not happening.
scenarios are below:
i38 = 1 then rows 38-55 should be visible
i48 = 1 then rows 38-66 should be visible
i59 = 1 then rows 38-77 should be visible
i70 = 1 then rows 38-87 should be visible
i80 = 1 then rows 38-97 should be visible
i90 = 1 then rows 38-107 should be visible
i100 = 1 then rows 38-117 should be visible
i110 = 1 then rows 38-127 should be visible
i120 = 1 then rows 38-137 should be visible

Best
Rem0

[Bob Phillips]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)

Me.Unprotect "O1"

'Application.ScreenUpdating = False

If Not Intersect(Target, Me.Range("D9")) Is Nothing Then

If Target.Value = "" Then

Call RowVisibility(Me.Range("21:22,24:150"), True, True)

ElseIf Target.Value = "OFGEN User Access Removal (stop user access to OFGEN)" Then

Call RowVisibility(Me.Range("21:22,24:150"), False, True)

ElseIf Target.Value = "CLONE Existing OFGEN User" Then

Call RowVisibility(Me.Range("21:22,24:150,34:150"), True, False, True)

End If

ElseIf Not Intersect(Target, Me.Range("I38")) Is Nothing Then

'For Role#1
If Target.Value = 0 Then

Call RowVisibility(Me.Range("24:35,34:35,46:137,138:150"), True, False, True, False)

'For Role#2
ElseIf Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,34:55,56:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I48")) Is Nothing Then

'For Role#3
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,34:65,66:137,138:150"), True, False, True, False)

'For Role#3
ElseIf Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,33:65,66:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I59")) Is Nothing Then

'For Role#4
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:76,77:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I70")) Is Nothing Then

'For Role#5
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:86,87:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I80")) Is Nothing Then

'For Role#6
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:96,97:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I90")) Is Nothing Then

'For Role#7
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35106,107:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I100")) Is Nothing Then

'For Role#8
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:116,117:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I110")) Is Nothing Then

'For Role#9
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:126,127:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I120")) Is Nothing Then

'For Role#10
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:126,127:137,138:150"), True, False, False, False)
End If

End If

Me.Protect "O1"
End Sub


Private Function RowVisibility(ByRef rng As Range, ParamArray visibility())
Dim rngArea As Range
Dim idx As Long

For Each rngArea In rng.Areas
rngArea.EntireRow.Hidden = visibility(idx)
idx = idx + 1
Next rngArea

End Function[/VBA]

[Rem0ram]

Hi xld

Many Thanks!

But still its not working...if possible can you update the code and chk whether its working in the sample file i have attached to this thread?

Best
Rem0

[Rem0ram]

HI

CAN ANYONE LOOK IN TO THIS?

[Aflatoon]

For #1
[vba]Private Function RowVisibility(ByRef rng As Range, ParamArray visibility())
Dim rngArea As Range
Dim idx As Long

For Each rngArea In rng.Areas
rngArea.EntireRow.Hidden = visibility(idx)
idx = idx + 1
Next rngArea

End Function
[/vba]

[Rem0ram]

Hi Aflatoon

Many Thanks!

But its the same already exist in the Input_Sheet Event Change.

Can u brief me what exactly the change is or if you can amend the file and upload the file that will be help ful.

Best
Rem0

[Aflatoon]

No it isn't - I would hardly have posted it if it were. Read it carefully... (or even just paste it in in place of what you have now)