Toggle Button Caption
 

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!

Toggle Button Caption
 

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

Sandy
Al wrote:
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!

Toggle Button Caption
 

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




"Al" wrote in message
...
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!

Toggle Button Caption
 

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



"Al" wrote:

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!

Toggle Button Caption
 

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)

"Al" wrote in message
...
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!

Toggle Button Caption
 

Thanks to you ALL!
I have it woorking now


"Al" wrote:

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!