Sheet Naming
I got 2 ways from this forum a while back ....hope it helps
Option 1:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
on error resume next
sh.Name = sh.Range("a2").Value
if err.number < 0 then
msgbox "Rename failed"
err.clear
end if
on error goto 0
End Sub
Option 2:
Option Explicit
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = False
On Error Resume Next
Sh.Name = Sh.Range("a2").Value
If Err.Number < 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("a2")) Is Nothing Then
Exit Sub
End If
On Error Resume Next
Sh.Name = Sh.Range("a2").Value
If Err.Number < 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
End Sub
Cheers,
Mayte
|