Is it better to use Active X controls or shapes with Macros attached

Hello: This is a general question regarding best practices or how to optimize performance. Hoping someone will have insight based on past experience because Microsoft support has not been helpful.

I have a large workbook that has 10 tabs in it that contain several activeX checkboxes. As you click on the boxes rows unhide with more options for checkboxes, data entry etc. Each sheet is identical in that each sheet is used to define settings for a "room". It works beautifully. However, now we want to set this up to give us 25 "rooms". If I create a total of 25 the spreadsheet no longer works. I can open it but the boxes, etc. won't load properly and the spreadsheet looks mostly blank because the pictures (from the controls) can't be displayed. I suspect it's due to the resource intensity of these controls.

What I'm looking to confirm is that I could replace the checkboxes with shapes to click on instead but not sure if this will solve my problem. I feel like shapes might be a bit less resource intense because I can assign the macros at a module level vs. private. So if each tab/room has 60 active x checkboxes, I currently have 60x10 sheets =600 little macros saved. If I use a shape I can refer to a macro in a module and only have 60 (vs. 600).

Does this sound reasonable? Asking first because it's a LOT of work and time to change this and figured the smart folks in here might know.

Thoughts or even other suggestions for best practices here would be appreciated.

Being realistic, I can't answer your question.

But . . . have you thought about ditching all the Active X Controls and just using Event handlers. First thought was to use a Worksheet Selection Change Event handler but that might be too easy to fire inadvertently.

Maybe better to use the BeforeDoubleClick event handler. For example:

Please Login or Register  to view this content.

Or you could even use the Workbook.SheetBeforeDoubleClick event handler and put the code in one place. That should work well and minimise the code if all the sheets are structured in the same way.

See: https://learn.microsoft.com/en-us/of...oredoubleclick

Anyway, just a thought. It should reduce the resource requirement dramatically. Maybe not as pretty. But, using fonts, fill colours and borders, you could simulate a Button without the overhead.

see big yellow banner - upload a workbook - there has to be a simpler way than replication.

Instead of constructing all those individual room sheets why not consider using 'bespoke' userform with multiple layers of 'multipage pages'.
This can be self-constructing 'on the fly' or individually tailored to suit each room, there should be no need to have a 'quotation form' full of formula.
To save the quotation data you only need a primary key reference, followed by a secondary key referencing your items table, followed by a quantity - you then pull the data into 'quotation' by code.
This is far faster than having a sheet full of formula where 75% may contain nothing (if you wish to continue using the quotation using formula, consider using a structured table (as you add your next row your formula will dynamically expand).
Attached is a solution I posted on this site a few years ago (although it is Swiss/German - the basis was the enquirer needed a means of registering all details of properties in a rental portfolio).