Not sure if this is the best forum for this kind of help that I'm looking for (G&T might be better?)
I've been tasked with a project to help our sales reps be more consistent when they send a quote/offer out to their clients. Right now, as we have a bunch of lifers who've been doing this for 10+ years, and a lot of turn over for new reps, they all have their own method when sending out a quote. Problem is, if the rep is out sick, and they get transferred to a colleague (or let go and their cases assigned to someone else) everyone's doing something different, not to mention the possibility of quotes/offers going out with wrong information, or non-professional verbiage and spelling errors.
I figured the easiest way to deal with it all is to create a checklist that has all of our products separated by groups (hardware, software, third-party hardware, value-adds, etc.) and as the rep talks to their client about what products they want, they just click on the group (Hardware for example) and then the kind of product(s) under that group, along with the number of units and the negotiated price for them (2 units of Device A at $25.00, 1 unit of Device C at $50.00).
Here's where I start running into problems.
I'm doing this in Excel. Making the checklist is easy, but I want the form to be as idiot-proof as possible. The top 7 rows are fixed, and never to be hidden. When a rep clicks on the checkbox for either New Client or Existing Client, it unhides the header rows (9, 17, 28, and 42, for example). These headers have a checkbox next to their title as well, so when the rep has determined that the client wants stuff from Hardware, they click the box on row 9 and it expands to unhide rows 10-16.
Each of those
rows has checkboxes next to each product, so the rep can click on the box, which unhides the corresponding product name on a separate worksheet with the entered amount of units and total cost.
The separate worksheet is what would ultimately be what the rep exports to a pdf (easy) and sends to their client. It will always look the same, it would contain all of our approved images and disclaimers from Marketing, and it's clean and only has the details of the products the rep and client agreed on. Everything else remains hidden and not used. When the rep is ready for a new quote, they click the big red RESET button and start from scratch.
Where I need help is in writing the macros so that separate rows hide/unhide as the checkboxes are checked/unchecked.
The other major problem is Excel treats checkboxes as objects, so they don't hide when the rows containing them are hidden. One of the coders in the company gave me some assistance with hiding objects, but their code hides boxes one at a time, and this checklist will probably have upwards of about 200+ boxes by the end of it all. I can't have Excel call out to every individual box every time one of the header boxes is checked/unchecked.
Is there a way to hide all checkboxes all at once and unhide them as the rows are unhidden too?
Is there maybe even an easier way to do what I'm looking to do? Some kind of third-party software maybe, though I'm not sure if I could get the company to approve purchasing a new software for something like this.