Add worksheet based on textbox/userform

[Mark \(InWales\)]

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)

 

[Nick Hodge]

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
(e-mail address removed)

 

[Mark \(InWales\)]

That's marvellous. Thank you very much Nick for your help

Mark (InWales)