As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
Excel Macro/VBA help - creating a checklist that hides/unhides rows and buttons
El Fantastico
Toronto, ONRegistered User regular
Toronto, ONRegistered User regular
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.
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.
PSN: TheArcadeBear
Steam: TheArcadeBear
Steam: TheArcadeBear
El Fantastico on
0
Posts
And that way you don't have to worry about them losing the base copy of the spreadsheet, or somehow disabling macros, or an office update breaking it,
Or tracking 200 objects and cell references in excel's clumsy scripting environment.
This google sheet has something sort of similar to what you want or at least could be modified
https://docs.google.com/spreadsheets/d/1vlWfhgM6K4iQF2RdnMNDBpDRY14UaK0f4AAq0FHdONk/edit?usp=sharing
basically you select the thing you want and it shows you only those.
its using the filter command
a) deletes the cell and kills a block of formulas in the process (bonus points if they choose "move cells up" and not row
b) pastes something into the cells from another document and kills the formatting
c) copied the entire document, saved it as a "plain sheet" and then had people start using that until people notice that "it's not working right"
I can agree with Tofy that if you can, you should just get a web app or a php-form script or something... or even use MS Access if you have it.
I hate the form they're going with though. It has unnecessary fields and boxes and it's wasted time for the rep to remove things that aren't part of the discussion they might have just had with the client. Or if they don't remove it, then it's just empty space which looks unprofessional.
I may still try to find an alternate way of doing this with your suggestions, but I'm not going to devote a whole lot of time to it as a side project. Thanks for helping where you all could.
Steam: TheArcadeBear