Change name of sheet based on cell formula...

February 11, 2011 at 02:48:25
Specs: Windows 7
Hi I have two workbooks. The first one has a range of cells with months formatted as 'mmm'. On the second work book I reference the first workbook e.g =A1 and the month appears in the second workbook Call A1 as 40633 until I re-format that cell as a month. The tab name refelcts the same name (40633) but will not make any changes once I haver reformatted cell A1. I ahve tried formatting the cell forst before linking the two celss but it does not help at all.
Is there any advice you can offer me?
I am using the following code.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
Me.Name = Me.Range("a1").Value
If Err.Number <> 0 Then
MsgBox "cannot rename sheet to this name"
Err.Clear
End If
On Error GoTo 0

End Sub

Many thanks


See More: Change name of sheet based on cell formula...

Report •

#1
February 11, 2011 at 04:41:48
The formatting of a cell is not considered a "change" by VBA.

Try something like:

Me.Name = Month(Range("A1")) & "-" & Day(Range("A1")) & "-" & Year(Range("A1"))

Edit:

(Not tested)

Tested briefly, seems to work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 12, 2011 at 07:06:58
Hi,
Many thanks for your prompt reply. I have done as you suggested however the tab is being renamed in this format "5-30-2011".
I have tried various formatting options for cell A1 however the tab name format remains the same although it does change to reflect the month . e.g the tab name of "5-30-2011" for May changes to "6-1-2011" when June is in cell A1. I want the tabs to reflect the month and year such as "Mar 11".
Hope you can help.
best regards

Report •

#3
February 12, 2011 at 08:50:15
re: "I have tried various formatting options for cell A1"

As I hinted at in my previous response, the cell formatting has no impact on how VBA interprets the contents of the cell. You could change the format in a cell all day long but unless you format the string directly within VBA you'll never get the format you want in the sheet tab.

Look at what you are doing in your code:

Me.Name = Me.Range("a1").Value

You are using the value of the cell to create a string that VBA uses to set the sheet name. There's nothing related to the formatting of A1 involved.

Look at what I did in my code:

Me.Name = Month(Range("A1")) & "-" & Day(Range("A1")) & "-" & Year(Range("A1"))

I am using the value of A1 to build a string that VBA uses to set the sheet name. Once again, there's nothing related to the formatting of A1 involved.

In neither case is any formatting of the string that VBA will use for to sheet name done.

If you want a specific format for the sheet tab, you need to format the string within VBA.

Me.Name = Format(Month(Range("A1")), "mmm") & " " _
          & Format(Year(Range("A1")), "yy")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 12, 2011 at 09:08:12
Many thanks DAerbyDad03,
I appreciate your assistance. Am a total newbie at VB etc and only learning to get a business plan finished.
I have followed the above and the tabs are now renaming to Jan 05.. :)
Maybe I should just do it the long way around.. :)
Thanks once again
Garwil

Report •

#5
February 12, 2011 at 09:18:55
Try this:

Me.Name = Format(Month(Range("A1")), "mmm") & " " _
          & Right(Year(Range("A1")), 2)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 12, 2011 at 09:34:41
The tab is remaIning on Jan regardless of what is in cell A1. The year was only changing in ranges from Jan 00 to Jan 99 as the months in cell A1 changed and corrected whe I enterred the mmm and yy in cell A1 (instead of linking to the other cell in the other workbook). The year on the tab is now correctly reflecting but the Month name stays on Jan

Report •

#7
February 12, 2011 at 10:40:03
Tried that and now althought the year changes the Month remains as "Jan" regardles of what is in cell A1.
regards

Report •

#8
February 12, 2011 at 11:54:45
Sorry, my error.

I didn't realize that the Format command in VBA worked on the entire date. I was asking it to work on just the Month and that was wrong - and I didn't test it to any great extent so I didn't see the problem.

This seems to work:

Me.Name = Format(Range("A1"), "mmm yy")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
February 12, 2011 at 12:05:22
Yohoooo!!! Absolutley wonderful...
Thanks a ton for giving up your time to help. Works like a charm!
Much appreciated

Report •

#10
February 12, 2011 at 12:09:23
Yeah, well, sorry for all the wrong answers...that's why I usually spend more time testing my suggestions. I got lazy.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
February 12, 2011 at 12:14:14
lol... Sorry, just one last question if you don't mind.. how can I get that code to run across a whole year of 12 tabs without having to set it up for each tab?

Report •

Ask Question