Dynamicly change spreadsheet tab names depending on cell value
 

Is it possible to change tab names dynamicaly ? For example i have 31 tab (1
for each day of the month)

These tabs are named - 1,2,3 e.t.c
Is it possible to define rule that would ad "!" to day which is weekend day.

In other words is it possible to dynamicly change tab name depending from
value of cell ?

If this is possbile how do i do this ?

Right-click on the spreadsheet tab, select View Code and paste this in:-

Private Sub Worksheet_Activate()
ActiveSheet.Name = Range("A1").Value
End Sub

To test this I put the date in cell B2 and this formula into A1:-

=TEXT(DAY(B1),"d")&IF(OR(WEEKDAY(B1)=7,WEEKDAY(B1) =1),"!","")

"Mikus" wrote:

Is it possible to change tab names dynamicaly ? For example i have 31 tab (1
for each day of the month)

These tabs are named - 1,2,3 e.t.c
Is it possible to define rule that would ad "!" to day which is weekend day.

In other words is it possible to dynamicly change tab name depending from
value of cell ?

If this is possbile how do i do this ?

Take a look he

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


In article ,
"Mikus" wrote:

Is it possible to change tab names dynamicaly ? For example i have 31 tab (1
for each day of the month)

These tabs are named - 1,2,3 e.t.c
Is it possible to define rule that would ad "!" to day which is weekend day.

In other words is it possible to dynamicly change tab name depending from
value of cell ?

If this is possbile how do i do this ?

You could add this routine to a module in the workbook:

Sub Auto_Open()
--For Each xSheet in ThisWorkbook.Worksheets
----nDay = Val(xSheet.Name) 'Use number in sheet tab to represent date
----dDate = DateSerial(Year(Now()),Month(Now()),nDay) 'Work out full date
----tDay = Format(dDate,"ddd") 'Work out which day it is
----'If day is SAT or SUN, add exclamation point
----If Left(tDay,1)="S" Then
------xSheet.Name = Format(nDay,"0") & "!"
----Else
------xSheet.Name = Format(nDay,"0")
----End If
--Next xSheet
End Sub

This will add an exclamation point symbol to any sheet tab representing a date which is a weekend day.

However, you could simply replace the 'If-Else-End If' section with:

----xSheet.Name = Format(dDate,"dd ddd")

And this will name your sheets:
'01 MON', '02 TUE', '03 WED', etc.

Hope this helps.
BizMark

Dynamicly change spreadsheet tab names depending on cell value
 

bigwheel .....

This was exactly what I was looking for & it works fine with one relatively
minor oddity (to me anyway since I'm not a programmer). When I change the
value in the target worksheet cell, the worksheet tab name does not change
until I click on another worksheet tab, then click back on the tab for the
worksheet whose tab name I was trying to change. How do you make the tab
name change as soon as you update the target cell ? It's not a showstopper,
but it would be less confusing if this could be fixed.

Thanks !!!
--
HOOS77


"bigwheel" wrote:

Right-click on the spreadsheet tab, select View Code and paste this in:-

Private Sub Worksheet_Activate()
ActiveSheet.Name = Range("A1").Value
End Sub

To test this I put the date in cell B2 and this formula into A1:-

=TEXT(DAY(B1),"d")&IF(OR(WEEKDAY(B1)=7,WEEKDAY(B1) =1),"!","")

"Mikus" wrote:

Is it possible to change tab names dynamicaly ? For example i have 31 tab (1
for each day of the month)

These tabs are named - 1,2,3 e.t.c
Is it possible to define rule that would ad "!" to day which is weekend day.

In other words is it possible to dynamicly change tab name depending from
value of cell ?

If this is possbile how do i do this ?