I screwed up the _change event:

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Set myCell = Me.Range("C2")
If myCell.HasFormula = False Then
Exit Sub
End If
Call DoTheRename(myCell)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("C2")

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
End If

If Target.HasFormula Then
Exit Sub
End If
Call DoTheRename(Target)
End Sub
Sub DoTheRename(myCell As Range)

Dim NewName As String

NewName = myCell.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


Dave Peterson wrote:

Since the code to do the renaming is gonna be the same, I think I'd separate
that portion to it's own procedure.

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Set myCell = Me.Range("C2")
If myCell.HasFormula = False Then
Exit Sub
End If
Call DoTheRename(myCell)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("C2")
If myCell.HasFormula Then
Exit Sub
End If
Call DoTheRename(myCell)
End Sub
Sub DoTheRename(myCell As Range)

Dim NewName As String

NewName = myCell.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

Max wrote:

Dave,
Thanks for responding. How could your sub be tweaked to also cater for C2
not containing a formula? Ie make it work even if we were to key in or paste
in a value into C2. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--

Dave Peterson

--

Dave Peterson