CommandButton and a variable

[PCLIVE]

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

 

[Tom Ogilvy]

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

 

[PCLIVE]

Thanks Tom,

That works pretty good. I have another dilema. Is there a way to carry a
variable over from a Private Sub to a Sub? For example, the CommandButton
sets the variable and then runs a Macro. I'm sure I can easily do this by
assigning the variable to a cell and then recall it from the macro, but I
didn't know if there was another way.

Thanks again.
Paul

 

[Tom Ogilvy]

Private Sub Commandbutton1_Click()
Dim wn as Long
wn = 1
myroutine wn
End Sub



Sub MyRoutine(wn as Long)
Dim cb as MSForms.CommandButton
set cb = Activesheet.OleObjects("CommandButton" & wn).Object
' other code
End Sub