Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding .xla button for Toggle Calculation Button | Excel Programming | |||
Toggle change control button face id & caption | Excel Programming | |||
Place the caption of a toggle button into a cell | Excel Worksheet Functions | |||
Caption of a button | Excel Programming | |||
Toggle custom button caption | Excel Programming |