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

You can put in about any valid formula in data validation, so if you come up
with a formula which checks for all the invalid characters, then you could
us validation. In you event, you would have to just remove any invalid
characters or clear the cell and not rename the sheet or something like
that. I am sure you will receive other ideas as well.

--
regards,
Tom Ogilvy

"Adam" wrote in message
...
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

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