Multiple Worksheet Change events??? "macro" needed to hide/unhide rows multiple instances

Hi dgibney,

Before starting here are a few tips:
1. To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

2. To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

3.Using the debugger.
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.


4. To import or export VBA code:
a. To export, right click on the Module Name in the 'Project Explorer'.
b. Select export file. I suggest you use a SubFolder that only contains exported (.bas) files.
Keep the original name.
c. To import, right click anywhere in 'Project Explorer'.
d. Select import file. Select a file to import.

---------------------

There are many different types of Modules. I am just going to describe the follow 3 module types:
1. Ordinary Code module. That is where most of the code is usually placed. Ordinary modules have names such as 'Module1' or 'Module2'. In the VBA editor, to create an ordinary code module:
a. Right Click on any item for the workbook in the 'Project Explorer'. That item will be highlighted.
b. Select Insert > Module

Names like Module1 are not very useful. To rename an ordinary VBA module from the VBA Editor:
a. Press f4 to show the Properties Window.
b. Change the name of the Module in the Properties Window.

2. ThisWorkbook Module. 'ThisWorkBook' module contains special code to process Workbook type events, such as Workbook_Open() to do something automatically each time the workbook is opened.

3. Sheet Module. Sheet modules contain special code to process Sheet type events such as Worksheet_Change(), which is activated each time a user manually changes a value in the sheet or when VBA code changes a value in the sheet.

-------------

To copy code, from one source to another you can:
a. Cut and Paste (just like in a word processor).
b. For ordinary code modules you can export the code to a file, and then import to a new workbook.
c. To copy an entire workbook sheet and it's associated code (Code in the Sheet1 module for example),
copy the sheet to the new workbook manually in Excel.
d. I don't recommend exporting and then importing ThisWorkbook or Sheet code directly.


-------------

Attached is an implementation of my original concept for 'J18' and 'J20'. It is NOT SUITABLE for having 50 options as you mention in your original post, because the design makes it easy to place code in the wrong place. That means each time you make one change you have to test everything. The next post will contain a similar solution that is more compartmentalized and easier to maintain.

Lewis

Sheet1 Code:

Please Login or Register  to view this content.

Module ModDisplayOrHideRows code:

Please Login or Register  to view this content.