Gordon

The code provided only hides and unhides sheets..........nothing to do with
Window Caption

For that you would need a few more lines.

Add this line to the workbook_open code.

ActiveWindow.Caption = Sheets("Sheet1").Range("A1")

Add this event to Thisworkbook module where you have the workbook_open code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWindow.Caption = ""
End Sub


Gord Dibben MS Excel MVP

On Tue, 23 Sep 2008 08:38:26 -0700, 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.