Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheet tab = to cell content
i have a workbook with 12 sheets on it (workbook A). this workbook is
VERY linked to another workbook (workbook B). workbook A has an area on each sheet for a date (cell C8) of each of the 12 sheets. this date comes from workbook B. workbook B contains a list of dates that increment weekly - i.e. 20-Feb-2007, 27-Feb-2007, 6-Mar-2007, etc. these dates in workbook B will change (via. linkages to other cells in workbook B). i am trying to name the sheets/tabs in workbook A to these dates that are in cell C8. I found a number of posts that allow you to do this, however, the problem is that most of them relate to changing/focusing on the cell (having to click in the cell & then it the tab value would change). I would like to know if this is possible to do as the file is open... after you click on yes to that box that comes up when you have linked workbooks. because the main issue is that someone may never click in that date cell (C8) but the date could be changed in workbook B if this is confusing, let me know. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheet tab = to cell content
One way:
this will run only one time after the workbook is opened (and after the request to update). Put it in your ThisWorkbook code module: Private Sub Workbook_Open() Dim ws As Worksheet On Error Resume Next For Each ws In ThisWorkbook.Worksheets ws.Name = ws.Cells(8, "C").Text Next ws End Sub In article .com, "reaa" wrote: i have a workbook with 12 sheets on it (workbook A). this workbook is VERY linked to another workbook (workbook B). workbook A has an area on each sheet for a date (cell C8) of each of the 12 sheets. this date comes from workbook B. workbook B contains a list of dates that increment weekly - i.e. 20-Feb-2007, 27-Feb-2007, 6-Mar-2007, etc. these dates in workbook B will change (via. linkages to other cells in workbook B). i am trying to name the sheets/tabs in workbook A to these dates that are in cell C8. I found a number of posts that allow you to do this, however, the problem is that most of them relate to changing/focusing on the cell (having to click in the cell & then it the tab value would change). I would like to know if this is possible to do as the file is open... after you click on yes to that box that comes up when you have linked workbooks. because the main issue is that someone may never click in that date cell (C8) but the date could be changed in workbook B if this is confusing, let me know. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheet tab = to cell content
You need a calculate event.
Private Sub Worksheet_Calculate() Me.Name = Range("C8").Value End Sub Right-click on sheet tab and "View Code" Paste into that sheet module. Gord Dibben MS Excel MVP On 20 Feb 2007 12:43:07 -0800, "reaa" wrote: i have a workbook with 12 sheets on it (workbook A). this workbook is VERY linked to another workbook (workbook B). workbook A has an area on each sheet for a date (cell C8) of each of the 12 sheets. this date comes from workbook B. workbook B contains a list of dates that increment weekly - i.e. 20-Feb-2007, 27-Feb-2007, 6-Mar-2007, etc. these dates in workbook B will change (via. linkages to other cells in workbook B). i am trying to name the sheets/tabs in workbook A to these dates that are in cell C8. I found a number of posts that allow you to do this, however, the problem is that most of them relate to changing/focusing on the cell (having to click in the cell & then it the tab value would change). I would like to know if this is possible to do as the file is open... after you click on yes to that box that comes up when you have linked workbooks. because the main issue is that someone may never click in that date cell (C8) but the date could be changed in workbook B if this is confusing, let me know. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheet tab = to cell content
JE, thanks for the reply. your code has the same effect as Gord... it
allowed the dates on each of the sheets to be calculated on open, but now i was hoping to get teh code for each sheet to be named after these newly calculated dates (on open). it appeared that the tab names on the workbook remained as the old dates and were not changed to the newly calculated date. any help you can provide would be greatly appreciated. thanks :o) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheet tab = to cell content
Well, for me the tab names changed. Are you sure the format in your cell
is correct for a tab name? If the new date is being used already as a tab name, the code will fail to rename a sheet with the same name. What happens when you comment out the "On Error Resume Next" line? Are you sure the In article .com, "reaa" wrote: JE, thanks for the reply. your code has the same effect as Gord... it allowed the dates on each of the sheets to be calculated on open, but now i was hoping to get teh code for each sheet to be named after these newly calculated dates (on open). it appeared that the tab names on the workbook remained as the old dates and were not changed to the newly calculated date. any help you can provide would be greatly appreciated. thanks :o) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheet tab = to cell content
thanks all. the code provided by JE worked.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to find cell content in sheets and make sheet active | Excel Discussion (Misc queries) | |||
add up cell content to get answer at bottom of sheet | Excel Discussion (Misc queries) | |||
add up cell content to get answer at bottom of sheet | Excel Discussion (Misc queries) | |||
Sheet Protection -- Content vs. FORMATTING | New Users to Excel | |||
How to search and copy cell content to another sheet? | Excel Worksheet Functions |