Window Caption

[Gordon]

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.

 

[JLGWhiz]

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]

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]

You will need to substitute your actual code name where you see CodeName in
the code.

If Target.Value = CodeName Then


For expl: If Target.Value = "Prestto" Then

Or you can add a line before the If statement:

CodeName = "Presto"
If Target.Value = CodeName Then

Then when "Presto" is typed in A1 it shouild work.

 

[JLGWhiz]

Also noticed a typo in the Dim statement>

Dim i As Loong

Should be: Dim i As Long

 

[Gord Dibben]

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

 

[Gordon]

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]

Sorry for the mix-up. Should have used Visible
instead of Hidden. Hidden is for controls, etc.
Also, see Gord's comments on the Window Caption.
Notice that he suggests using two statements. One
to add the caption and a second to remove it before
the workbook closes. The Window caption is part of
Windows and not Excel so could affect subsequent
documents if not removed each time the Excel wb is
closed. See the revised code below.


Private Sub Workbook_Open()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name <> "setup" Then
Sh.Visible = 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).Visible = False
Next
End If
End If
End Sub

This revised code should now operate as you expect, except for the caption.
I thought you would want to work with that.

 

[JLGWhiz]

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.

 

[JLGWhiz]

That was my fault, I should have changed them when I changed to Visible
instead of Hidden. Visible/False = Hidden/True and vice versa.