|
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/ |
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/ |
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 |
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/ |
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/ |
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/ |
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/ |
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/ |
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 |
| All times are GMT +1. The time now is 03:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com