Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Textbox value based on another textbox value.doc | Excel Discussion (Misc queries) | |||
Calculate Textbox value based on another textbox value | Excel Discussion (Misc queries) | |||
Generating a Userform based on a worksheet | Excel Discussion (Misc queries) | |||
Textbox in userform | Excel Programming | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming |