Printable View
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
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]
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
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
Hi
Can anyone look in to this....though xls provided the revised code which partially works but its not completely working.
Best
Rem0
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.
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
If you provide an updated file with the current code and a specific example that fails, yes. ;)
HERE U GO...AFLATOON..
HI XLD.. CAN U TOO LOOK IN TO THE ERROR?
BEST
rEM0
Missed a bit. ;)Quote:
Originally Posted by Aflatoon
Hi Aflatoon
not getting u?
Hi Aflatoon
Any Luck?
I meant that you need to give us a specific example that fails and explain in what way it is incorrect.
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
[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]
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
HI
CAN ANYONE LOOK IN TO THIS?
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]
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
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)
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
The Change event is not triggered by formulas recalculating. You would have to use the Worksheet_Calculate event for that.
Hi Aflatoon
Many Thanks!
This is something new...sure will give a shot and get back to you...
Best
Rem0