Hi Dave,
What do i need to change to your code to update the sheet name automatically when the reference cell A1 is a formula ?
Thanks
Nicolas
Dave Peterson wrote:
So you want to be able to go to any sheet and change A1 and then see
02-Aug-08
So you want to be able to go to any sheet and change A1 and then see tha
worksheet name change
If yes, then I wouldn't use the code you have. Delete it and try this workboo
event instead (Workbook events go under the ThisWorkbook module)
Option Explici
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range
'one cell at a tim
If Target.Cells.Count > 1 Then Exit Su
If Intersect(Target, Sh.Range("A1")) Is Nothing The
Exit Sub 'not in A
End I
On Error Resume Next 'just in case it's not a valid nam
Sh.Name = Sh.Range("a1").Valu
If Err.Number <> 0 The
MsgBox Sh.Name & " cannot be renamed to: " & Target.Valu
Err.Clea
End I
On Error GoTo
End Su
Sena wrote
--
Dave Peterson
Previous Posts In This Thread:
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
Hi,Try this. Alt + f11 to open VB editor.
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 Su
On Error Resume Nex
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing The
Application.EnableEvents = Fals
Sheets(Target.Row).Name = Target.Valu
Application.EnableEvents = Tru
End I
End Su
Mik
:
Hi,Forget that. Right click a sheet tab, View code and paste this in there.
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 Su
On Error Resume Nex
If Not Intersect(Target, Range("A1:A10")) Is Nothing The
Application.EnableEvents = Fals
Sheets(Target.Row).Name = Target.Valu
Application.EnableEvents = Tru
End I
End Su
Mik
:
The code given by Mike is great.
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 Lin
If Not Intersect(Target, Sheets("Sheet1").Range("A1:A10")) Is Nothing The
T
If Not Intersect(Target, Sheet1.Range("A1:A10")) Is Nothing The
Cheers
--
Tausif Mohamme
:
I wouldn't use either the worksheet's name or its codename.
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 The
Me is the thing that owns the code--in this case, it's the worksheet
Tausif wrote
--
Dave Peterson
hello,this is something i have been looking for.
hello
this is something i have been looking for
when i tried this, only the first sheet tab changes others do not
any ideas
-
Cheers
:
The code is written to react to a single change in a single cell.
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:
--
Dave Peterson
Hi, here's the code I've used.
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 !
:
So you want to be able to go to any sheet and change A1 and then see
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:
--
Dave Peterson
Thanks Dave, this exactly what I'm looking for.
Thanks Dave, this exactly what I am looking for.
How do I use it as a workbook event? VB code automatically assigns to sheet.
Pls let me know
--
Cheers !
:
The code will go in the ThisWorkbook module. Not under each worksheet.
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:
--
Dave Peterson
Re: Automatic tab titles
Thanks a lot !
It works well
--
Cheers !
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Six Free Visual Studio 2010 MSDN Memberships Giveaway
http://www.eggheadcafe.com/tutorial...al-studio-2010-msdn-memberships-giveaway.aspx