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.

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

[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]

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

[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
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

[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)

[Rem0ram]

Hi Aflatoon

Apologies!

Its working now.

Then next problem,

When D9 value = new ofgen user request (or) ofgen user update request Then its showing the Role#1 rows visible then if all values are filled for Role#1 the flag in cell i38 turns 1 after that it should trigger Role#2 rows visible and so on.... but, this is not happening with current code.

Also, once this works there also possibility of users using Radio buttons in row1 which will update the validations of respective cells in Role#1 to Role#10.

These are the two solutions i'm looking for...as this intersect Range option used in the worksheet event is new to me i really have no idea of its funtionality.

If you help further i will be really thankful to you...

Best
Rem0

[Aflatoon]

The Change event is not triggered by formulas recalculating. You would have to use the Worksheet_Calculate event for that.

[Rem0ram]

Hi Aflatoon

Many Thanks!

This is something new...sure will give a shot and get back to you...

Best
Rem0