| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet's Name
HI ALL ! I want to automatically change the name of cell A1 when I
change the sheet's name. For example: Cell A1=JANUARY Sheet 1=JANUARY When i write FEBRUARY I want that cell A1 = February but i need it to change automatically. TIA MARTIN --- Message posted from http://www.ExcelForum.com/ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sheet's Name
Martin,
Put the following code in the code module for the worksheet. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Address = "$A$1" Then Me.Name = Range("A1").Value End If If Err.Number < 0 Then MsgBox "Error naming sheet: " & Err.Description End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 " wrote in message ... HI ALL ! I want to automatically change the name of cell A1 when I change the sheet's name. For example: Cell A1=JANUARY Sheet 1=JANUARY When i write FEBRUARY I want that cell A1 = February but i need it to change automatically. TIA MARTIN --- Message posted from http://www.ExcelForum.com/ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sheet's Name
Thanks but it doesn't work. When i change the sheet's name nothin
happens in cell a1. Other way??? Chip Pearson wrote: *Martin, Put the following code in the code module for the worksheet. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Address = "$A$1" Then Me.Name = Range("A1").Value End If If Err.Number < 0 Then MsgBox "Error naming sheet: " & Err.Description End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 " wrote in message ... HI ALL ! I want to automatically change the name of cell A1 when I change the sheet's name. For example: Cell A1=JANUARY Sheet 1=JANUARY When i write FEBRUARY I want that cell A1 = February but i need it to change automatically. TIA MARTIN --- Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sheet's Name
Martin,
Where did you put the code? It should go in the worksheet's code module, not a regular code module or the ThisWorkbook code module. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 " wrote in message ... Thanks but it doesn't work. When i change the sheet's name nothing happens in cell a1. Other way??? Chip Pearson wrote: *Martin, Put the following code in the code module for the worksheet. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Address = "$A$1" Then Me.Name = Range("A1").Value End If If Err.Number < 0 Then MsgBox "Error naming sheet: " & Err.Description End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 " wrote in message ... HI ALL ! I want to automatically change the name of cell A1 when I change the sheet's name. For example: Cell A1=JANUARY Sheet 1=JANUARY When i write FEBRUARY I want that cell A1 = February but i need it to change automatically. TIA MARTIN --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sheet's Name
Martin,
I misread your question. To return the sheet's name back to a cell, use the following formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Chip Pearson" wrote in message ... Martin, Where did you put the code? It should go in the worksheet's code module, not a regular code module or the ThisWorkbook code module. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 " wrote in message ... Thanks but it doesn't work. When i change the sheet's name nothing happens in cell a1. Other way??? Chip Pearson wrote: *Martin, Put the following code in the code module for the worksheet. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Address = "$A$1" Then Me.Name = Range("A1").Value End If If Err.Number < 0 Then MsgBox "Error naming sheet: " & Err.Description End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 " wrote in message ... HI ALL ! I want to automatically change the name of cell A1 when I change the sheet's name. For example: Cell A1=JANUARY Sheet 1=JANUARY When i write FEBRUARY I want that cell A1 = February but i need it to change automatically. TIA MARTIN --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sheet's Name
THANKS, THIS FORMULA WORKS PERFECT !!!!
MARTIN Chip Pearson wrote: *Martin, I misread your question. To return the sheet's name back to a cell, use the following formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Chip Pearson" wrote in message ... Martin, Where did you put the code? It should go in the worksheet's code module, not a regular code module or the ThisWorkbook code module. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 " wrote in messag ... Thanks but it doesn't work. When i change the sheet's name nothing happens in cell a1. Other way??? Chip Pearson wrote: *Martin, Put the following code in the code module fo the worksheet. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Address = "$A$1" Then Me.Name = Range("A1").Value End If If Err.Number < 0 Then MsgBox "Error naming sheet: " & Err.Description End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 wrote in message ... HI ALL ! I want to automatically change the name o cell A1 when I change the sheet's name. For example: Cell A1=JANUARY Sheet 1=JANUARY When i write FEBRUARY I want that cell A1 = Februar but i need it to change automatically. TIA MARTIN --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sheet's Name
This is what i did:
Right click on sheet1, see code and I pasted the code there. It's Ok, isn't it?? --- Message posted from http://www.ExcelForum.com/ |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sheet's Name
Use this formula then in A1
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Martín2626 " wrote in message ... Thanks but it doesn't work. When i change the sheet's name nothing happens in cell a1. Other way??? Chip Pearson wrote: *Martin, Put the following code in the code module for the worksheet. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Address = "$A$1" Then Me.Name = Range("A1").Value End If If Err.Number < 0 Then MsgBox "Error naming sheet: " & Err.Description End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Martín2626 " wrote in message ... HI ALL ! I want to automatically change the name of cell A1 when I change the sheet's name. For example: Cell A1=JANUARY Sheet 1=JANUARY When i write FEBRUARY I want that cell A1 = February but i need it to change automatically. TIA MARTIN --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sheet's Name
I'm doing the same thing with the workbook I use for my budget. I am using
a simple custom formula : Public Function SheetName(BaseCell As Range) As String SheetName = BaseCell.Parent.Name End Function Put this in a module and you can use the formula in whatever cell you want. Lauren "Martín2626 " wrote in message ... HI ALL ! I want to automatically change the name of cell A1 when I change the sheet's name. For example: Cell A1=JANUARY Sheet 1=JANUARY When i write FEBRUARY I want that cell A1 = February but i need it to change automatically. TIA MARTIN --- Message posted from http://www.ExcelForum.com/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Possible to refer to a sheet's object name ? | Excel Worksheet Functions | |||
| Printing Sheet's Name | Excel Discussion (Misc queries) | |||
| function to use sheet's name as variable | Excel Worksheet Functions | |||
| how to get sheet's number it's name | Excel Discussion (Misc queries) | |||
| Tab Name Shown In Another Sheet's Cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
