Does anyone know of a method of getting tab titles to update automatically to
changes in worksheet contents? i.e. if I have a worksheet with a list of
words which I want to set equal to tab titles, so when I change the words the
titles update automatically, is this possible?

Like a normal function but for tab titles.

Thanks

Hi,

Try this. Alt + f11 to open VB editor. Double Click 'This Workbook' and
paste this in on the right. Enter a name in A1 and provided it's a legal name
Sheet 1 will be changed to that name. A2 for Sheet 2 etc. If you have more
than 10 sheets extend to range A1 - A10.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Sheets(Target.Row).Name = Target.Value
Application.EnableEvents = True
End If
End Sub

Mike

"LiAD" wrote:

Does anyone know of a method of getting tab titles to update automatically to
changes in worksheet contents? i.e. if I have a worksheet with a list of
words which I want to set equal to tab titles, so when I change the words the
titles update automatically, is this possible?

Like a normal function but for tab titles.

Thanks

Hi,

Forget that. Right click a sheet tab, View code and paste this in there.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Sheets(Target.Row).Name = Target.Value
Application.EnableEvents = True
End If
End Sub

Mike

"Mike H" wrote:

Hi,

Try this. Alt + f11 to open VB editor. Double Click 'This Workbook' and
paste this in on the right. Enter a name in A1 and provided it's a legal name
Sheet 1 will be changed to that name. A2 for Sheet 2 etc. If you have more
than 10 sheets extend to range A1 - A10.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Sheets(Target.Row).Name = Target.Value
Application.EnableEvents = True
End If
End Sub

Mike

"LiAD" wrote:

Does anyone know of a method of getting tab titles to update automatically to
changes in worksheet contents? i.e. if I have a worksheet with a list of
words which I want to set equal to tab titles, so when I change the words the
titles update automatically, is this possible?

Like a normal function but for tab titles.

Thanks

The code given by Mike is great.
I would suggest a very small change to it, (use this only if you intend to
change the name of Sheet 1 else just use Mike's code.)

Replace the Line
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
To
If Not Intersect(Target, Sheet1.Range("A1:A10")) Is Nothing Then

Cheers,
--
Tausif Mohammed


"Mike H" wrote:

Hi,

Try this. Alt + f11 to open VB editor. Double Click 'This Workbook' and
paste this in on the right. Enter a name in A1 and provided it's a legal name
Sheet 1 will be changed to that name. A2 for Sheet 2 etc. If you have more
than 10 sheets extend to range A1 - A10.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Sheets(Target.Row).Name = Target.Value
Application.EnableEvents = True
End If
End Sub

Mike

"LiAD" wrote:

Does anyone know of a method of getting tab titles to update automatically to
changes in worksheet contents? i.e. if I have a worksheet with a list of
words which I want to set equal to tab titles, so when I change the words the
titles update automatically, is this possible?

Like a normal function but for tab titles.

Thanks

I wouldn't use either the worksheet's name or its codename. I'd use:

If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then

Me is the thing that owns the code--in this case, it's the worksheet.

Tausif wrote:

The code given by Mike is great.
I would suggest a very small change to it, (use this only if you intend to
change the name of Sheet 1 else just use Mike's code.)

Replace the Line
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
To
If Not Intersect(Target, Sheet1.Range("A1:A10")) Is Nothing Then

Cheers,
--
Tausif Mohammed

"Mike H" wrote:

Hi,

Try this. Alt + f11 to open VB editor. Double Click 'This Workbook' and
paste this in on the right. Enter a name in A1 and provided it's a legal name
Sheet 1 will be changed to that name. A2 for Sheet 2 etc. If you have more
than 10 sheets extend to range A1 - A10.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Sheets(Target.Row).Name = Target.Value
Application.EnableEvents = True
End If
End Sub

Mike

"LiAD" wrote:

Does anyone know of a method of getting tab titles to update automatically to
changes in worksheet contents? i.e. if I have a worksheet with a list of
words which I want to set equal to tab titles, so when I change the words the
titles update automatically, is this possible?

Like a normal function but for tab titles.

Thanks

--

Dave Peterson

hello,
this is something i have been looking for.
when i tried this, only the first sheet tab changes others don't.
any ideas?
--
Cheers !


"Dave Peterson" wrote:

I wouldn't use either the worksheet's name or its codename. I'd use:

If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then

Me is the thing that owns the code--in this case, it's the worksheet.

Tausif wrote:

The code given by Mike is great.
I would suggest a very small change to it, (use this only if you intend to
change the name of Sheet 1 else just use Mike's code.)

Replace the Line
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
To
If Not Intersect(Target, Sheet1.Range("A1:A10")) Is Nothing Then

Cheers,
--
Tausif Mohammed

"Mike H" wrote:

Hi,

Try this. Alt + f11 to open VB editor. Double Click 'This Workbook' and
paste this in on the right. Enter a name in A1 and provided it's a legal name
Sheet 1 will be changed to that name. A2 for Sheet 2 etc. If you have more
than 10 sheets extend to range A1 - A10.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Sheets(Target.Row).Name = Target.Value
Application.EnableEvents = True
End If
End Sub

Mike

"LiAD" wrote:

Does anyone know of a method of getting tab titles to update automatically to
changes in worksheet contents? i.e. if I have a worksheet with a list of
words which I want to set equal to tab titles, so when I change the words the
titles update automatically, is this possible?

Like a normal function but for tab titles.

Thanks

--

Dave Peterson

The code is written to react to a single change in a single cell.

But each time you change a single cell, then the event should fire and a
worksheet named should be changed.

If this doesn't help, post the current code you're using and explain what you're
changing.

Sena wrote:

hello,
this is something i have been looking for.
when i tried this, only the first sheet tab changes others don't.
any ideas?
--
Cheers !

"Dave Peterson" wrote:

I wouldn't use either the worksheet's name or its codename. I'd use:

If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then

Me is the thing that owns the code--in this case, it's the worksheet.

Tausif wrote:

The code given by Mike is great.
I would suggest a very small change to it, (use this only if you intend to
change the name of Sheet 1 else just use Mike's code.)

Replace the Line
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
To
If Not Intersect(Target, Sheet1.Range("A1:A10")) Is Nothing Then

Cheers,
--
Tausif Mohammed

"Mike H" wrote:

Hi,

Try this. Alt + f11 to open VB editor. Double Click 'This Workbook' and
paste this in on the right. Enter a name in A1 and provided it's a legal name
Sheet 1 will be changed to that name. A2 for Sheet 2 etc. If you have more
than 10 sheets extend to range A1 - A10.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Sheets(Target.Row).Name = Target.Value
Application.EnableEvents = True
End If
End Sub

Mike

"LiAD" wrote:

Does anyone know of a method of getting tab titles to update automatically to
changes in worksheet contents? i.e. if I have a worksheet with a list of
words which I want to set equal to tab titles, so when I change the words the
titles update automatically, is this possible?

Like a normal function but for tab titles.

Thanks

--

Dave Peterson


--

Dave Peterson