Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 3rd 04, 08:41 PM
michaelspearin
 
Posts: n/a
Default How do I automatically rename a sheet with the contents of a cell.



  #2   Report Post  
Old December 3rd 04, 09:03 PM
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  
Old December 3rd 04, 09:50 PM
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  
Old December 3rd 04, 10:27 PM
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
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
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 1 December 2nd 04 09:30 PM


All times are GMT +1. The time now is 04:16 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017