Hiding and Unhiding buttons question

[Alex Piotto]

Hi!
I have got two command buttons overlapping.
In the script below, CommandButton1 will hide the zero's rows, and CommandButton2 will show them again.
And they will hide and show each other too.
The hiding zeros part is OK.
Quite simple I can say... BUT....

'HIDING ZERO ROWS
ActiveSheet.Shapes("CommandButton2").Visible = True
ActiveSheet.Shapes("CommandButton1").Visible = False

    Dim cell As Range
    For Each cell In Range("K2:K200")
    If Not IsEmpty(cell) Then
    If cell.Value <> "" And cell.Value = 0 Then
    cell.EntireRow.Hidden = True
    End If
    End If
    Next

'SHOW ALL ROWS
ActiveSheet.Shapes("CommandButton1").Visible = True
ActiveSheet.Shapes("CommandButton2").Visible = False

    Dim cell As Range
    For Each cell In Range("K2:K200")
    cell.EntireRow.Hidden = False
    Next

... BUT... the buttons will hide only if I click somewhere on the sheet.
Why is that happening and what can I do to avoid it?

 

[Alex Piotto]

Not sure what you mean?
Did you try what I suggested?

Hi Fluff, if I remember well the use of me.CommandButton1 is used inside a form. I have no forms in my book... and no, not tried yet. Later. Having Family dinner...

 

[Alex Piotto]

Hi!.

Is the property name of your command button "CommandButton1" and "CommandButton2"?

Also is that region of your worksheet unlocked or your worksheet unprotected?

The buttons have the correct names. And the code unprotects the sheet before hiding the zeros and protects when finished.

 

[Fluff]

if I remember well the use of me.CommandButton1 is used inside a form.

Not entirely, you can use it inside a sheet module for controls on that sheet.

 

[Alex Piotto]

How do I attach a sample file?

 

[Fluff]

You would have to upload to a share site such as OneDrive, Google Drive, mark for sharing & then post the link to the thread.

If you are happy swapping two command buttons for a shape, you could assign this code to the shape & it will act as a toggle

Sub AlexPiotto()
   Dim Shp As Shape
   Dim Cl As Range
  
   With Sheets("Contaggio")
      .Unprotect
      Set Shp = .Shapes(Application.Caller)
      If Shp.TextFrame.Characters.Caption = "Hide" Then
         For Each Cl In .Range("K2:K200")
            If Cl.Value <> "" And Cl.Value = 0 Then Cl.EntireRow.Hidden = True
         Next Cl
         Shp.TextFrame.Characters.Caption = "Unhide"
      Else
         .Range("K2:K200").EntireRow.Hidden = False
         Shp.TextFrame.Characters.Caption = "Hide"
      End If
      .Protect , True, True, True
      .EnableSelection = xlUnlockedCells
   End With
End Sub

 

[Alex Piotto]

FANTASTIC! It works like a charme, Thank you Fluff! You know... without you guys I am nothing!

 

[Fluff]

You're welcome & thanks for the feedback.