Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Rename Sheet using Cell Reference

Is is possible to automatically rename a worksheet based on the contents of a
cell? For Example:

Cell A1 contains Bob Smith - I'd like the worksheet to automatically name
itself Bob Smith, even if the contents of A1 change.

I've done a search of the discussions here and found:

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

The above example however works in reverse from what I'm looking for and
copies the worksheet name to the cell. What I'd like to accomplish is
copying the cell to the worksheet name if possible.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default Rename Sheet using Cell Reference

Right click the sheet and select view code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = Target.Value
End If
End Sub


--
Regards,

Peo Sjoblom


"RDana" wrote in message
...
Is is possible to automatically rename a worksheet based on the contents
of a
cell? For Example:

Cell A1 contains Bob Smith - I'd like the worksheet to automatically name
itself Bob Smith, even if the contents of A1 change.

I've done a search of the discussions here and found:

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

The above example however works in reverse from what I'm looking for and
copies the worksheet name to the cell. What I'd like to accomplish is
copying the cell to the worksheet name if possible.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Rename Sheet using Cell Reference

Instead of using Activesheet, I'd use the sheet that owns the code. Activesheet
may not cause any trouble--until a macro runs that changes A1 on that sheet--and
that sheet isn't active.

In fact, I'd add a little checking (and some completely arbitrary changes,
<bg):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time
if target.cells.count 1 then exit sub

If Intersect(Target, Me.Range("A1")) Is Nothing Then
exit sub 'nots and nothings confuse me!
else
on error resume next
Me.Name = Target.Value
if err.number < 0 then
beep 'or msgbox "Was not renamed!"
err.clear
end if
on error goto 0
End If

End Sub

======
To the OP:
Remember that this event will fire when you're typing a new value--not the
result of a calculation.

And if you're typing in dates, you'll want to format the date to avoid invalid
worksheet names. Something like:

Me.Name = Format(Target.Value, "yyyymmdd")



Peo Sjoblom wrote:

Right click the sheet and select view code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = Target.Value
End If
End Sub

--
Regards,

Peo Sjoblom

"RDana" wrote in message
...
Is is possible to automatically rename a worksheet based on the contents
of a
cell? For Example:

Cell A1 contains Bob Smith - I'd like the worksheet to automatically name
itself Bob Smith, even if the contents of A1 change.

I've done a search of the discussions here and found:

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

The above example however works in reverse from what I'm looking for and
copies the worksheet name to the cell. What I'd like to accomplish is
copying the cell to the worksheet name if possible.


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default Rename Sheet using Cell Reference

You are such a nanny! <bg

Peo

"Dave Peterson" wrote in message
...
Instead of using Activesheet, I'd use the sheet that owns the code.
Activesheet
may not cause any trouble--until a macro runs that changes A1 on that
sheet--and
that sheet isn't active.

In fact, I'd add a little checking (and some completely arbitrary changes,
<bg):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time
if target.cells.count 1 then exit sub

If Intersect(Target, Me.Range("A1")) Is Nothing Then
exit sub 'nots and nothings confuse me!
else
on error resume next
Me.Name = Target.Value
if err.number < 0 then
beep 'or msgbox "Was not renamed!"
err.clear
end if
on error goto 0
End If

End Sub

======
To the OP:
Remember that this event will fire when you're typing a new value--not the
result of a calculation.

And if you're typing in dates, you'll want to format the date to avoid
invalid
worksheet names. Something like:

Me.Name = Format(Target.Value, "yyyymmdd")



Peo Sjoblom wrote:

Right click the sheet and select view code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = Target.Value
End If
End Sub

--
Regards,

Peo Sjoblom

"RDana" wrote in message
...
Is is possible to automatically rename a worksheet based on the
contents
of a
cell? For Example:

Cell A1 contains Bob Smith - I'd like the worksheet to automatically
name
itself Bob Smith, even if the contents of A1 change.

I've done a search of the discussions here and found:

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

The above example however works in reverse from what I'm looking for
and
copies the worksheet name to the cell. What I'd like to accomplish is
copying the cell to the worksheet name if possible.


--

Dave Peterson



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Rename Sheet using Cell Reference

I take it that nanny is something very nice in that Swedish language that you
speak!

It must mean something totally different than the English word that looks the
same.

<hehehe


Peo Sjoblom wrote:

You are such a nanny! <bg

Peo

"Dave Peterson" wrote in message
...
Instead of using Activesheet, I'd use the sheet that owns the code.
Activesheet
may not cause any trouble--until a macro runs that changes A1 on that
sheet--and
that sheet isn't active.

In fact, I'd add a little checking (and some completely arbitrary changes,
<bg):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time
if target.cells.count 1 then exit sub

If Intersect(Target, Me.Range("A1")) Is Nothing Then
exit sub 'nots and nothings confuse me!
else
on error resume next
Me.Name = Target.Value
if err.number < 0 then
beep 'or msgbox "Was not renamed!"
err.clear
end if
on error goto 0
End If

End Sub

======
To the OP:
Remember that this event will fire when you're typing a new value--not the
result of a calculation.

And if you're typing in dates, you'll want to format the date to avoid
invalid
worksheet names. Something like:

Me.Name = Format(Target.Value, "yyyymmdd")



Peo Sjoblom wrote:

Right click the sheet and select view code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = Target.Value
End If
End Sub

--
Regards,

Peo Sjoblom

"RDana" wrote in message
...
Is is possible to automatically rename a worksheet based on the
contents
of a
cell? For Example:

Cell A1 contains Bob Smith - I'd like the worksheet to automatically
name
itself Bob Smith, even if the contents of A1 change.

I've done a search of the discussions here and found:

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

The above example however works in reverse from what I'm looking for
and
copies the worksheet name to the cell. What I'd like to accomplish is
copying the cell to the worksheet name if possible.


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting a sheet name reference within a cell reference??? Broyston Excel Discussion (Misc queries) 9 July 8th 08 08:35 PM
Move data to new sheet - rename sheet based on criteria ? [email protected] Excel Discussion (Misc queries) 7 May 16th 07 10:22 PM
Insert sheet, move to end, rename with cell data. [email protected] Excel Discussion (Misc queries) 2 October 11th 05 03:04 PM
how do I rename a sheet jonwyn New Users to Excel 2 March 1st 05 09:36 PM
How do I automatically rename a sheet with the contents of a cell. michaelspearin Excel Discussion (Misc queries) 3 December 3rd 04 10:27 PM


All times are GMT +1. The time now is 01:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"