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.
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.