Adding command buttons to worksheet at run time and also define events
Solution 1
The easiest way to work this sort of thing out is to record a macro, then perform the action, and see what code gets recorded. In this case I recorded a macro and added a button to the sheet and got the code snippet:
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveSheet.Buttons.Add(126.75, 39.75, 46.5, 19.5).Select
End Sub
You should be able to take it from there...
Solution 2
it can help, probably you will wonder also how to access it;
Sub addButton()
Dim myButton As OLEObject
Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=0, Top:=300, Height:=20, Width:=200)
myButton.Placement = XlPlacement.xlFreeFloating
myButton.Object.Caption = "Click Me..."
myButton.Name = "DynamicButton"
End Sub
Private Sub DynamicButton_Click()
MsgBox "Hello sheet"
End Sub
Ank
Updated on June 04, 2022Comments
-
Ank almost 2 years
I am trying to add an ActiveX command button in my work sheet at run time. The number of command buttons will depend on the number of lines in the work sheet. I plan to give x and y coordinates in the command button property to position them correctly. I understand that we can insert command buttons in user form this way.
Private Sub CommandButton1_Click() Me.Controls.Add _ "Forms.CommandButton.2", "CopyOf" End Sub
How can we insert command buttons in a work sheet (not user form). Worksheets("abc").Add doesn't work. Also how to I define independent click events for them. For example if I click a command button, it should tell me which row is it in.
Thanks
--------------UPDATE -----------------------------
So I am able to add the command buttons in their required spot dynamically based on the number of lines in my worksheet.
Private Sub addb3(ByVal rows_present_alerts As Integer) Dim topcounter As Double topcounter = 15.75 For i = 2 To rows_present_alerts ' The first row has the column headers With Worksheets("abc").OLEObjects .Add(ClassType:="Forms.CommandButton.1", Link:=False _ , DisplayAsIcon:=False, Left:=509.25, Top:=topcounter, Width:=48, Height:=14.25 _ ).Select End With topcounter = topcounter + 15 ' (not sure this approach will work in monitors with diff screen resolution but anyways) Next i End Sub
I want to assign click events to each command button. When I click the command button, it should tell me which row is it in.