Add Controls With Events to Worksheet at Runtime

[llowwelll]

I want to be able to add controls to a VBA worksheet at runtime and then be able to handle them with events. How do I do this?

Specifically, my application automatically queries a constantly changing Access database every five minutes. The records from the query are then displayed on an Excel worksheet. There is one row for each record. What I need to do is add a couple of command buttons and a label to each row/record. If the user clicks on one of the buttons or labels added at runtime, I need to be able to respond to this with code.

Thanks very much in advance for any help or advice that you can provide!!

 

[SkipVought]

Hi,

I'd suggest a simpler solution. ONE SET of Command Buttons. (knock yourself out if you want a label for each row)

When the user selects a cell in a row (could be ANY cell or a cell in certain column(s)), the set of buttons is made visible and positioned relative to that row. When the user clicks a button, the ROW CONTEXT is passed to a routine to perform whatever you want.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Fherrera]

Or better yet just use one floating toolbar. I find them more attractive then commandbars for some reason... (although you'd have to create it dynamically and delete it before closing the workbook if you want to keep it specific to the workbook)

 

[SkipVought]

I should have been more specific.

When a single cell is selected in the target columns...
[tt]
the control is made visible and positioned relative to the selected row
[/tt]
When a selection is amde in other than the target columns...
[tt]
the control is made invisible
[/tt]
When the control is clicked
[tt]
code is executed based on the context (row/column) of the selected cell and the control is made invisible.
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884