Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
michaelspearin
 
Posts: n/a
Default How do I automatically rename a sheet with the contents of a cell.


  #2   Report Post  
Norman Jones
 
Posts: n/a
Default

Hi Michael,

Right-Click the worksheet tab, select the 'View Code' option and paste the
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
Cleanup:
Application.EnableEvents = True
End Sub

Change A1 to the required cell reference,

---
Regards,
Norman



"michaelspearin" wrote in message
...



  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

There's a minor gotcha in this code. By using Me.Name = Target.Value,
rather than the cell reference, an error will occur if multiple cells
are selected (since Name can't be assigned an array).

A variation of this, with some additional error checking, can be seen at

http://www.mcgimpsey.com/excel/event...efromcell.html



In article ,
"Norman Jones" wrote:

Hi Michael,

Right-Click the worksheet tab, select the 'View Code' option and paste the
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
Cleanup:
Application.EnableEvents = True
End Sub

Change A1 to the required cell reference,

---
Regards,
Norman

  #4   Report Post  
Norman Jones
 
Posts: n/a
Default

Hi JE,

You are correct and, in any case, I prefer your version.

Thank you.

---
Regards,
Norman



"JE McGimpsey" wrote in message
...
There's a minor gotcha in this code. By using Me.Name = Target.Value,
rather than the cell reference, an error will occur if multiple cells
are selected (since Name can't be assigned an array).

A variation of this, with some additional error checking, can be seen at

http://www.mcgimpsey.com/excel/event...efromcell.html



In article ,
"Norman Jones" wrote:

Hi Michael,

Right-Click the worksheet tab, select the 'View Code' option and paste
the
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
Cleanup:
Application.EnableEvents = True
End Sub

Change A1 to the required cell reference,

---
Regards,
Norman



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
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Linking to an Entire Sheet Chaplain Doug Links and Linking in Excel 2 December 24th 04 08:39 AM
Hyperlink to specific sheet in Excel Web File jd17 Links and Linking in Excel 0 December 8th 04 10:03 PM


All times are GMT +1. The time now is 06:13 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"