Hide/unhide sheets as per list in named range

**rain4u** · 02-18-2012, 10:59 AM

Hi
I wonder can help me. I have the following sheets within my workbook:

Interface
properties M60
properties M61
properties M62
properties M64
properties general

On sheet "properties general" in cells G1 to G7 I have the following values:

G1 ---- Sheets (properties)
G2 ---- All sheets
G3 ---- properties M60
G4 ---- properties M61
G5 ---- properties M62
G6 ---- properties M64
G7 ---- properties general

Now I have created a dynamic damed range (Sheet_count_DYNAMIC_Properties). This named range essentially covers G3 to G7 (highlighted in green).

My question is, how to code in VBA to hide all sheets declared in named range Sheet_count_DYNAMIC_Properties???. I have attached example xls.
Any help is much appreciated.

**Mordred** · 02-18-2012, 11:19 AM

Hi rain4u, try the following two procedures:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**AndyPS** · 02-18-2012, 11:27 AM

If you have sheet names in a range, you can just loop through all the names (either by each cell or using an offset such as range_name(x,1)). It is then just a matter of setting the worksheet visible property

Please Login or Register  to view this content.

I often put in a hidden sheet listing all the worksheets along with visibility, protection, cell selection, scroll area. On start-up the code checks the sheets are present and sets the values per the list. Other code can unhide, remove the protection etc. This is good for debugging.
At least one sheet must be visible but I don't think you have that problem.

**royUK** · 02-18-2012, 11:59 AM

Do you really need to have the list on a sheet. Someone could delete that list or a sheet name in it & the code would.t work.

You could simply have the list within the code

Please Login or Register  to view this content.

**rain4u** · 02-18-2012, 01:14 PM

Thanks for all of the answers. I went with solution provided by Mordred.
I just changed Ws.Range("G3:G7") to Ws.Range("Sheet_count_DYNAMIC_Properties"). Its just a matter of taste.

Roy's solution would work as well, but I would like to avoid hard coding it in on this occasion.

ExcelForums.com never fails. Thanks again.