Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
I have a spreadsheet that has a calculated date field in "=H34+3"
I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
Oh, and I like the date in the TAB to be formatted, like 6/4/07.
Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
One way:
Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "H35" 'Change to suit On Error Resume Next Me.Name = Range(sCELLADDRESS).Text On Error GoTo 0 End Sub In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
Using my suggested solution, instead of
Me.Name = Range(sCELLADDRESS).Text you could use Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") or some other format. Note that you can't use slashes in tab names. In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: Oh, and I like the date in the TAB to be formatted, like 6/4/07. Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
I think you will find TABs cannot be formatted as you requi try renaming
one. "Kelvin Beaton" wrote: Oh, and I like the date in the TAB to be formatted, like 6/4/07. Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
Tabs can not be formated with any of these characters (\/:*?<|) it .
So 6/1/07 will not work You can use VBA to change the tab name to = 6107 0r 060107 or Mar 06,07 and so on "Kelvin Beaton" wrote: Oh, and I like the date in the TAB to be formatted, like 6/4/07. Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
Well i think the code would be soming like
Sheets("Sheet1").Name = activesheet.cells(rowno, colno).value & Format(somedate, "d/m/yy") Stick under the desired event and simmer gently for 30 mins hth |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
Ok, non of the sugestions seem to work...
I have a calacualted field in D5 on a data... what is supposed to fire this off? I put this code in Sheet3 and the sheet TAB still says Sheet3 When I open the spreadsheet it asked me if I want to enable Macros, and I say yes... I seem to be missing something in what I'm doing... I right mouse clicked on the tab and choose View Code and pasted it there. I pretty sure that's what I was supposed to do... Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "D5" 'Change to suit On Error Resume Next Me.Name = Range(sCELLADDRESS).Text On Error GoTo 0 End Sub Can you think of anything I'm doing wrong? Thanks Kelvin PS sorry, I do want the format of the date to be 2-2-07... "JE McGimpsey" wrote in message ... One way: Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "H35" 'Change to suit On Error Resume Next Me.Name = Range(sCELLADDRESS).Text On Error GoTo 0 End Sub In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
Works fine for me: trying entering data in any cell.
"Kelvin Beaton" wrote: Ok, non of the sugestions seem to work... I have a calacualted field in D5 on a data... what is supposed to fire this off? I put this code in Sheet3 and the sheet TAB still says Sheet3 When I open the spreadsheet it asked me if I want to enable Macros, and I say yes... I seem to be missing something in what I'm doing... I right mouse clicked on the tab and choose View Code and pasted it there. I pretty sure that's what I was supposed to do... Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "D5" 'Change to suit On Error Resume Next Me.Name = Range(sCELLADDRESS).Text On Error GoTo 0 End Sub Can you think of anything I'm doing wrong? Thanks Kelvin PS sorry, I do want the format of the date to be 2-2-07... "JE McGimpsey" wrote in message ... One way: Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "H35" 'Change to suit On Error Resume Next Me.Name = Range(sCELLADDRESS).Text On Error GoTo 0 End Sub In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
Thanks
Here's what I'm using. I have this pasted in the Code for the tab and if I run it manually it works, what do I need to do to make this code fire? What I'm doing is, I use this Excel file as a timesheet. the value in cell D5 is the date the timesheet is to be handed in... So for each pay period I have a Tab and I want the TAB to be the sheet\D5 value to show. I really only need this to update once a year... as the first day of the pay period changed each year... I guess my question is, how do I fire this off for each page? If I have this code in there, will I then always be prompted to "Enable Marcos"? Thanks Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "D5" 'Change to suit On Error Resume Next Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") On Error GoTo 0 End Sub "JE McGimpsey" wrote in message ... Using my suggested solution, instead of Me.Name = Range(sCELLADDRESS).Text you could use Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") or some other format. Note that you can't use slashes in tab names. In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: Oh, and I like the date in the TAB to be formatted, like 6/4/07. Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
I'm not typing the date into the field.
I enter one date on the first spreadsheet and the rest of the sheets calculate off that. so an after update event wouldn't like fire if I don't make the change on that sheet. Also, I don't if this matters, but the sheets are locked for the most part. the cell with the date in it is one that is locked, since it's calcualted there's no reason for the user to type there... Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... Thanks Here's what I'm using. I have this pasted in the Code for the tab and if I run it manually it works, what do I need to do to make this code fire? What I'm doing is, I use this Excel file as a timesheet. the value in cell D5 is the date the timesheet is to be handed in... So for each pay period I have a Tab and I want the TAB to be the sheet\D5 value to show. I really only need this to update once a year... as the first day of the pay period changed each year... I guess my question is, how do I fire this off for each page? If I have this code in there, will I then always be prompted to "Enable Marcos"? Thanks Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "D5" 'Change to suit On Error Resume Next Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") On Error GoTo 0 End Sub "JE McGimpsey" wrote in message ... Using my suggested solution, instead of Me.Name = Range(sCELLADDRESS).Text you could use Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") or some other format. Note that you can't use slashes in tab names. In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: Oh, and I like the date in the TAB to be formatted, like 6/4/07. Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
White out over sheet1 tab let dry then with sharpie put 6/1/07
should work for more sheets repeat steps "Kelvin Beaton" wrote: I'm not typing the date into the field. I enter one date on the first spreadsheet and the rest of the sheets calculate off that. so an after update event wouldn't like fire if I don't make the change on that sheet. Also, I don't if this matters, but the sheets are locked for the most part. the cell with the date in it is one that is locked, since it's calcualted there's no reason for the user to type there... Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... Thanks Here's what I'm using. I have this pasted in the Code for the tab and if I run it manually it works, what do I need to do to make this code fire? What I'm doing is, I use this Excel file as a timesheet. the value in cell D5 is the date the timesheet is to be handed in... So for each pay period I have a Tab and I want the TAB to be the sheet\D5 value to show. I really only need this to update once a year... as the first day of the pay period changed each year... I guess my question is, how do I fire this off for each page? If I have this code in there, will I then always be prompted to "Enable Marcos"? Thanks Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "D5" 'Change to suit On Error Resume Next Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") On Error GoTo 0 End Sub "JE McGimpsey" wrote in message ... Using my suggested solution, instead of Me.Name = Range(sCELLADDRESS).Text you could use Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") or some other format. Note that you can't use slashes in tab names. In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: Oh, and I like the date in the TAB to be formatted, like 6/4/07. Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
Ok, your humor is interesting, but I don't see how it is helpful at this point .......... "Mike" wrote in message ... White out over sheet1 tab let dry then with sharpie put 6/1/07 should work for more sheets repeat steps "Kelvin Beaton" wrote: I'm not typing the date into the field. I enter one date on the first spreadsheet and the rest of the sheets calculate off that. so an after update event wouldn't like fire if I don't make the change on that sheet. Also, I don't if this matters, but the sheets are locked for the most part. the cell with the date in it is one that is locked, since it's calcualted there's no reason for the user to type there... Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... Thanks Here's what I'm using. I have this pasted in the Code for the tab and if I run it manually it works, what do I need to do to make this code fire? What I'm doing is, I use this Excel file as a timesheet. the value in cell D5 is the date the timesheet is to be handed in... So for each pay period I have a Tab and I want the TAB to be the sheet\D5 value to show. I really only need this to update once a year... as the first day of the pay period changed each year... I guess my question is, how do I fire this off for each page? If I have this code in there, will I then always be prompted to "Enable Marcos"? Thanks Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "D5" 'Change to suit On Error Resume Next Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") On Error GoTo 0 End Sub "JE McGimpsey" wrote in message ... Using my suggested solution, instead of Me.Name = Range(sCELLADDRESS).Text you could use Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") or some other format. Note that you can't use slashes in tab names. In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: Oh, and I like the date in the TAB to be formatted, like 6/4/07. Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#14
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
to change all of the sheets to the date in D5 try a macro like
Sub dt() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets sh.Activate shn = Cells(5, 4) sh.Name = Format(shn, "m-d-yy") Next sh End Sub "Kelvin Beaton" wrote: I'm not typing the date into the field. I enter one date on the first spreadsheet and the rest of the sheets calculate off that. so an after update event wouldn't like fire if I don't make the change on that sheet. Also, I don't if this matters, but the sheets are locked for the most part. the cell with the date in it is one that is locked, since it's calcualted there's no reason for the user to type there... Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... Thanks Here's what I'm using. I have this pasted in the Code for the tab and if I run it manually it works, what do I need to do to make this code fire? What I'm doing is, I use this Excel file as a timesheet. the value in cell D5 is the date the timesheet is to be handed in... So for each pay period I have a Tab and I want the TAB to be the sheet\D5 value to show. I really only need this to update once a year... as the first day of the pay period changed each year... I guess my question is, how do I fire this off for each page? If I have this code in there, will I then always be prompted to "Enable Marcos"? Thanks Private Sub Worksheet_Calculate() Const sCELLADDRESS As String = "D5" 'Change to suit On Error Resume Next Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") On Error GoTo 0 End Sub "JE McGimpsey" wrote in message ... Using my suggested solution, instead of Me.Name = Range(sCELLADDRESS).Text you could use Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy") or some other format. Note that you can't use slashes in tab names. In article , "Kelvin Beaton" <kelvin at mccsa dot com wrote: Oh, and I like the date in the TAB to be formatted, like 6/4/07. Thanks Kelvin "Kelvin Beaton" <kelvin at mccsa dot com wrote in message ... I have a spreadsheet that has a calculated date field in "=H34+3" I would love for the TAB to equal that value. I think I've tried to figure this out a long time back, but I think I got stuck becasue it is a calculated fileld. Anyone know if this can be done, and how? Thanks Kelvin |
#15
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have the TAB name be a calculated field...?
In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote: Can you think of anything I'm doing wrong? Do something to make the sheet recalculate, and make sure D5 contains text that is a valid sheet non-duplicate sheet name. |
#16
|
|||
|
|||
Put this in the ThisWorkbook CodeModule. It will automaticaly update the tab of every sheet in the workbook. Yes, you will have to enable macros.
Code:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.EnableEvents = False Sh.Name = Format(Range("D5").Value, "m-d-yy") Application.EnableEvents = True End Sub Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
PivotTable:Using a calculated field result in another calculated f | Excel Worksheet Functions | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
how to create a calculated field from another calculated field? | Excel Discussion (Misc queries) |