Folks...

Bit of ambitious challenge here!

I have 15 sheets. In cell A1 of each sheet the user will enter a name eg
John. I want the sheet tab for sheet 1 to change to say John. Sheet 2 tab say
Phil etc...

Also, in cell A2 I'm trying build a drop down navigation menu that uses the
names of the sheet tabs?

I'm struggling at the moment! Any help appreciated...

Gordon...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value < "" Then ActiveSheet.Name = Range("A1").Value
End Sub

Will do the Sheet Tab Names 4 u !

"Gordon" wrote in message
...
Folks...

Bit of ambitious challenge here!

I have 15 sheets. In cell A1 of each sheet the user will enter a name eg
John. I want the sheet tab for sheet 1 to change to say John. Sheet 2 tab
say
Phil etc...

Also, in cell A2 I'm trying build a drop down navigation menu that uses
the
names of the sheet tabs?

I'm struggling at the moment! Any help appreciated...

Gordon...

Gordon I had a similar problem - and tried to get some help here and found
some solution around it - send your email ad to me and
I'll give you my suggestion
--
HJN


"Gordon" wrote:

Folks...

Bit of ambitious challenge here!

I have 15 sheets. In cell A1 of each sheet the user will enter a name eg
John. I want the sheet tab for sheet 1 to change to say John. Sheet 2 tab say
Phil etc...

Also, in cell A2 I'm trying build a drop down navigation menu that uses the
names of the sheet tabs?

I'm struggling at the moment! Any help appreciated...

Gordon...

Hi Corey...

Just checking: Does this code go into the sheet code, wokbook code or a
module. Also, does this work in 2007 when sheet tabs aren't visible? Will
this also work if the value in cell A1 is determined by a validation drop
down menu?

It didn't work for me...yet!

Cheers

Gordon.

"corey" wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value < "" Then ActiveSheet.Name = Range("A1").Value
End Sub

Will do the Sheet Tab Names 4 u !

"Gordon" wrote in message
...
Folks...

Bit of ambitious challenge here!

I have 15 sheets. In cell A1 of each sheet the user will enter a name eg
John. I want the sheet tab for sheet 1 to change to say John. Sheet 2 tab
say
Phil etc...

Also, in cell A2 I'm trying build a drop down navigation menu that uses
the
names of the sheet tabs?

I'm struggling at the moment! Any help appreciated...

Gordon...

This goes under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Sh.Range("A1"), Target) Is Nothing Then
Exit Sub
End If

On Error Resume Next
Sh.Name = Target.Value
If Err.Number < 0 Then
MsgBox "Rename failed!"
Err.Clear
End If
On Error GoTo 0
End Sub


And for navigating the sheets:
http://contextures.com/xlToolbar01.html
or
http://contextures.com/xlToolbar01b.html

From Debra Dalgleish's site.

Gordon wrote:

Folks...

Bit of ambitious challenge here!

I have 15 sheets. In cell A1 of each sheet the user will enter a name eg
John. I want the sheet tab for sheet 1 to change to say John. Sheet 2 tab say
Phil etc...

Also, in cell A2 I'm trying build a drop down navigation menu that uses the
names of the sheet tabs?

I'm struggling at the moment! Any help appreciated...

Gordon...

--

Dave Peterson