Toggle Button Caption

[Guest]

Hi Guys

I have the following code which hides/unhides rows, that I have assigned to
a button...

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Dim I As Long
Dim Hidden As Boolean

For I = 8 To 426
If Rows(I).EntireRow.Hidden Then
Hidden = True
Rows(I).EntireRow.Hidden = False
End If

Next I

If Hidden Then Exit Sub

For I = 8 To 426
If Cells(I, 2).Value <> "TOTAL" Then
Range(Cells(I, 1), Cells(I, 1)).EntireRow.Hidden = True
End If
Next I
End Sub

.... how do I toggle the button caption so that when that only the TOTAL rows
are showing the caption is SHOW and else the caption is HIDE?

Thanks!

 

[Sandy]

Here's your code modified

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim I As Long
Dim Hidden As Boolean
For I = 8 To 426
If Rows(I).EntireRow.Hidden Then
Hidden = True
Rows(I).EntireRow.Hidden = False
CommandButton1.Caption = "Hide"
End If
Next I
If Hidden Then Exit Sub
For I = 8 To 426
If Cells(I, 2).Value <> "TOTAL" Then
Range(Cells(I, 1), Cells(I, 1)).EntireRow.Hidden = True
CommandButton1.Caption = "Show"
End If
Next I
End Sub

 

[PCLIVE]

One way:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Dim I As Long
Dim Hidden As Boolean

For I = 8 To 426
If Rows(I).EntireRow.Hidden Then
Hidden = True
Rows(I).EntireRow.Hidden = False
Me.CommandButton1.Caption = "Hide"
End If

Next I

If Hidden Then Exit Sub

For I = 8 To 426
If Cells(I, 2).Value <> "TOTAL" Then
Range(Cells(I, 1), Cells(I, 1)).EntireRow.Hidden = True
Me.CommandButton1.Caption = "Show"
End If
Next I
End Sub

 

[Guest]

Hi,
I would suggest the following:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Dim I As Long
Static Hidden As Boolean

Range("A8:A426").Rows.Hidden=True

Hidden = not Hidden
CommandButton1.Caption = iif(Hidden,"Show","Hide")

' It is not clear to me what you are doing here..
'But you can use the Hidden Static variable if you like
For I = 8 To 426
If Cells(I, 2).Value <> "TOTAL" Then
Range(Cells(I, 1), Cells(I, 1)).EntireRow.Hidden = True
End If
Next I
End Sub

 

[Bob Phillips]

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Dim iRow As Long
Dim Hidden As Boolean

If Rows(8).Hidden Then
Rows("8:426").Hidden = False
Me.CommandButton1.Caption = "Hide"
On Error Resume Next
iRow = Application.Match("TOTAL", Range("B8:B426"), 0)
On Error GoTo 0
If iRow > 0 Then Rows(iRow + 7).Hidden = True
Else
Rows("8:426").Hidden = True
Me.CommandButton1.Caption = "Show"
End If

Application.ScreenUpdating = True

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks to you ALL!
I have it woorking now