using a Cmd button's location as a relative reference

[gilbertvb3]

I want to use a command button that unhides the row below it. However there are going to be 50 buttons. Is there a way to use the button that was pressed as the reference to make the cell beneath it active and change the row height. I am trying to avoid using 50 different macros.

 

[Smitty]

Welcome to the Board!

Take a look at the TopLeftCell Property in the VBA helpfile. You can identify the cell below each button based off of that by using Offset.

Hope that helps,

 

[gilbertvb3]

This is what I got so far. It works on commandbutton1 only. Is there any way I can reuse this code for the rest of the buttons?


Private Sub CommandButton1_Click()

With CommandButton1.TopLeftCell
ActiveSheet.Unprotect
.Offset(1, 0).Resize(1).EntireRow.Hidden = Not .Offset(1, 0).Resize(1).EntireRow.Hidden
ActiveSheet.Protect End With
End Sub

 

[John_w]

You could do this:

Option Explicit

Private Sub CommandButton1_Click()
    ToggleHiddenRow CommandButton1
End Sub

Private Sub CommandButton2_Click()
    ToggleHiddenRow CommandButton2
End Sub

Private Sub ToggleHiddenRow(cbo As MSForms.CommandButton)
    ActiveSheet.Unprotect
    With cbo.TopLeftCell
        .Offset(1, 0).Resize(1).EntireRow.Hidden = Not .Offset(1, 0).Resize(1).EntireRow.Hidden
    End With
    ActiveSheet.Protect
End Sub

and call ToggleHiddenRow in each of the 50 command button Click subroutines.

Alternatively, you could create a class to handle multiple command buttons and then you only need 1 command button Click handler. I've written some code based on http://www.ozgrid.com/forum/showthread.php?t=80631 for command buttons on a worksheet - let me know if you want to see it.

 

[gilbertvb3]

Worked like a charm. Thanks a bunch. I am not very proficient in any programming language. Your a life saver.