How to create a dynamic button in excel

Question

I'm populating my spreadsheet with Database values.Now if I 30 rows were filled then I want to create a dynamic button on say 31st or 32nd row for doing some action.The number of rows that will be populated is not fixed.How can I do this.

Answer 1

I assume that you will cycle through the records after the query has filled your table, search for a condition and "do stuff". I therefore asume that the location where you want to place the button is represented by a Range() object within the ActiveSheet()

So let's create a dynamic ActiveX button at that location:

Sub CreateDynamicButton()
Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long


    Set MyR = Range("C110") 'just an example - you get that from your own script
    MyR_T = MyR.Top         'capture positions
    MyR_L = MyR.Left        '...
    'create button
    Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)

    'set main button properties
    With MyB
        .Name = "MyPrecodedButton"     'important - code must exist ... see below
        .Object.Caption = "MyCaption"
        .Top = MyR_T
        .Left = MyR_L
        .Width = 50
        .Height = 18
        .Placement = xlMoveAndSize
        .PrintObject = True            'or false as per your taste
    End With

End Sub

If - in advance - you have created following routine within the active sheet

Private Sub MyPrecodedButton_Click()
    MsgBox "Co-Cooo!"
End Sub

then a nice message box will appear once you press the button created above (tested under XP/SP2 + Excel 2003).

The Create routine doesn't ask if a button of same name exists, you need to take measures to create it only once with the same name. If you call the routine twice, the .Name = "..." will silently fail and start naming the button "CommandButton1" and up.

So you should have all ingredients now to create your button(s). Each of them will need to have a precoded procedure if they need to act differently. I should mention you cannot debug (step through) the Create routine after the cration of the OLE object, because control is transfered outside Excel - "it's not a bug, it's a feature!"

I have to admit for me it sounds a bit unusual and I would probably prefer not to install dynamic buttons acting on pre-coded Sub's, instead I would do an initial dialog before the query giving options via checkboxes like "Truncate after X rows (Y/N)" and the like - but you will have good reasons for doing it your way.

Hope that helps - good luck