More than one ActiveX button to same piece of code

[ABabeNChrist]

I have a worksheet with multiple ActiveX Command buttons that pretty much do the same thing but only to different rows. Here’s a sample of the code I’m using.

Code: Select all

    With ActiveSheet.Rows("18:18")
        If .RowHeight > 160 Then
            .RowHeight = 24
        Else
            .RowHeight = .RowHeight + 155
        End If
    End With

    If ActiveSheet.Rows("18:18").RowHeight > 25 Then
        MsgBox ("Row height increased ")
    End If

Is there an easier way to accomplish this without having to repeat code for each command button? I’d like to keep the buttons. I thought of using a Case statement, but not sure that will work. I know I could use Worksheet change event or Double click event but like the idea of buttons, any thoughts

[HansV]

You could create a procedure with an argument that could be called from the command buttons:

Code: Select all

Sub HandleRow(r As Long)
    With ActiveSheet.Rows(r)
        If .RowHeight > 160 Then
            .RowHeight = 24
        Else
            .RowHeight = .RowHeight + 155
        End If
    End With

    If ActiveSheet.Rows(r).RowHeight > 25 Then
        MsgBox ("Row height increased ")
    End If
End Sub

Call like this:

Code: Select all

Private Sub CommandButton1_Click()
  Call HandleRow(18)
End Sub

Private Sub CommandButton2_Click()
  Call HandleRow(36)
End Sub

[ABabeNChrist]

Thank you Hans
Great Idea, I think that will work just fine, it will defiantly reduces the repeating of the code and simplify everything.

[Jan Karel Pieterse]

For things like this, forms buttons are easier, as you can tie them all to one routine. You can then use
ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address to find out where a button is located to decide whatto do.

[ABabeNChrist]

For things like this, forms buttons are easier, as you can tie them all to one routine. You can then use
ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address to find out where a button is located to decide whatto do.

Thank you Jan
I originally was using checkbox’s (form controls) in the same way you mention in this thread and it did work great but the only small problem I experienced was when other users who had little to no computer skills they would sometimes accidently delete or change checkbox caption or even move the checkbox by right clicking on checkbox. I figured that by using ActiveX Command buttons and with Hans suggestion this would help eliminate these possible accidentals. And at this point seems to work fantasic.

[Jan Karel Pieterse]

Good point. HOw many of those checkboxes do you have? It may pay to use a class module approach where you can have one click event for all of them.

[ABabeNChrist]

Hi Jan
I did have somewhere around 300 checkboxes, I changed them all out to ActiveX Command buttons. I then adapted them to the suggestion that Hans offered.

[rory]

If you have 300 checkboxes all doing the same thing, then a class module would be a good way to go.

[Jan Karel Pieterse]

see:
http://www.jkp-ads.com/articles/controlhandler00.asp