Creating alot of controls at runtime is too slow!!

Hi

what im doing is creating controls (buttons) at runtime based on data contained in the table..... the data is contained within a listobject and im looping through the rows of this list and comparing data in 3-4 cells to determine whether a control should be placed over each of these cells (up to 3 buttons per row).

i have gone through the usual steps of preventing excel updates (screen updating, events etc etc)
i have also tried various variations of the loop, (using an array(for loop) instead of ranges(for each loop) to check the values)

the table which is currently just for development can reach up to 500 rows which is tiny compared to the real table which may contain 10's of thousands, the dev table is taking around 20-30 seconds to draw these controls and this is just not fast enough... imagine a table 100 times larger.....zzZZZ coffee...more ZZzzz haha

anyway i was wandering if anyone knows a way to create them in bulk or whether having a repository of 100'000 controls waiting somewhere would be OK (i dont like that idea but), i was looking into multi-threading this process but the techniques to do this arnt without problems.

i also tried just formatting cells to look like buttons which actually is a nice workaround however has drawbacks with visualizations (no indication that its a button if you put your cursor on it etc) and still this method is not fast enough (i though about this method pasting to an entire range, still thinking about it

anyone got any ideas?
Thanks

Paul S.

How about a custom pop-up menu with three buttons. Make them visible as needed depending on the selected row.

The attached has some simple routines. Select a cell in rows 2:15 of sheet1 and click a button.

You could use either the Enabled or Visible property of the controls.

Note that the Temporary argument of Add methods has been used to avoid mucking up the clients Excel application.

Please Login or Register  to view this content.

The optional arguments in the below routines are just there to hide them from the Macro dialog box.

Please Login or Register  to view this content.

I might consider using a combination of formatting the cells as you have tried, and then only adding a single control on the cell that is being hovered over, removing it when you move away.
(Edit: Although my multi-platform-addled brain may be missing the fact that there may be no 'hover' event to use)

i keep loosing my messages :/ pressing the wrong button haha

I really would love a mouse over event on a worksheet haha, that would allow for some pretty inventive sheets......... Mike...Thanks for idea unfortunately i tried something similar and although its quick i ran into a problem where navigating the worksheet became a knightmare, the controls acted as a visual aid for the sheet and after removing them all i had a sheet full of numbers and characters and it was very hard to look at, i would have tried mixing the ideas of formatting and just drawing the controls on a single line but the formatting also was too slow......

in the end i decided to develop it on visual studio using a form and a couple of gridviews, its working out great so far and its very responsive.

Thanks anyway guys