sheet1
 

Hi,

I would like to change/rename the sheet tab to automatically update to cell
in that sheet.

The tab is called sheet1, as soon as I go into sheet1 and update cell A1 to
WIP, the name of that sheet 1 should correspond to WIP.

Kindly assist

thanks
TJ

sheet1
 

Right click sheet tabview codeinsert this. If you want it to apply to ALL
sheets, place in the This_Workbook module instead

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < Range("a1").Address Then Exit Sub
ActiveSheet.Name = Target

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TJ" wrote in message
...
Hi,

I would like to change/rename the sheet tab to automatically update to
cell
in that sheet.

The tab is called sheet1, as soon as I go into sheet1 and update cell A1
to
WIP, the name of that sheet 1 should correspond to WIP.

Kindly assist

thanks
TJ

sheet1
 

TJ wrote:
Hi,

I would like to change/rename the sheet tab to automatically update to cell
in that sheet.

The tab is called sheet1, as soon as I go into sheet1 and update cell A1 to
WIP, the name of that sheet 1 should correspond to WIP.

Kindly assist

thanks
TJ

This code will do what you want

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
sSheetName = Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

Credit to

http://www.mcgimpsey.com/excel/event...efromcell.html

found at their site.

It is always a good idea to google your request - you never know what will
come back

edvwvw

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200904/1

sheet1
 

Thank you very much!


"edvwvw via OfficeKB.com" wrote:

TJ wrote:
Hi,

I would like to change/rename the sheet tab to automatically update to cell
in that sheet.

The tab is called sheet1, as soon as I go into sheet1 and update cell A1 to
WIP, the name of that sheet 1 should correspond to WIP.

Kindly assist

thanks
TJ

This code will do what you want

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
sSheetName = Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

Credit to

http://www.mcgimpsey.com/excel/event...efromcell.html

found at their site.

It is always a good idea to google your request - you never know what will
come back

edvwvw

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200904/1