Conditional sheet unhiding

[Todd Huttenstine]

Hey guys, I have a list of names in column B starting in cell 3 going down
on sheet "Customer Records". This list will continue to grow. I would like
a code that will count the number of names from cell B3 down and then unhide
that many number of sheets starting with sheet 3 as well as rename each
sheet by the name listed in each corresponding cell. For instance, there
are 5 people in the list. The name Jeff is in B3, Tom in B4, Nancy in B5,
Jim in B6, and Pat in B7. The code must unhide 5 sheets(sheets3-7) and
rename each sheet according to the name of the corresponding cell starting
with sheet 3. For instance, sheet3 unhides and is renamed Jeff, sheet4
unhides and is renamed Tom, sheet5 unhides and is renamed Nancy, etc... If
a name is taken out of the list, I need for the code to hide the
corresponding sheet.

thanx

Todd

 

[Bob Phillips]

Todd,

here you go to set it up

Dim cRows As Long
Dim i As Long
With Worksheets("Customer Records")
cRows = .Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To cRows
Worksheets(i).Visible = True
Worksheets(i).Name = .Cells(i, "B").Value
Next i
End With

To remove it I suggest evcent code. Setup the names list as a named range
called 'names' and add this to the Customer Records worksheet module

Dim oldVal

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Range("names"), Target) Is Nothing Then
oldVal = Target.Value
End If
ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Range("names"), Target) Is Nothing Then
Worksheets(oldVal).Visible = False
End If
ws_exit:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

You got a few answers to your thread in .programming.