Using Worksheet Change events to trigger macros

[theluggageofmanylegs]

Hi folks,

First time poster here - apologies if I get anything wrong etiquette-wise.

I have a worksheet that has 7 either/or questions at the top for the user to answer, with two buttons associated with each - think "Used"/"Not Used". Based on the button that the user clicks, a macro is run to autopopulate certain cells and show/hide specific rows.

I'm trying to think ahead and code for a situation where someone skips right past my lovely formatted buttons and decides to answer the questions from the get-go. I know I can trigger my macros to run using Worksheet Change events - my question is, is it very messy to do this for the 14 different situations (7 questions x 2 answers) in one sub, as each will have different things to do? I'm assuming it isn't possible to do separate worksheet change subs - if so, please excuse my ignorance, still figuring all of this out!

 

[mumps]

It would be easier to help if once you have designed your worksheet, you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[theluggageofmanylegs]

Thanks mumps - unfortunately addins have been disabled by our IT department so I can't do that - but as an approximation, see below:

Question 1 - options are "Used" or "Not Used"

If user clicks "Used":
Macro1 changes value of cell F44 to "Used" and hides row 45

If user clicks "Not Used"
Macro2 changes value of cell F44 to "Not Used" and unhides row 45

So in this case, I would want my Worksheet change event to run Macro1 if the user skips the buttons and instead F44 is changed to "Used" (from a dropdown, created with data validation), or Macro2 if F44 is "Not Used"

Question 2 - options are "Applicable" or "Not applicable"
If user clicks "Applicable":
Macro3 unhides rows 66-70 and changes value of F65 to "Applicable - see below"

If user clicks "Not applicable":
Macro4 hides rows 66-70 and changes value of F65 to "Not applicable"

In this case, I would want my Worksheet Change event to run Macro3 if the buttons aren't used, but the user changes F65 to "Applicable", or Macro4 if they select "Not applicable"

My question is - can I use one Worksheet Change sub for both of these cases, or does that get too messy?

 

[mumps]

You can most likely use one macro to do everything but it is hard to visualize how the buttons are set up. Do F44 and F65 start out blank? Also we would need to know all 7 situations. Could you possibly post a picture of what your sheet looks like? Alternately, could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com? Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Joe4]

my question is, is it very messy to do this for the 14 different situations (7 questions x 2 answers) in one sub, as each will have different things to do? I'm assuming it isn't possible to do separate worksheet change subs - if so, please excuse my ignorance, still figuring all of this out!

That is correct. You can only have 1 Worksheet_Change event procedure per sheet.
To tell you truth, checking 14 conditions inside one probably isn't that bad (I have seen much more).
My advice to you as to whether you want to have each one call and run a macro, or just do it right in the Worksheet_Change procedure is this:
1. If the code for each one is long, put each one in its own procedure
2. If the same code is run for multiple selections, then create a procedure for each one and call it
3. If the code for each is short, and doesn't repeat for any of the options, then just do it all in the Worksheet_Change procedure

To me, that makes the most sense. But that is just my opinion...