Hiding and unhiding worksheets based on a cell value from another worksheet

**J_barber** · 06-05-2013, 12:44 PM

I am trying to set up an excel spreadsheet so that based on a value in a cell it will unhide a given number of worksheets. Example: I have 6 worksheets named Section 1, Section2, Section 3, etc. On the “Input” worksheet I have a cell where I want to enter a value between 1 & 6 and based on that value the given number of worksheets will unhide. If I enter the value of “2” then I want worksheets “Section 1” & “Section 2” to be unhidden, a value of 3 would unhide “Section 1”, “Section 2” & “Section 3”. I want to accomplish this using VBA.

**Charles** · 06-05-2013, 01:02 PM

HI,

A suggestion is to use a worksheet Change event that will trigger a code to unhide the work sheets.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Mynr as Long
dim i as long
    If Target.Address = "$A$1" Then''change to the location in the Input sheet
      Mynr = Target.value''' added
      ''' set loop to unhide the sheets ''
       For i = 1 to Mynr
         ''' place code here to unhide the sheet
             Sheets("Section" & i).Visible = False
       next i
   end if
 end sub

Not tested but you get the ideal.