Hi Adam,

The following is slightly modified from something I use.

Best Regards,
Walt Weber

'This part in the Sheet Obect Code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("B4").Address Then _
Call NameSheet
End Sub

'This part in a Module
'Call this from the Worksheet Change routine.
Sub NameSheet()
Dim ChNo As Integer, X As String, Y As String
On Error GoTo NameSheetERROR
Application.EnableCancelKey = False
X = ActiveSheet.Range("D4").Value
Y = ""

'Remove Special Characters
'I think I remember this part was in a Debra Dalgleish response.
For ChNo = 1 To Len(X)
If Mid(X, ChNo, 1) Like "[A-Za-z0-9]" Then
Y = Y & Mid(X, ChNo, 1)
End If
Next

'Optionally limit the name to 12 characters.
Y = Left(Y, 12)

'Trap any worksheet name duplicates
For Each WS In ThisWorkbook.Worksheets
If UCase(WS.Name) = UCase(Y) Then
MsgBox "That name resolves to one already in use." _
& Chr(13) & "Try changing it please."
Exit Sub
End If
Next

ActiveSheet.Name = Y
Exit Sub
NameSheetERROR:
MsgBox "Error in NameSheet routine. -- " & Error()
End Sub


"Adam" wrote:

I have worksheet_calculate even that changes the name of the sheet tab
depending on cell D4 wich contains "OF_"&B4
Is there any way to set validation on cell B4 so that it is not possible to
insert symbols that are not accepted as sheet names as - * etc. or is it
possible to prevent this in the event code. The code is as follows

Private Sub Worksheet_Calculate()
Me.Name = Range("D4")
End Sub

Thanks for any help