Else problem with clicking on a row to run macro

[noname91]

Hi,

I'm experiencing a problem that I believe is stemming from the use of the Else function. Basically, my setup is such that if I click on row 2, a range of rows below it will be hidden if the rows are visible, and will become visible once row 2 is clicked again. This seems to be working, except that I can't click anywhere else after clicking on row 2 (i.e. I can click elsewhere, but the cursor instantly snaps back to the range of rows targeted for hiding). The code is below:




Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 2 And Rows("3:9").Hidden = False Then
Rows("3:9").Select
Selection.EntireRow.Hidden = True
Else
Rows("3:9").Select
Selection.EntireRow.Hidden = False
End If
End Sub


More than likely its the Else function that's causing, but I'm not sure what workaround would be the best. Any guidance here would be appreciated.
Thanks!

[georgiboy]

Try to take away the select/selection parts,

For example:

Rows("3:9").Select
Selection.EntireRow.Hidden = True

Needs to be:

Rows("3:9").EntireRow.Hidden = True

See if that fixes the issue

Hope this helps

[noname91]

Thank you georgiboy!

I tried your solution. While the auto-snapping is fixed, there are two other issues that have come up. The first is minor. I'd like to be able to click on any cell in row 2 to trigger the hide/show macro. This works except for when the cell that is currently selected is clicked on again. The second issue is problematic. Basically, once they're hidden, if I select any cell even outside row 2, the target row region still becomes visible. This only occurs when the target rows are already hidden, not when they are visible.


Any way to fix these issues?



Thanks again.

[Paul_Hossler]

Try this then

Note that this is the selection CHANGE event so clicking A2 and then clicking A2 again does NOT count as a selection change (issue 1)




Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row <> 2 Then Exit Sub ' issue 2

Rows("3:9").Hidden = Not Rows("3:9").Hidden
End Sub

[noname91]

Thank you Paul_Hossler and sorry for the late reply, my pc had some issues that needed to be taken care of. The solution you posted works perfectly. The first issue is really a minor one so its fine.

Thanks again!