T
Tenaj
Can I link the worksheet tab name to a cell so that it automatically updates
the tab name accordingly ?
the tab name accordingly ?
J
JKHouston
this worked for me, thanks!
one thing that happens is if I type in a name that already exists on another
worksheet, it crashes to the debugger. Is there any way to prevent this?
thanks again!
one thing that happens is if I type in a name that already exists on another
worksheet, it crashes to the debugger. Is there any way to prevent this?
thanks again!
D
Dave Peterson
I'd use
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
on error resume next
sh.Name = sh.Range("a2").Value
if err.number <> 0 then
msgbox "Rename failed"
err.clear
end if
on error goto 0
End Sub
But I'm not sure I'd do this each time the selection changed. If A2 was
changing because of the user typing something in that cell, I'd use the
workbook_sheetchange event. If it changed because of a calculation, I'd use the
Workbook_SheetCalculate event.
Kind of like one of these -- and only use one!
Option Explicit
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = False
On Error Resume Next
Sh.Name = Sh.Range("a2").Value
If Err.Number <> 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("a2")) Is Nothing Then
Exit Sub
End If
On Error Resume Next
Sh.Name = Sh.Range("a2").Value
If Err.Number <> 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
on error resume next
sh.Name = sh.Range("a2").Value
if err.number <> 0 then
msgbox "Rename failed"
err.clear
end if
on error goto 0
End Sub
But I'm not sure I'd do this each time the selection changed. If A2 was
changing because of the user typing something in that cell, I'd use the
workbook_sheetchange event. If it changed because of a calculation, I'd use the
Workbook_SheetCalculate event.
Kind of like one of these -- and only use one!
Option Explicit
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = False
On Error Resume Next
Sh.Name = Sh.Range("a2").Value
If Err.Number <> 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("a2")) Is Nothing Then
Exit Sub
End If
On Error Resume Next
Sh.Name = Sh.Range("a2").Value
If Err.Number <> 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
End Sub
J
jkhouston
Thanks very much for your quick response. I agree that it should only query
when there is a change to the cell. Thanks for the error handler as well,
nice job.
Regards,
John
when there is a change to the cell. Thanks for the error handler as well,
nice job.
Regards,
John
I
i2meek
I wish to use this but have never used the vba editor. I can get to where
the program goes, but I am not sure what part of the formula needs to be
replaced with the actual workbook, worksheet, cell locations. For instance
in my workbook named "Bk Stats Master_Forms", I have a worksheet named
"Schedule". In the "Schedule" worksheet I have listed team names in column
E. I want to link these team names to name the worksheet tabs. For instance
I want one worksheet tab name to be linked from "Schedule" worksheet cell
"E6", then the next worksheet name to be linked from "Schedule" "E7" etc.
What parts of the module are replaced with the actual location/destination?
Thanks
the program goes, but I am not sure what part of the formula needs to be
replaced with the actual workbook, worksheet, cell locations. For instance
in my workbook named "Bk Stats Master_Forms", I have a worksheet named
"Schedule". In the "Schedule" worksheet I have listed team names in column
E. I want to link these team names to name the worksheet tabs. For instance
I want one worksheet tab name to be linked from "Schedule" worksheet cell
"E6", then the next worksheet name to be linked from "Schedule" "E7" etc.
What parts of the module are replaced with the actual location/destination?
Thanks
D
Don Guillett
Place in a macro module and fire
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Assumes you want to add 4 sheets with desired names in cells e6,e7,e8,e9
Sub addshtsandname()
For i = 1 To 4
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheets("schedule").Cells(i+5, 5)
Next i
End Sub
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Assumes you want to add 4 sheets with desired names in cells e6,e7,e8,e9
Sub addshtsandname()
For i = 1 To 4
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheets("schedule").Cells(i+5, 5)
Next i
End Sub
I
i2meek
1. I re-posted because someone misled me by saying my post was in reply to
such an old post i might not get an answer. I am very sorry this wasted your
time and angered you so.
2. I had asked how to use the "code" or whatever you wish to call it that I
provided. Is it not useable? If not please let me know. I just would like to
know where to put the actual locations into that "code" "formula" whatever.
3. By not answering what I asked and assuming something else is also wasting
time. My worksheets are already created and filled with data/formulas/links
etc. So assuming I want to add sheets is wrong. Letting me know if and how
to use the code I provided is helpful. Providing a better way to rename my
EXISTING worksheets with the names from the cell locations I stated would be
outstanding.
Again I'm sorry and am in desparate need of quickly finishing an overdue
basketballstats project
such an old post i might not get an answer. I am very sorry this wasted your
time and angered you so.
2. I had asked how to use the "code" or whatever you wish to call it that I
provided. Is it not useable? If not please let me know. I just would like to
know where to put the actual locations into that "code" "formula" whatever.
3. By not answering what I asked and assuming something else is also wasting
time. My worksheets are already created and filled with data/formulas/links
etc. So assuming I want to add sheets is wrong. Letting me know if and how
to use the code I provided is helpful. Providing a better way to rename my
EXISTING worksheets with the names from the cell locations I stated would be
outstanding.
Again I'm sorry and am in desparate need of quickly finishing an overdue
basketballstats project
D
Don Guillett
You're welcome.
I
i2meek
I'm welcome? Is it not possible to perform the task I asked?
D
Don Guillett
Of course, it is very easily done. However, it is always nice to state your
request in your original post and even nicer not to berate the people who
may want to help you. Perhaps someone else will assist????I don't recall you stating anything about existing sheets or what their
names might have been. Maybe I missed it.
request in your original post and even nicer not to berate the people who
may want to help you. Perhaps someone else will assist????I don't recall you stating anything about existing sheets or what their
names might have been. Maybe I missed it.
I
i2meek
You wrongly, unjusitifiably jumped me, assuming I was intentionally posting
several places - for which I took the high road and explained what I did and
still apologized.
Then you didn't answer the question as asked.
I didn't state I was creating new worksheets - you assumed.
You want to be taken literally but not take others as such? that's
hypocritical.
I stated my request - you assumed otherwise and in no way was my intention
to berate(when you assUme . . .) As a person wit a 20 year career of helping
others it is obvious to me you do not have the temperament to help others.
It's obvious you have no solution for my question.
Maybe it can't be done.
several places - for which I took the high road and explained what I did and
still apologized.
Then you didn't answer the question as asked.
I didn't state I was creating new worksheets - you assumed.
You want to be taken literally but not take others as such? that's
hypocritical.
I stated my request - you assumed otherwise and in no way was my intention
to berate(when you assUme . . .) As a person wit a 20 year career of helping
others it is obvious to me you do not have the temperament to help others.
It's obvious you have no solution for my question.
Maybe it can't be done.
D
Don Guillett
And we STILL know nothing about your existing sheet names.Of course, it is easily done. In fact, you should be able to figure it out
from what is provided.
from what is provided.
S
Sandy Mann
The code that you posted does not work for me. A slight alteration does
however:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveSheet.Name = Sheets("Schedule").Range("E6").Value Then Exit Sub
ActiveSheet.Name = Sheets("Schedule").Range("E6").Value
End Sub
Right-click on the sheet tab for the sheet relating to the name in E6 of the
"Schedule" sheet and select View Code. Ensure that you are in the sheet
module and that it says "Worksheet" and not "General" in the top left
window. If the right window does not say "SelectionChange" click on the
arrowhead and select it.
I would delete eveything in the module, (should be an empty
Worksheet_SelectionChange Macro), copy and paste the above code into the
module.
For the other sheets paste the code into the Worksheet module of that sheet
and change the Range("E6") reference to the correct cell.
The sheet name should change when you select any cell in the worksheet.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
[email protected]
Replace @mailinator.com with @tiscali.co.uk
however:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveSheet.Name = Sheets("Schedule").Range("E6").Value Then Exit Sub
ActiveSheet.Name = Sheets("Schedule").Range("E6").Value
End Sub
Right-click on the sheet tab for the sheet relating to the name in E6 of the
"Schedule" sheet and select View Code. Ensure that you are in the sheet
module and that it says "Worksheet" and not "General" in the top left
window. If the right window does not say "SelectionChange" click on the
arrowhead and select it.
I would delete eveything in the module, (should be an empty
Worksheet_SelectionChange Macro), copy and paste the above code into the
module.
For the other sheets paste the code into the Worksheet module of that sheet
and change the Range("E6") reference to the correct cell.
The sheet name should change when you select any cell in the worksheet.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
[email protected]
Replace @mailinator.com with @tiscali.co.uk
I
i2meek
Finally was able to try it - Did exactly as you said: copied, pasted, clicked
in a cell and it worked perfectly!!! Thank You very much. Your expertise is
greatly appreciated.
in a cell and it worked perfectly!!! Thank You very much. Your expertise is
greatly appreciated.
S
Sandy Mann
I'm glad that it worked for you. Thanks for the feedback telling us that it
worked.
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
[email protected]
Replace @mailinator.com with @tiscali.co.uk
worked.
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
[email protected]
Replace @mailinator.com with @tiscali.co.uk