Hide / Unhide rows based on cell selection.

[humsboyle]

Evening all,
I am currently looking for a way to unhide rows when particular cells are clicked on (Nothing typed, just clicked on)
I.e
When Cell E5 is clicked on, Rows 33 & 34 are unhidden to display a message.
When Cell E6 is clicked on Rows 33 & 34 would be hidden again, but rows 36 & 37 would become unhidden. Etc Etc. upto Cell E9 unhidden rows.
If none of the rows are clicked on then rows 33 to 40 would stay hidden.

i have seen code

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("A1")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
If LenB(Target.Value) <> 0 Then
Range("A5,A11:A16").EntireRow.Hidden = False
Else: Range("A5,A11:A16").EntireRow.Hidden = True
End If
End Sub

from pages
http://www.mrexcel.com/forum/excel-questions/467136-hide-unhide-certain-rows-if-cell-populated.html

which seems to work in un-hiding the rows however i have to type into the cells in order for it to work.

i need the rows to unhide when i click on the cell.

Any help or pointers on how to create would be greatly appreciated.

Thanks

Steve

 

[JLGWhiz]

Evening all,
I am currently looking for a way to unhide rows when particular cells are clicked on (Nothing typed, just clicked on)
I.e
When Cell E5 is clicked on, Rows 33 & 34 are unhidden to display a message.
When Cell E6 is clicked on Rows 33 & 34 would be hidden again, but rows 36 & 37 would become unhidden. Etc Etc. upto Cell E9 unhidden rows.
If none of the rows are clicked on then rows 33 to 40 would stay hidden.

i have seen code


from pages
http://www.mrexcel.com/forum/excel-questions/467136-hide-unhide-certain-rows-if-cell-populated.html

which seems to work in un-hiding the rows however i have to type into the cells in order for it to work.

i need the rows to unhide when i click on the cell.

Any help or pointers on how to create would be greatly appreciated.

Thanks

Steve

Try using the Worksheet_SelectionChange event. Then "If Not Intersect(Target, Range("E6:E9")) Is Nothing" would set the trigger to execute code to hide/unhide, etc.

 

[humsboyle]

Thanks for your quick replay JLGWhiz
Where would i insert the code you suggested?

 

[ChrisR]

Does it have to be a click? Could you be a double click or right click? As there are worksheet change events for those............

 

[humsboyle]

Afternoon,

Either a left click onto cell or when cell is tabbed onto, would be most benefical but if double click is easiest / best way then i would instruct team to do that.

 

[ChrisR]

OK, well I don't think there is a left click onto a specific cell............You'd have to use a double click........

Replace your private sub line with this one

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

If you say the rest of your code works OK then this should do the trick........

 

[JLGWhiz]

Hi humsboyle, Sorry, I did not realize that you were not an avid code user.
The code below should be copied to the code module for the sheet where you want to hide and unhide the rows. To access the code module, right click the sheet name tab, then clidk 'View Code' in the pop-up menu. That should then display the VB Editor screen for that worksheet code module. copy the code into that module, then click any cell on the worksheet other than E5:E9. Rows 33:40 should now be hidden. Click any cell in range E5:E8 and two rows will be visible from row 33 to row 40.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Code goes in the Worksheet specific module
If Not Intersect(Target, Range("E5:E9")) Is Nothing Then
    If Target.Address = "$E$5" Then
        Rows("33:40").Hidden = True
        Rows("33:34").Hidden = False
    ElseIf Target.Address = "$E$6" Then
        Rows("33:40").Hidden = True
        Rows("35:36").Hidden = False
    ElseIf Target.Address = "$E$7" Then
        Rows("33:40").Hidden = True
        Rows("37:38").Hidden = False
    ElseIf Target.Address = "$E$8" Then
        Rows("33:40").Hidden = True
        Rows("39:40").Hidden = False
    Else
        Rows("33:40").Hidden = True
    End If
End If
End Sub

 

[humsboyle]

OK, well I don't think there is a left click onto a specific cell............You'd have to use a double click........

Replace your private sub line with this one

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

If you say the rest of your code works OK then this should do the trick........

Hi Chris.
Adding the above does not work.

Any ideas?

 

[humsboyle]

Hi JLGWhiz,

That works like a charm
one question tho
If Not Intersect(Target, Range("E5:E9")) Is Nothing Then mean?
I know Range ("E5:E9) means that range, but not sure about rest.

Thanks

Steve

 

[JLGWhiz]

one question tho
If Not Intersect(Target, Range("E5:E9")) Is Nothing Then mean?

That is a VBA term for "If this is the target". Then the range identifies what specific cells apply. BTW, I included cell E9 in that range because you specified it, but I don't really see where it is needed.