Excel: Use button to hide/unhide rows based on cell value. Change/toggle button name.

Question

After almost four hours of trying to cobble together my own solution (I obviously have failed), I'm turning to the community.

Scenario: a worksheet used to track cost-saving actions includes plan and actuals. The value in Column G is either "Plan" or "Actual". The first "Plan" row is Row 11 (and then every other row is a plan row). I'd like to enhance the worksheet where:

• the default view does not show the plan rows
• a button above Column G is named "Show Plan"
• when the user clicks the "Show Plan" button, the plan rows (11, 13, 15,...) appear AND the button name changes to "Hide Plan."
• -when the user clicks the "Hide Plan" button, the plan rows (11, 13, 15,...) are hidden AND the button name changes to "Show Plan."

Thanks for your guidance

Answer 1

Hi,

Go to the 'developer' tab and click on 'Insert' and then click the command button icon in the ActiveX toolbox. On the worksheet hold down the left mouse button and drag to draw a button on the sheet in the appropriate place. Right click the button and view code and paste the code below into the editor. Back on the worksheet click the button and the rows will either hide or unhide depending upon the current state and the button caption will change to the correct one.

Private Sub CommandButton1_Click()

Dim x As Long, LastRow As Long

LastRow = Cells.Find(What:="*", After:=[A1], _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

For x = 11 To LastRow Step 2

Rows(x).Hidden = Not Rows(x).Hidden

Next

If Rows(11).Hidden Then

    CommandButton1.Caption = "Show Plan"

Else

    CommandButton1.Caption = "Hide Plan"

End If

End Sub

Answer 2

Thanks, Mike, for responding so quickly! If I had another four hours, I still wouldn't have arrived at the solution on my own.

The button works as expected (i.e., it hides/unhides the "Plan" rows); however, the button name does not appear on the button (neither "Show Plan" nor "Hide Plan"), leading me to wonder what I did wrong.

Answer 3

Hi,

What I suspect has happened is that the command button you've put on the worksheet isn't called CommandButton1, it's something else. The name of your command button can be found in the code shown in bold below. If it isn't commandbutton1 then change the name in the code; also in bold italics to the correct name.

Private Sub CommandButton1_Click()

Dim x As Long, LastRow As Long

LastRow = Cells.Find(What:="*", After:=[A1], _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

For x = 11 To LastRow Step 2

Rows(x).Hidden = Not Rows(x).Hidden

Next

If Rows(11).Hidden Then

    CommandButton1.Caption = "Show Plan"

Else

    CommandButton1.Caption = "Hide Plan"

End If

End Sub

Answer 4

Hi, Mike.

I'm sorry to make a private tutorial public, but I hope this helps others. And I appreciate your time.

Is there somewhere else I need to assign the button name. (By the way, I changed "11" to "13" in the script because I moved the first Plan row to Row 13. If the original "x=11" wasn't meant to indicate  the first Plan row, let me know.)

Regards.

Answer 5

Hi,

I cannot see any reason why that wouldn't work. I've uploaded a working version to my Onedrive, it's called Hide_Show_Plan.xlsm Have a look and see if it works for you.

https://onedrive.live.com/?id=66A66EA84229B01B%21108&cid=66A66EA84229B01B