Using the PerformClick() function on an excel commandbutton

Pappaloo

New Member
Joined
Oct 20, 2005
Messages
5
Hi,

I got a little bit of a problem here with this macro.

I am trying to programmatically add an Excel (not activex) command button to my sheet as part of macro that I got going. Now adding this button is not a problem, but I need to simulate a mouse click on this control at the end of the macro. Here is the code to create the button and assign the button to another macro:

ActiveSheet.Buttons.Add(Left:=ActiveCell.Left + 1, Top:=ActiveCell.Offset(1, 0).Top - 14.5, Width:=ActiveCell.Width - 1, Height:=14.5).Select
Selection.OnAction = "Change_Status"
Selection.Characters.Text = "Change Status"

Now, I am creating hundreds of the same buttons on my spreadsheet and the reason why I cannot just programmatically run the same macro which is run by my commandbutton is because I use a Application.Caller command to identify which button was pressed inside this new macro (for reasons inside this macro).

I was thinking maybe I could run the PerformClick() function but I need this button to be an object and I do not know which type an excel button is (like a activex button is an OLEObject). When I try and set an undefined variable like Set newbutton = ActiveSheet.Buttons.Add(blah blah) and then perform newbutton.PerformClick I get a type mismatch error.

As you can see I am not very proficient in programming VBA.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

garyd1234

Board Regular
Joined
Apr 17, 2003
Messages
103
try this

I think you can insert a procedure. Make it a sub or function and in that function put Call nameofbutton_Click(). Have your macro run this function by saying at the end of the macro functionname()
 

Pappaloo

New Member
Joined
Oct 20, 2005
Messages
5
The problem with that is that every button on my sheet has the same button name. So if I make a call to open that button name it will not click on the right button. The buttons must all have a different progID, but I am unsure on how to access that property with each new button.

See, this code is used to create a new row and with this new row a button is placed in one of the cells. When I click on this button I use:

Set button = ActiveSheet.Shapes(Application.Caller)
Range(button.TopLeftCell.Address).Select

to determine what cell contains the button that was pressed. That is why I was hoping that when I already have the button selected after I perform Buttons.Add, I could just use like Selection.PerformClick or something similar because the button either needs to be clicked or somehow the Application.Caller needs to be satisfied in order for the button code to run.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I'm not aware of a PerformClick function. If you want to simulate your macro you will have to copy the code from there to the procedure that adds the Button. To reproduce this:

Code:
Set button = ActiveSheet.Shapes(Application.Caller) 
Range(button.TopLeftCell.Address).Select

it would be:

Code:
    Set Button = ActiveSheet.Buttons.Add(Left:=ActiveCell.Left + 1, Top:=ActiveCell.Offset(1, 0).Top - 14.5, Width:=ActiveCell.Width - 1, Height:=14.5)
    Button.OnAction = "Change_Status"
    Button.Characters.Text = "Change Status"
    Range(Button.TopLeftCell.Address).Select
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,076
Members
412,763
Latest member
sienweiw
Top