Add worksheet based on textbox/userform
 

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)

Add worksheet based on textbox/userform
 

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)

Add worksheet based on textbox/userform
 

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

Mark (InWales)

"Nick Hodge" wrote in message
...
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)