Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 16th 05, 05:15 PM
Karen
 
Posts: n/a
Default Auto Update Sheet Tab

I have a date in cell D1 and (of course) it changes every day. Is there a
way of auto updating the sheet tab name when the date changes in cell D1?

Thank you, Karen

  #2   Report Post  
Old May 16th 05, 06:46 PM
Jason Morin
 
Posts: n/a
Default

Right-click on the worksheet tab, select View Code, and paste in the
following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[D1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA


"Karen" wrote:

I have a date in cell D1 and (of course) it changes every day. Is there a
way of auto updating the sheet tab name when the date changes in cell D1?

Thank you, Karen

  #3   Report Post  
Old May 16th 05, 07:47 PM
Karen
 
Posts: n/a
Default

Thank you for your help - I pasted the code as you mentioned and the sheet
tab is not changing. I exited the file and reopened it and nothing happened.
Am I doing something wrong?

"Karen" wrote:

I have a date in cell D1 and (of course) it changes every day. Is there a
way of auto updating the sheet tab name when the date changes in cell D1?

Thank you, Karen

  #4   Report Post  
Old May 16th 05, 08:11 PM
Jason Morin
 
Posts: n/a
Default

Make sure you select "Enable Macros" upon opening your file. I would also
check Tools Options Security tab and click the "Macro Security" button.
Make sure it is not set to "High."

What I gave was some code I posted recently. I should have tested it before
giving it to you. Replace the code I originally posted with this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[D1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Format(Target.Value, "mm-dd-yy")
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
HTH
Jason
Atlanta, GA




"Karen" wrote:

Thank you for your help - I pasted the code as you mentioned and the sheet
tab is not changing. I exited the file and reopened it and nothing happened.
Am I doing something wrong?

"Karen" wrote:

I have a date in cell D1 and (of course) it changes every day. Is there a
way of auto updating the sheet tab name when the date changes in cell D1?

Thank you, Karen



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
Entering Data in multiple cells on one sheet & having it auto upda haynblend Excel Worksheet Functions 2 March 27th 05 12:41 AM
excel links update not working in auto, calculations in auto Mikey Boy Excel Worksheet Functions 0 December 7th 04 11:53 PM
auto update an open excel sheet over network, with mutiple users crumkerr Excel Worksheet Functions 1 November 29th 04 11:47 AM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 05:57 PM
Why can't my macro use Auto Filter when I told the Sheet Protecti. KC Rippstein Excel Worksheet Functions 1 October 28th 04 06:13 PM


All times are GMT +1. The time now is 10:15 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017