FBeckenbauer4
FBeckenbauer4

Reputation: 27

How to Stop Double Click

I've got the below code that is used to hide/unhide rows. In my spreadsheet I have a whole lot of images which are small down arrows. I've assigned the below code to those down arrows, effectively to use them to minimise and maximise rows as the spreadsheet has hundreds and hundreds of rows of data.

If I press on one down arrow it hides rows under it, then press again it unhides it, however if i press one down arrow and then another arrow the second click doesn't do anything I then have to press on it one more time to get it to work, or I have to press the original down arrow again. It's obviously due to cell A1 containing x. Is there any way to fix this?

Sub Advertising_20_21()
     If Range("A1") = "x" Then
    ActiveSheet.Range("A1").Value = ""
    ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden = True
  Else
    ActiveSheet.Range("A1") = "x"
    ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden = False
  End If
End Sub

Upvotes: 0

Views: 79

Answers (1)

Super Symmetry
Super Symmetry

Reputation: 2875

Test if the row is hidden directly rather than the value of A1 (which I assume is shared by all your arrow procedures and is therefore, the source of your undesired behaviour)

Sub Advertising_20_21()
  If Not ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden Then
    ActiveSheet.Range("A1").Value = ""
    ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden = True
  Else
    ActiveSheet.Range("A1") = "x"
    ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden = False
  End If
End Sub

The following is a slightly better approach

Sub ToggleHiddenRow(rng As Range)
  With rng.EntireRow
    .Hidden = Not .Hidden
  End With
End Sub

Sub ToggleAdvertising_20_21()
  ToggleHiddenRow ActiveSheet.Range("Advertising_20_21")
End With

Upvotes: 2

Related Questions