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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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()
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top