Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 ? |
#2
|
|||
|
|||
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 ? |
#3
|
|||
|
|||
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 ? |
#4
|
|||
|
|||
Quote:
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I change the a,b,c, column headers in excel to names | Excel Discussion (Misc queries) | |||
How do I change row names from A to something else in Excel? | Excel Discussion (Misc queries) | |||
how do you change pathways (links) when consolidating spreadsheet. | Excel Discussion (Misc queries) | |||
How do I change a spreadsheet from English to Spanish | New Users to Excel | |||
How do I change a spreadsheet from all caps to "Proper" | Excel Discussion (Misc queries) |