I use something similar to this in a lot of userforms and was hoping that
there might be a better way.

Scenario.......
I want to pass the number of the CommandButton clicked to another sub.

How I do it now......
Private Sub CBClick()
' Do stuff here based on the BoxClicked Number
End Sub
Private Sub CommandButton1_Click()
BoxClicked = 1
CBClick
End Sub
Private Sub CommandButton2_Click()
BoxClicked = 2
CBClick
End Sub

' continued for another 97 Command Buttons

Private Sub CommandButton99_Click()
BoxClicked = 99
CBClick
End Sub

Is there a better way?????

Thanks,
John

As far as I know, you have to create a variable that can be stored on a
worksheet and then capture it in another sub from the worksheet. Or, make
public variable declarations up front and use those. There is no simple way
that I know of.


"John Wilson" wrote:

> I use something similar to this in a lot of userforms and was hoping that
> there might be a better way.
>
> Scenario.......
> I want to pass the number of the CommandButton clicked to another sub.
>
> How I do it now......
> Private Sub CBClick()
> ' Do stuff here based on the BoxClicked Number
> End Sub
> Private Sub CommandButton1_Click()
> BoxClicked = 1
> CBClick
> End Sub
> Private Sub CommandButton2_Click()
> BoxClicked = 2
> CBClick
> End Sub
>
> ' continued for another 97 Command Buttons
>
> Private Sub CommandButton99_Click()
> BoxClicked = 99
> CBClick
> End Sub
>
> Is there a better way?????
>
> Thanks,
> John
>
>
>

http://j-walk.com/ss/excel/tips/tip44.htm

RBS


"John Wilson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I use something similar to this in a lot of userforms and was hoping that
> there might be a better way.
>
> Scenario.......
> I want to pass the number of the CommandButton clicked to another sub.
>
> How I do it now......
> Private Sub CBClick()
> ' Do stuff here based on the BoxClicked Number
> End Sub
> Private Sub CommandButton1_Click()
> BoxClicked = 1
> CBClick
> End Sub
> Private Sub CommandButton2_Click()
> BoxClicked = 2
> CBClick
> End Sub
>
> ' continued for another 97 Command Buttons
>
> Private Sub CommandButton99_Click()
> BoxClicked = 99
> CBClick
> End Sub
>
> Is there a better way?????
>
> Thanks,
> John
>