Tab naming from a Reference cell

  • Thread starter Thread starter Finance Guru
  • Start date Start date
F

Finance Guru

Hi All,

Using Excel 2007

Can I name a worksheet tab from a cell contained within the worksheet. I
want to name a worksheet from the date contained in say A1 = 30/06/2009
(dd/mm/yyyy)
so the tab would be called 'June' from the month number in A1.

If so could someone give me either the function or the code to do this. If
it is code could also detail how to apply it, as I am not that familiar with
code.

Many thanks for taking the time out to respond.

FinanceGuru
 
Try the below macro which renames the activesheet to the month of date in A1

Sub Macro1()
Activesheet.name = Format(Range("A1"),"mmmm")
End Sub

If this post helps click Yes
 
Hi Jacob - That didn't work. Any other ideas.
I put the code into the tab - right clicked the tab>view code>paste .. and
entered 30/06/09 in A1 .. and the Tab name never changed

Thanks
Finance Guru
 
This is not to be pasted into the sheet tab..

Set the Security level to low/medium in (Tools|Macro|Security). From
workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and
paste the code. Save. Get back to Workbook. Run macro from Tools|Macro|Run
<selected macro()>


If this post helps click Yes
 
If you want this to change everytime you change cell A1; then try the below
code; which can be pasted to the SheetTab>View Code window...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = False
Me.Name = Format(Range("A1"), "mmmm")
Application.EnableEvents = True
End If
End Sub

If this post helps click Yes
 
Hi Jacob - The second option is what I wanted.

Did as you said but when I typed in 30/06/2009 in A1 nothing happened to the
tab name. The format of A1 is date. I am not sure whether this has anything
to do with it or not.

Thanks - FinanceGuru
 
Hello,

Jacob's code worked for me. The code must be run after the date is entered
in A1. In xl2007 the file must be macro enabled. To learn more about macro
security settings see:
http://office.microsoft.com/en-us/excel/HP100969191033.aspx
To run the macro press [F8] or go to View > Macros > View Macros. Select
your macro and click [Run].
To learn more about getting started with macros see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
If it still doesn't work, make sure that the entry in A1 is actually a date
code and not just text that looks like a date.
Hope this helps.

Dave
 
Check whether security level to low/medium in (Tools|Macro|Security). Close
application and reopen and try with the below code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If IsDate(Range("A1")) = True Then
Me.Name = Format(Range("A1"), "mmmm")
End If
End If
End Sub

If this post helps click Yes
 
Thanks Guys. Unfortunately all your offerings didn't work for me.



Thanks for all you help anyways
 
Hi Jacob & Bassman62

I must just having one of those days today. My mind is winding down for hols
next week.

Jacob I got your code to work fine, even altered the macro to bring the last
2 digits of the year. So thank you for that.

As a matter of interest how would alter the macro to ensure that tab was
filled with the month and the full year ( 2009 ) eg. July2009. I added 4
yyyy to the macro but it messed the year up to something like 2743

Have a great weekend guys. Thanks again
Finance Guru
 
Me.Name = Format(Range("A1"), "mmmmyyyy")
OR
Me.Name = Format(Range("A1"), "mmmyyyy")

If this post helps click Yes
 
Back
Top