Mark

Only lightly tested...but this should work

Private Sub Text4_Change()
Dim ans As String
Dim wks As Worksheet
ans = Text4.Text 'InputBox("What is the pallet number?", "Pallet Number ")"
For Each wks In ThisWorkbook.Worksheets
If wks.Name = ans Then
MsgBox "Error: Pallet " & ans & " already exists.", vbOKOnly +
vbInformation
Exit Sub
End If
Next wks
Sheets.Add.Name = ans
Worksheets.Add after:=Worksheets(Worksheets.Count)
Sheets(ans).Select
'OrderNo.Show
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Mark (InWales)" wrote in message
...
Hello world

I have some code from Chip Pearson (Posted here!) but I need some help
with it. Basically a userform is asking a user to input information
relating to pallets. The form is asking the user to tell it what the
pallet number is where goods are being added to. The code works great but
what I don't understand is how to stop sheets(no.) being added when a
duplicate is found...

Private Sub Text4_Change()
Dim ans As String
ans = Text4.Text 'InputBox("What is the pallet number?", "Pallet
Number")
If ThisWorkbook.SheetExists(ans) = False Then
Sheets.Add.Name = ans
Worksheets.Add after:=Worksheets(Worksheets.Count)
Sheets(ans).Select
Else
MsgBox "Error: Pallet " & ans & " aleady exists.", vbInformation
Exit Sub
End If
'OrderNo.Show
End Sub

Function SheetExists(sname As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(sname).Name))
End Function

Any help as always appreciated
Mark (InWales)