I just noticed that you have reversed the True and False. The way that you
have it, the sheets will all be visible at open and will not be visible when
the code word is entered in M13.

"Gordon" wrote:

Hi

I've put the first code block into the workbook code and the second into the
worksheet.

When I run it I get an complie error with the sh.hidden=true that says
Method or Data member not found.

Any help settling this in would be great... this is what I have so far.

Private Sub Workbook_Open()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name < "setup" Then
Sh.Hidden = True
End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target = Range("M13") Then
If Target.Value = "ABC" Then
For i = 2 To Worksheets.Count
Sheets("Sheet" & i).Hidden = False
Next
End If
End If
Exit Sub
End Sub


"JLGWhiz" wrote:

Also noticed a typo in the Dim statement

Dim i As Loong

Should be: Dim i As Long

"Gordon" wrote:

Hi...

The second block of code came up with a compiler error and said it expected
an 'end sub' which I added. Nothing happened after this. I entered a word
into A1 and the window caption didn't change. Any more thoughts?

Thanks

G

"JLGWhiz" wrote:

These are untested but I think with two macros you can do what you want.
The workbook_open goes in the ThisWorkbook code module and the
Worksheet_Change goes in the code module for sheet1.

Private Sub Workbook_Open()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name < "Sheet1" The
Sh.Hidden = True
End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Loong
If Target = Range("A1") Then
If Target.Value = CodeName Then
For i = 2 To Worksheets.Count
Sheets("Sheet" & i).Hidden = False
Next
End If
End If
Exit Sub

These are untested. To access the code modules, press Alt + F11, Then in
the project window in the left panel, right click the ThisWorkbook name or
Sheet1 name as applicable to select View Code from the drop down menu.

"Gordon" wrote:

Hi...

Is it possible to have a window caption read the value of a cell?

Also, I have 15 sheets in my spreadsheet. How can I hide them until the user
inputs a code number in cell A1 on sheet1

Thanks

Gordon.