Command Button
 

Dear experts,

I want to create a command button and when I click it, rows under row 30
will be collapsed. When I click the button again, it toggles to expand the
rows under row 30. In the meantime, can the wording on the button be changed
to "expand rows" in collapse status and "collapse rows" in expand status? Is
it required a VBA code to operate? If yes, please kindly advise the code.

Thanks in advance

Command Button
 

Something lik:


Sub HideRows()
With Sheet1
Rows("30:65536").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Shapes("CommandButton1").Select
Sheet1.CommandButton1.Caption = "Expand Rows"
End With
End Sub

Sub ShowRows()
With Sheet1
Rows("30:65536").Select
Selection.EntireRow.unHide = True
ActiveSheet.Shapes("CommandButton1").Select
Sheet1.CommandButton1.Caption = "Colapse Rows"
End With
End Sub



Corey....
"Freshman" wrote in message
...
Dear experts,

I want to create a command button and when I click it, rows under row 30
will be collapsed. When I click the button again, it toggles to expand the
rows under row 30. In the meantime, can the wording on the button be
changed
to "expand rows" in collapse status and "collapse rows" in expand status?
Is
it required a VBA code to operate? If yes, please kindly advise the code.

Thanks in advance

Command Button
 

Hi Corey,

Thanks for your tip and it works great. Thanks once again.

"Corey" wrote:

Something lik:


Sub HideRows()
With Sheet1
Rows("30:65536").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Shapes("CommandButton1").Select
Sheet1.CommandButton1.Caption = "Expand Rows"
End With
End Sub

Sub ShowRows()
With Sheet1
Rows("30:65536").Select
Selection.EntireRow.unHide = True
ActiveSheet.Shapes("CommandButton1").Select
Sheet1.CommandButton1.Caption = "Colapse Rows"
End With
End Sub



Corey....
"Freshman" wrote in message
...
Dear experts,

I want to create a command button and when I click it, rows under row 30
will be collapsed. When I click the button again, it toggles to expand the
rows under row 30. In the meantime, can the wording on the button be
changed
to "expand rows" in collapse status and "collapse rows" in expand status?
Is
it required a VBA code to operate? If yes, please kindly advise the code.

Thanks in advance

Command Button
 

Hi Roger,

Your tip works great and thanks once again. Have a nice day. Cheers.

"Roger Govier" wrote:

Hi

Sub ToggleHideRows()
With Sheet1
Dim bText As String
If Rows(30).Hidden = False Then
Rows("30:65536").Hidden = True
bText = "Expand Rows"
Else
Rows("30:65536").Hidden = False
bText = "Collapse Rows"
End If
.CommandButton1.Caption = bText
End With
End Sub


--
Regards
Roger Govier

"Freshman" wrote in message
...
Dear experts,

I want to create a command button and when I click it, rows under row 30
will be collapsed. When I click the button again, it toggles to expand the
rows under row 30. In the meantime, can the wording on the button be
changed
to "expand rows" in collapse status and "collapse rows" in expand status?
Is
it required a VBA code to operate? If yes, please kindly advise the code.

Thanks in advance