Using Worksheet Change events to trigger macros

Joined
Jul 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,949
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).
 
Joined
Jul 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,949
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,381
Office Version
  1. 365
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,075
Messages
5,570,051
Members
412,309
Latest member
essobsan
Top