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
--
Regards,
Tom Ogilvy
"PCLIVE" <pclive(RemoveThis)@cox.net> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:06F34209-9B5F-467C-BB62-(E-Mail Removed)...
>> 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
>>>
>>>
>>>
>>>
>
>
Similar Threads
