4

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.

Community
  • 1
  • 1
gizgok
  • 7,303
  • 21
  • 79
  • 124
  • what does "populating ... with database values" mean ? you populate by user input or by means of VBA code? – MikeD Aug 23 '10 at 16:32
  • Through VBA.Sorry if I got the jargon wrong.I'm making a DB connection through VBA and then doing the Select command to get data in the spreadsheet. – gizgok Aug 23 '10 at 18:12

1 Answers1

8

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

MikeD
  • 8,861
  • 2
  • 28
  • 50
  • I've populated my spreadsheet right after that,I select a cell,so when I select the cell,I should get a button to update my changes.I would try your code snippet and lert u know.Thanks for the effort – gizgok Aug 25 '10 at 12:37
  • Have you created a reference from VBA to "Microsoft Office Forms 2.0 Object Library" or aequivalent (Tools / References) ... this library is found in .../System32/FM20.DLL – MikeD Aug 26 '10 at 09:16
  • Do I need a reference for ActiveSheet.Buttons also – gizgok Aug 26 '10 at 11:29
  • no - ActiveSheet is a generic Excel object, no external DLL is needed to work with that object (in fact it's not an object of its own, but a reference to a Sheet object) – MikeD Sep 04 '10 at 10:04