Sheet's Name

  • Thread starter Thread starter Martín2626
  • Start date Start date
M

Martín2626

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
 
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
 
Thanks but it doesn't work. When i change the sheet's name nothin
happens in cell a1.
Other way???
 
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
 
Use this formula then in A1

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
 
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 said:
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

message news:[email protected]...
Thanks but it doesn't work. When i change the sheet's name nothing
happens in cell a1.
Other way???
i
 
This is what i did:
Right click on sheet1, see code and I pasted the code there.
It's Ok, isn't it??
 
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
 
THANKS, THIS FORMULA WORKS PERFECT !!!!

MARTIN






Chip said:
*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 said:
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

Thanks but it doesn't work. When i change the sheet's name nothing
happens in cell a1.
Other way???



the worksheet.
i
 
Back
Top