| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic tab titles
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
The code will go in the ThisWorkbook module. Not under each worksheet.
You'll want to remove any previous code you've tried in those worksheet modules. Then inside the VBE, you can hit ctrl-r to see the project explorer Then you'll see something that looks like windows explorer (usually on the left side). Expand the projects so that you can see your workbook (click on those +'s in the little boxes). You'll see the ThisWorkbook module. double click on that and paste the code into the right hand code window that just opened. See Debra Dalgleish's site for some instructions with pictures: http://www.contextures.com/xlvba01.html#Workbook Sena wrote: 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 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatic tab titles
Thanks a lot !
It works well -- Cheers ! "Dave Peterson" wrote: The code will go in the ThisWorkbook module. Not under each worksheet. You'll want to remove any previous code you've tried in those worksheet modules. Then inside the VBE, you can hit ctrl-r to see the project explorer Then you'll see something that looks like windows explorer (usually on the left side). Expand the projects so that you can see your workbook (click on those +'s in the little boxes). You'll see the ThisWorkbook module. double click on that and paste the code into the right hand code window that just opened. See Debra Dalgleish's site for some instructions with pictures: http://www.contextures.com/xlvba01.html#Workbook Sena wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Y Axis Titles (Excel is clipping titles) | Excel Discussion (Misc queries) | |||
| Automatic Axis Titles in Excel 2007 | Charts and Charting in Excel | |||
| Column and Row Titles | Excel Worksheet Functions | |||
| Titles | Excel Discussion (Misc queries) | |||
| Column Titles | Setting up and Configuration of Excel | |||
Linear Mode
