Hello! I am relatively new to VBA and Excel, so please pardon if my code/knowledge is lacking.
I have a worksheet that deals with locations and facilities. The user can choose up to 10 locations via a dropdown cell menu in cell D4. This uses data validation, just a list, with values 1-10 as options. I have the corresponding sections in named ranges location1, location2, etc. Depending on how many locations the user selects in D4, the corresponding named ranges are revealed. The rows they're in are hidden by default.
Within these named ranges, there is another dropdown cell, where the user can select up to 5 facilities that will be visited at each location. This dropdown cell is similar to D4 in that it's just a data validation list with options 1-5. This also refers to named ranges, facility1.1 through facility1.5. Depending on how many facilities the user selects in the dropdown cell, the macro hides/reveals the associated rows.
My problem is, if the user selects more than one location, that is, if they change their input in D4 to 2 rather than 1, it will reveal all hidden rows pertaining to facilities, including the ones that the facilities macro is trying to hide. I'm basically trying to nest these named ranges and drop-down cells and not having luck.
I need to somehow have it check and see if rows are hidden before revealing them and the next named range.
I currently have the two macros in separate modules (1 and 3). In my code, I refer to cell K16 as the drop-down cell for choosing how many facilities per location. Obviously, there are many more drop-down cells than just K16 (although all are in column K), but I was just trying to get one instance to work before delving into the rest.
Please check out my attached spreadsheet. For quick reference, here is my code for Module 1 which deals with the Facilities:
Here is my code for Module 3 which deals with Locations:
Any help is appreciated. I'm not the best at explaining things, so it may be easiest to checkout the attachment and just see the issue when you select 2 locations instead of one. You'll see how it then reveals all of the facility rows despite only one facility being selected. Thanks!
Bookmarks