Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code for renaming a work sheet with a cell reference
I would like to change the sheet name automatically whenever a cell reference
is changed |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code for renaming a work sheet with a cell reference
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Me.Name = .Name End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Britto" wrote in message ... I would like to change the sheet name automatically whenever a cell reference is changed |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code for renaming a work sheet with a cell reference
What you need is a worksheet_change event. Right click the sheet tabview
codeleft window select worksheetright window select changeput in your macro if target.address="$A$1" then activesheet.name=target -- Don Guillett SalesAid Software "John Britto" wrote in message ... I would like to change the sheet name automatically whenever a cell reference is changed |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code for renaming a work sheet with a cell reference
think Bob meant
Me.Name = .Name should be Me.Name = .Value -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Me.Name = .Name End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Britto" wrote in message ... I would like to change the sheet name automatically whenever a cell reference is changed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Last Sheet | Excel Worksheet Functions | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Clicking Cell Link Changes Cell on Another Sheet | Excel Discussion (Misc queries) | |||
indirect function to reference cell on different sheet | Excel Worksheet Functions | |||
Using a cell reference of a sheet in Vlookup | Excel Worksheet Functions |