Transfer Cell Contents to Tab
HI
Yes that's got it. Works like clockwork.
I combined the two routines , and now the contents of the target cell C2
are transferred to the tab , and then replaced with the text specified
in 'NewName' when C2 is blanked.
This means that the name of the tab can revert to it's previous name
before it was amended.
Perfect - thanks.
Here's how the final code looks :
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If
ws_exit:
Application.EnableEvents = True
Dim NewName As String
NewName = Me.Range("c2").Value
If NewName = "" Then
NewName = "Audit Results Template"
End If
If LCase(Me.Name) = LCase(NewName) Then
'do nothing
Else
On Error Resume Next
Me.Name = NewName
If Err.Number < 0 Then
Err.Clear
Beep
MsgBox "Can't rename"
End If
On Error GoTo 0
End If
End Sub
Best Wishes
Colin
In article , Dave Peterson
writes
Maybe...
Option Explicit
Private Sub Worksheet_Calculate()
Dim NewName As String
NewName = Me.Range("c2").Value
If NewName = "" Then
NewName = "OldName"
End If
If LCase(Me.Name) = LCase(NewName) Then
'do nothing
Else
On Error Resume Next
Me.Name = NewName
If Err.Number < 0 Then
Err.Clear
Beep
MsgBox "Can't rename"
End If
On Error GoTo 0
End If
End Sub
Colin Hayes wrote:
Hi All
No ideas on this one?
Grateful if you could help.
Best Wishes
Colin
In article , Colin Hayes
writes
Hi
OK thanks guys I got that working fine. Very grateful.
It transfers the contents of C2 to the tab no problem.
I do notice however that when I delete the new value in C2 , that the
tab value stays the same. Is the a way to add a few lines to the code ,
so that the tab name would revert to a fixed title - say 'Oldname' when
C2 is blanked?
Best Wishes
Colin
In article , Max
writes
Here's one from a past post by Bob Phillips which does it ..
(Note that the file must be saved beforehand. A pre-requisite.)
'---------
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
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.
|