Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CommandButton and a variable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CommandButton and a variable
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CommandButton and a variable
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CommandButton and a variable
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commandbutton | Excel Programming | |||
CommandButton | Excel Programming | |||
CommandButton | Excel Programming | |||
CommandButton | Excel Programming | |||
Commandbutton | Excel Programming |