Thanks Dave, this exactly what I'm looking for.

How do I use it as a workbook event? VB code automatically assigns to sheet.

Pls let me know
--
Cheers !


"Dave Peterson" wrote:

So you want to be able to go to any sheet and change A1 and then see that
worksheet name change?

If yes, then I wouldn't use the code you have. Delete it and try this workbook
event instead (Workbook events go under the ThisWorkbook module).

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'one cell at a time
If Target.Cells.Count 1 Then Exit Sub

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

On Error Resume Next 'just in case it's not a valid name
Sh.Name = Sh.Range("a1").Value
If Err.Number < 0 Then
MsgBox Sh.Name & " cannot be renamed to: " & Target.Value
Err.Clear
End If
On Error GoTo 0

End Sub


Sena wrote:

Hi, here's the code I've used.
What I am looking for is like where cell A1=tab name in all sheets.

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, Sheet1.Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Sheets(Target.Row).Name = Target.Value
Application.EnableEvents = True
End If
End Sub

--
Cheers !

"Dave Peterson" wrote:

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


--

Dave Peterson