Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
Is there a way to get a Tab name to reference what is in a specific cell?
-- Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
Yes.
You'll need a macro for that. Changing a sheetname causes changes to all local and remote formulas that depends on cells in this sheet. So before you ask "which macro", let me ask which specific cell and how does it change its value? Best wishes Harald "Eric D" wrote in message ... Is there a way to get a Tab name to reference what is in a specific cell? -- Eric |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
I have a template saved. Every month cell A5 = "For The Month Ending
February 2009" for february and this changes for each month. I just want the a tab to change based on what was pasted in cell A5. Does that make sense? -- Eric "Harald Staff" wrote: Yes. You'll need a macro for that. Changing a sheetname causes changes to all local and remote formulas that depends on cells in this sheet. So before you ask "which macro", let me ask which specific cell and how does it change its value? Best wishes Harald "Eric D" wrote in message ... Is there a way to get a Tab name to reference what is in a specific cell? -- Eric |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
Absolutely. Rightclick sheet tab, "view code", paste this in:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub On Error Resume Next Me.Name = Trim$(Replace(Me.Range("A5").Value, _ "For The Month Ending ", "")) End Sub HTH. Best wishes Harald "Eric D" wrote in message ... I have a template saved. Every month cell A5 = "For The Month Ending February 2009" for february and this changes for each month. I just want the a tab to change based on what was pasted in cell A5. Does that make sense? -- Eric "Harald Staff" wrote: Yes. You'll need a macro for that. Changing a sheetname causes changes to all local and remote formulas that depends on cells in this sheet. So before you ask "which macro", let me ask which specific cell and how does it change its value? Best wishes Harald "Eric D" wrote in message ... Is there a way to get a Tab name to reference what is in a specific cell? -- Eric |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
the cell i want to reference is on another tab will this formula still work?
-- Eric "Harald Staff" wrote: Absolutely. Rightclick sheet tab, "view code", paste this in: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub On Error Resume Next Me.Name = Trim$(Replace(Me.Range("A5").Value, _ "For The Month Ending ", "")) End Sub HTH. Best wishes Harald "Eric D" wrote in message ... I have a template saved. Every month cell A5 = "For The Month Ending February 2009" for february and this changes for each month. I just want the a tab to change based on what was pasted in cell A5. Does that make sense? -- Eric "Harald Staff" wrote: Yes. You'll need a macro for that. Changing a sheetname causes changes to all local and remote formulas that depends on cells in this sheet. So before you ask "which macro", let me ask which specific cell and how does it change its value? Best wishes Harald "Eric D" wrote in message ... Is there a way to get a Tab name to reference what is in a specific cell? -- Eric |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
Maybe...
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, me.Range("A5")) Is Nothing Then Exit Sub end if On Error Resume Next Sheet9999.Name = Trim$(Replace(Me.Range("A5").Value, _ "For The Month Ending ", "")) On Error Goto 0 End Sub This looks at A5 of the sheet that owns the code. And it changes the name of the worksheet that has a code name of Sheet9999. You can find the codename of the worksheet by opening the VBE (alt-f11). Showing the project explorer (ctrl-r) Expanding the workbooks project (like expanding a folder in windows explorer) Under the Microsoft Objects section, you'll see something like: Sheet1 (NameYouSeeOnTheWorkSheetTabInExcel) The codename is the name to the left of the name in ()'s. You'll have to change that in the code. And this code goes in the worksheet module for the worksheet that contains the cell getting changed. Eric D wrote: the cell i want to reference is on another tab will this formula still work? -- Eric "Harald Staff" wrote: Absolutely. Rightclick sheet tab, "view code", paste this in: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub On Error Resume Next Me.Name = Trim$(Replace(Me.Range("A5").Value, _ "For The Month Ending ", "")) End Sub HTH. Best wishes Harald "Eric D" wrote in message ... I have a template saved. Every month cell A5 = "For The Month Ending February 2009" for february and this changes for each month. I just want the a tab to change based on what was pasted in cell A5. Does that make sense? -- Eric "Harald Staff" wrote: Yes. You'll need a macro for that. Changing a sheetname causes changes to all local and remote formulas that depends on cells in this sheet. So before you ask "which macro", let me ask which specific cell and how does it change its value? Best wishes Harald "Eric D" wrote in message ... Is there a way to get a Tab name to reference what is in a specific cell? -- Eric -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
hey dave, thanks for the suggestion, but i am very very visual basic
impared, i know nothing ... i don't see a module for the sheet that will contain the "A5" i want to reference. Do i just add a module to the workbook and paste that formula in there? (changing of course the reference of the sheet i am wanting to change) -- Eric "Dave Peterson" wrote: Maybe... Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, me.Range("A5")) Is Nothing Then Exit Sub end if On Error Resume Next Sheet9999.Name = Trim$(Replace(Me.Range("A5").Value, _ "For The Month Ending ", "")) On Error Goto 0 End Sub This looks at A5 of the sheet that owns the code. And it changes the name of the worksheet that has a code name of Sheet9999. You can find the codename of the worksheet by opening the VBE (alt-f11). Showing the project explorer (ctrl-r) Expanding the workbooks project (like expanding a folder in windows explorer) Under the Microsoft Objects section, you'll see something like: Sheet1 (NameYouSeeOnTheWorkSheetTabInExcel) The codename is the name to the left of the name in ()'s. You'll have to change that in the code. And this code goes in the worksheet module for the worksheet that contains the cell getting changed. Eric D wrote: the cell i want to reference is on another tab will this formula still work? -- Eric "Harald Staff" wrote: Absolutely. Rightclick sheet tab, "view code", paste this in: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub On Error Resume Next Me.Name = Trim$(Replace(Me.Range("A5").Value, _ "For The Month Ending ", "")) End Sub HTH. Best wishes Harald "Eric D" wrote in message ... I have a template saved. Every month cell A5 = "For The Month Ending February 2009" for february and this changes for each month. I just want the a tab to change based on what was pasted in cell A5. Does that make sense? -- Eric "Harald Staff" wrote: Yes. You'll need a macro for that. Changing a sheetname causes changes to all local and remote formulas that depends on cells in this sheet. So before you ask "which macro", let me ask which specific cell and how does it change its value? Best wishes Harald "Eric D" wrote in message ... Is there a way to get a Tab name to reference what is in a specific cell? -- Eric -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
Right click on the worksheet tab that contains that A5 that you'll be changing.
Select View code and you'll be there. Eric D wrote: hey dave, thanks for the suggestion, but i am very very visual basic impared, i know nothing ... i don't see a module for the sheet that will contain the "A5" i want to reference. Do i just add a module to the workbook and paste that formula in there? (changing of course the reference of the sheet i am wanting to change) -- Eric "Dave Peterson" wrote: Maybe... Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, me.Range("A5")) Is Nothing Then Exit Sub end if On Error Resume Next Sheet9999.Name = Trim$(Replace(Me.Range("A5").Value, _ "For The Month Ending ", "")) On Error Goto 0 End Sub This looks at A5 of the sheet that owns the code. And it changes the name of the worksheet that has a code name of Sheet9999. You can find the codename of the worksheet by opening the VBE (alt-f11). Showing the project explorer (ctrl-r) Expanding the workbooks project (like expanding a folder in windows explorer) Under the Microsoft Objects section, you'll see something like: Sheet1 (NameYouSeeOnTheWorkSheetTabInExcel) The codename is the name to the left of the name in ()'s. You'll have to change that in the code. And this code goes in the worksheet module for the worksheet that contains the cell getting changed. Eric D wrote: the cell i want to reference is on another tab will this formula still work? -- Eric "Harald Staff" wrote: Absolutely. Rightclick sheet tab, "view code", paste this in: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub On Error Resume Next Me.Name = Trim$(Replace(Me.Range("A5").Value, _ "For The Month Ending ", "")) End Sub HTH. Best wishes Harald "Eric D" wrote in message ... I have a template saved. Every month cell A5 = "For The Month Ending February 2009" for february and this changes for each month. I just want the a tab to change based on what was pasted in cell A5. Does that make sense? -- Eric "Harald Staff" wrote: Yes. You'll need a macro for that. Changing a sheetname causes changes to all local and remote formulas that depends on cells in this sheet. So before you ask "which macro", let me ask which specific cell and how does it change its value? Best wishes Harald "Eric D" wrote in message ... Is there a way to get a Tab name to reference what is in a specific cell? -- Eric -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
Just right-click on the appropriate Sheet tab and "View Code"
Copy/paste Dave's code into that module. Edit to suit then Alt + q to return to Excel window. Or if you have VBE open already with your your Microsoft Excel Objects expanded, just double-click on the Sheet1 or 2 or whichever Gord Dibben MS Excel MVP On Thu, 5 Mar 2009 12:14:01 -0800, Eric D wrote: hey dave, thanks for the suggestion, but i am very very visual basic impared, i know nothing ... i don't see a module for the sheet that will contain the "A5" i want to reference. Do i just add a module to the workbook and paste that formula in there? (changing of course the reference of the sheet i am wanting to change) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
I told you how to do this. You didn't even test ths code I wrote for you,
did you? "Eric D" wrote in message ... hey dave, thanks for the suggestion, but i am very very visual basic impared, i know nothing ... i don't see a module for the sheet that will contain the "A5" i want to reference. Do i just add a module to the workbook and paste that formula in there? (changing of course the reference of the sheet i am wanting to change) -- Eric |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab name = what's in a cell
He did change what he wanted, though.
Harald Staff wrote: I told you how to do this. You didn't even test ths code I wrote for you, did you? "Eric D" wrote in message ... hey dave, thanks for the suggestion, but i am very very visual basic impared, i know nothing ... i don't see a module for the sheet that will contain the "A5" i want to reference. Do i just add a module to the workbook and paste that formula in there? (changing of course the reference of the sheet i am wanting to change) -- Eric -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |