With ActiveSheet.OleObjects("CommandButton" & (wn + 1)).Object

--
Regards,
Tom Ogilvy




"PCLIVE" wrote:

Is it possible to reference a CommandButton by using a variable. For
example, I have CommandButton's 1 through 8. So the first one is
"CommandButton1", the second, "CommandButton2", etc.

These eight buttons perform the same actions with the exception of some
number references which correspond to the specific button that was pressed.
So instead of duplicating the code on each button, I'd like to know if there
is a way to have each button run the same module code, but a variable will
be used to set the numbers as appropriate.

Here is the code that is being used.

Private Sub CommandButton7_Click()
With Me.CommandButton7
.Caption = "Done 6"
.BackColor = &H800000
.ForeColor = &HFFFFFF
.Font.Bold = True
End With
Application.Calculation = xlCalculationManual
Range("AA1").Formula = "6"
Range("AG6").Formula = "1"
Application.Run "CreateWeeks"
End Sub


I've altered it to be used in a module as follows.

Sub WeekButtons()

With ActiveSheet.CommandButton7
.Caption = "Done " & wn 'this variable is set from the
CommandButton code
.BackColor = &H800000
.ForeColor = &HFFFFFF
.Font.Bold = True
End With

Application.Calculation = xlCalculationManual
Range("AA1").Formula = wn
Range("AG6").Formula = "1"
Application.Run "CreateWeeks"
End Sub

Is there a way to reference CommandButton7 as variable? For example, where
wn = 6:
With ActiveSheet.CommandButton & (wn + 1)

I know this doesn't work, but is there some way to do this?

Thanks,
Paul