Excel Macro Challenge
Hi experts,
I'm a little out of my comfort zone here, being an Access developer who now has an Excel VBA challenge.
I have a workbook with many worksheets. On one of the configuration sheets I have a series (perhaps could be a number of named ranges) with yes/no combos. Three columns to the left of each is a unique code no.
As each yes/no is set, it needs to gather the code number, then go to a selected number of other worksheets and find that code in a predetermined colum in each. The worksheets and columns can be stored in a table in another worksheet. Once found (and there will always be a match) the row that the code is in is hidden if the yes/no is no, and unhidden if yes.
To further complicate things, whenever the workbook is opened, it needs to check each and evry one of the yes/no values and ensure that the the appropraite rows are hidden or not.
I'm quite happy to battle through the VBA if I can get a few pointers and equally happy to break this into a number of questions if there's too much here.
Kelvin
Kelvin
I'm a little out of my comfort zone here, being an Access developer who now has an Excel VBA challenge.
I have a workbook with many worksheets. On one of the configuration sheets I have a series (perhaps could be a number of named ranges) with yes/no combos. Three columns to the left of each is a unique code no.
As each yes/no is set, it needs to gather the code number, then go to a selected number of other worksheets and find that code in a predetermined colum in each. The worksheets and columns can be stored in a table in another worksheet. Once found (and there will always be a match) the row that the code is in is hidden if the yes/no is no, and unhidden if yes.
To further complicate things, whenever the workbook is opened, it needs to check each and evry one of the yes/no values and ensure that the the appropraite rows are hidden or not.
I'm quite happy to battle through the VBA if I can get a few pointers and equally happy to break this into a number of questions if there's too much here.
Kelvin
Kelvin
StephenJR
Kelvin - can you post a sample workbook?
ASKER
I'll make up a sample and upload
ASKER
The sample xlsx file has a number of sheets. If you look at the sheet select product - two rows have yes and one no. Looking at worksheet Admin, it tells us to only use workbooks Wholesale and Retail.
Going back to Select product, there is a yes in the row for product 100, I want it to check the worksheetslisted in Admin and look in the column indicated for that worksheet for the code 100. As this is yes, that row containing the code 100 in each of the Retail and wholesale workbooks should be unhidden. For product code 101, there is a no, so the rows for the 101 code in thosew two sheets are to be hidden. Worksheet Product Details is not listed in Admin and so is to be ignored for this exercise.
Kelvin
Sample.xlsx
Going back to Select product, there is a yes in the row for product 100, I want it to check the worksheetslisted in Admin and look in the column indicated for that worksheet for the code 100. As this is yes, that row containing the code 100 in each of the Retail and wholesale workbooks should be unhidden. For product code 101, there is a no, so the rows for the 101 code in thosew two sheets are to be hidden. Worksheet Product Details is not listed in Admin and so is to be ignored for this exercise.
Kelvin
Sample.xlsx
For worksheet Wholesale I take it should be col B to check rather than C? Does the (un)hiding of rows apply only to those codes in the first sheet - i.e. if other rows are hidden they should be left as they are?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I'm away today, but will try this over the weekend - looking very very promising!
Kelvin
Kelvin
ASKER
Thanks for the very efficient code. I'm having some fun and games with it running against locked or protected sheets, but am working through this.
ASKER
Hi,
The link below is a followup question for this answer - more points on offer!
Hope you can help
https://www.experts-exchange.com/questions/27672336/Excel-Macro-Challenge-Pt-2.html
The link below is a followup question for this answer - more points on offer!
Hope you can help
https://www.experts-exchange.com/questions/27672336/Excel-Macro-Challenge-Pt-2.html