3

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.

Ank
  • 6,040
  • 22
  • 67
  • 100
  • 1
    It would be simpler to use a Forms button instead of the ActiveX type (unless there's some reason you really need the ActiveX one). Then assign the macro using onAction as Jon suggested. If you name your buttons using the row number, you can use Application.Caller in the macro to get the name of the clicked button. – Tim Williams Nov 23 '11 at 23:45
  • Well I've used ActiveX throughout my project. Now its become big and I can't revert :-( – Ank Nov 23 '11 at 23:48
  • 1
    In that case there is an approach here which should work: http://stackoverflow.com/questions/1083603/vba-using-withevents-on-userforms See Gary's answer. – Tim Williams Nov 23 '11 at 23:50
  • I like that approach but then I don't know how many command buttons do I have in my worksheet. They can be anywhere from 2 to thousands.. They are dependent on number of lines in the worksheet.. I wish I could create an array of command buttons. – Ank Nov 24 '11 at 00:09
  • Having a button for each row, with possibly thousands of rows, is very difficult to maintain. Much better to add a button to the Row right-click menu (or the ribbon) that acts on the currently selected row(s). – Doug Glancy Nov 24 '11 at 01:35
  • @Ankur - that approach can work for any number of buttons (within reason). If you don't like the alternative of using form buttons then maybe consider using hyperlinks instead... – Tim Williams Nov 24 '11 at 03:03

2 Answers2

3

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
HRgiger
  • 2,750
  • 26
  • 37
  • The thing here is that I do not know how many buttons I have to create. The number of buttons is a function of number of lines in the sheet abc. I do not have a single DynamicButton.. – Ank Nov 23 '11 at 23:02
  • @Ankur, Alex has a nice example here: http://stackoverflow.com/questions/566770/assign-on-click-vba-function-to-a-dynamically-created-button-on-excel-userform – HRgiger Nov 23 '11 at 23:18
  • Also I can't create lots of command buttons and make them invisible as suggested.. The number of rows can be anywhere between 2 and thousands.. :-( – Ank Nov 23 '11 at 23:50
2

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

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
  • Thanks.. I was doing the same thing.. :). How do I assign runtime events to it now. How do I use macro recorder for this? – Ank Nov 23 '11 at 22:55
  • Add the name of the sub you want to call using the OnAction property on the button, e.g. `.OnAction = "Macro1"`. – Jon Egerton Nov 23 '11 at 22:58
  • For macro recording just google it - its the same as recording other actions. Once you recorded the code, go into the VBA afterwards to view what was recorded. – Jon Egerton Nov 23 '11 at 23:00
  • When I do that it says "Unable to set an OnAction property to OLEObject Class – Ank Nov 23 '11 at 23:30
  • You can avoid the Select from the redcorded macro and just use `ActiveSheet.Buttons.Add 126.75, 39.75, 46.5, 19.5` – brettdj Nov 24 '11 at 06:38