Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically click CommandButton
The following expression works fine and clicks CommandButton #1:-
Call UserForm1.CommandButton1_Click Is it possible to assign a variable to the number of the button clicked, along the lines:- For X=1 to 5 step 1 Call UserForm1.CommandButton(X)_Click (Then does what clicking the CB does) Next X This doesn't work, but I don't know if the fault is incorrect syntax, or that the expression simply cannot take a variable. donwb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically click CommandButton
hi
incorrect syntax. what are you trying to pass to the procedure? variables can be passed but the call command is looking for a named procedure and what you have written is not a named prodedure. UserForm1.CommandButton1_Click is your named prodedure. UserForm1.CommandButton(X)_Click is not. the correct syntax would be..... call UserForm1.CommandButton1_Click () you can pass variables, arrays or expressions but they have to be within the parenthases. but i'm not too sure about a button number. what are you trying to do...call 5 different button click? regards FSt1 "donwb" wrote: The following expression works fine and clicks CommandButton #1:- Call UserForm1.CommandButton1_Click Is it possible to assign a variable to the number of the button clicked, along the lines:- For X=1 to 5 step 1 Call UserForm1.CommandButton(X)_Click (Then does what clicking the CB does) Next X This doesn't work, but I don't know if the fault is incorrect syntax, or that the expression simply cannot take a variable. donwb |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically click CommandButton
On Mar 15, 12:52 pm, FSt1 wrote:
hi incorrect syntax. what are you trying to pass to the procedure? variables can be passed but the call command is looking for a named procedure and what you have written is not a named prodedure. UserForm1.CommandButton1_Click is your named prodedure. UserForm1.CommandButton(X)_Click is not. the correct syntax would be..... call UserForm1.CommandButton1_Click () you can pass variables, arrays or expressions but they have to be within the parenthases. but i'm not too sure about a button number. what are you trying to do...call 5 different button click? regards FSt1 "donwb" wrote: The following expression works fine and clicks CommandButton #1:- Call UserForm1.CommandButton1_Click Is it possible to assign a variable to the number of the button clicked, along the lines:- For X=1 to 5 step 1 Call UserForm1.CommandButton(X)_Click (Then does what clicking the CB does) Next X This doesn't work, but I don't know if the fault is incorrect syntax, or that the expression simply cannot take a variable. donwb Hello Donwb, You can Click each Command Button by setting its value property to true, like this... Dim CmdBtn As Object For X=1 to 5 step 1 Set CmdBtn = Controls("CommandButton" & X) CmdBtn.Value = True Next X Sincerely, Leith Ross |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically click CommandButton
Hi FSt1
I have a UserForm with many CBs. I need a macro (lets call it Macro1) to programmatically click CB1 on the UF, which will then do something. UserForm1.CommandButton1_Click in macro1 does this ok. When that's done, control goes back to macro1, which I then want to click CB2. UserForm1.CommandButton2_Click would do this. But there are 20 or so CBs to click, so I wanted to automate the process with something like UserForm1.CommandButton(X)_Click, with values 1 to 20 assigned to the variable X sequentially. The real question is:- can UserForm1.CommandButton(X)_Click become a named procedure when X is given a number - with the correct syntax? or is it treated as a named procedure in VBA ONLY if is assigned an integer and not a variable? donwb "FSt1" wrote in message ... hi incorrect syntax. what are you trying to pass to the procedure? variables can be passed but the call command is looking for a named procedure and what you have written is not a named prodedure. UserForm1.CommandButton1_Click is your named prodedure. UserForm1.CommandButton(X)_Click is not. the correct syntax would be..... call UserForm1.CommandButton1_Click () you can pass variables, arrays or expressions but they have to be within the parenthases. but i'm not too sure about a button number. what are you trying to do...call 5 different button click? regards FSt1 "donwb" wrote: The following expression works fine and clicks CommandButton #1:- Call UserForm1.CommandButton1_Click Is it possible to assign a variable to the number of the button clicked, along the lines:- For X=1 to 5 step 1 Call UserForm1.CommandButton(X)_Click (Then does what clicking the CB does) Next X This doesn't work, but I don't know if the fault is incorrect syntax, or that the expression simply cannot take a variable. donwb |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically click CommandButton
hi
that is what i thought you were trying to do. follow Leith's suggestion instead. Regards FSt1 "donwb" wrote: Hi FSt1 I have a UserForm with many CBs. I need a macro (lets call it Macro1) to programmatically click CB1 on the UF, which will then do something. UserForm1.CommandButton1_Click in macro1 does this ok. When that's done, control goes back to macro1, which I then want to click CB2. UserForm1.CommandButton2_Click would do this. But there are 20 or so CBs to click, so I wanted to automate the process with something like UserForm1.CommandButton(X)_Click, with values 1 to 20 assigned to the variable X sequentially. The real question is:- can UserForm1.CommandButton(X)_Click become a named procedure when X is given a number - with the correct syntax? or is it treated as a named procedure in VBA ONLY if is assigned an integer and not a variable? donwb "FSt1" wrote in message ... hi incorrect syntax. what are you trying to pass to the procedure? variables can be passed but the call command is looking for a named procedure and what you have written is not a named prodedure. UserForm1.CommandButton1_Click is your named prodedure. UserForm1.CommandButton(X)_Click is not. the correct syntax would be..... call UserForm1.CommandButton1_Click () you can pass variables, arrays or expressions but they have to be within the parenthases. but i'm not too sure about a button number. what are you trying to do...call 5 different button click? regards FSt1 "donwb" wrote: The following expression works fine and clicks CommandButton #1:- Call UserForm1.CommandButton1_Click Is it possible to assign a variable to the number of the button clicked, along the lines:- For X=1 to 5 step 1 Call UserForm1.CommandButton(X)_Click (Then does what clicking the CB does) Next X This doesn't work, but I don't know if the fault is incorrect syntax, or that the expression simply cannot take a variable. donwb |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically click CommandButton
I'd move the guts of the commandbutton#_click events to a general module.
I created a small userform with 4 buttons--Commandbutton4 runs the first 3 routines. In that General module (where all the real work is done): Option Explicit Sub CB01Click() MsgBox "cb1" End Sub Sub CB02Click() MsgBox "cb2" End Sub Sub CB03Click() MsgBox "cb3" End Sub Under the userform: Option Explicit Private Sub CommandButton1_Click() Call CB01Click End Sub Private Sub CommandButton2_Click() Call CB02Click End Sub Private Sub CommandButton3_Click() Call CB03Click End Sub Private Sub CommandButton4_Click() Dim iCtr As Long For iCtr = 1 To 3 Application.Run "'" & ThisWorkbook.Name & "'!cb" _ & Format(iCtr, "00") & "Click" Next iCtr End Sub donwb wrote: The following expression works fine and clicks CommandButton #1:- Call UserForm1.CommandButton1_Click Is it possible to assign a variable to the number of the button clicked, along the lines:- For X=1 to 5 step 1 Call UserForm1.CommandButton(X)_Click (Then does what clicking the CB does) Next X This doesn't work, but I don't know if the fault is incorrect syntax, or that the expression simply cannot take a variable. donwb -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically click CommandButton
Hi Leith
Many thanks for the code. It does just what I want - just needed a full stop before "Controls" Thanks to everyone who helped me with this including Dave's suggestion. donwb "Leith Ross" wrote in message ... On Mar 15, 12:52 pm, FSt1 wrote: hi incorrect syntax. what are you trying to pass to the procedure? variables can be passed but the call command is looking for a named procedure and what you have written is not a named prodedure. UserForm1.CommandButton1_Click is your named prodedure. UserForm1.CommandButton(X)_Click is not. the correct syntax would be..... call UserForm1.CommandButton1_Click () you can pass variables, arrays or expressions but they have to be within the parenthases. but i'm not too sure about a button number. what are you trying to do...call 5 different button click? regards FSt1 "donwb" wrote: The following expression works fine and clicks CommandButton #1:- Call UserForm1.CommandButton1_Click Is it possible to assign a variable to the number of the button clicked, along the lines:- For X=1 to 5 step 1 Call UserForm1.CommandButton(X)_Click (Then does what clicking the CB does) Next X This doesn't work, but I don't know if the fault is incorrect syntax, or that the expression simply cannot take a variable. donwb Hello Donwb, You can Click each Command Button by setting its value property to true, like this... Dim CmdBtn As Object For X=1 to 5 step 1 Set CmdBtn = Controls("CommandButton" & X) CmdBtn.Value = True Next X Sincerely, Leith Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
after click a button(commandbutton), getting the button name | Excel Programming | |||
Capture/Passing Commandbutton click | Excel Programming | |||
DTPicker Control: Can you programatically fire the click event? | Excel Programming | |||
Click on graph bar to execute a double-click in a pivot table cell | Charts and Charting in Excel | |||
Mouse Over Graph, Capture Information on Click(Double Click) | Excel Programming |