Trying to add (create) worksheets in a workbook using a MultiSelect Listbox and have the sheet tabs be names as selected in the MultiSelect Listbox.

It has me stumped. I have been able to delete sheets and even print sheets, using a MultiSelect Listbox. The difference is deleting and printing the sheets exist.

Any help would be appreciated.

Thank you in advance.

WKWells

--
Posted by Mimo Usenet Browser v0.2.5
http://www.mimousenet.com/mimo/post

"wkwells" wrote in message Trying to add (create)
worksheets in a workbook using a MultiSelect Listbox and have the sheet tabs
be names as selected in the MultiSelect Listbox.

It has me stumped. I have been able to delete sheets and even print
sheets, using a MultiSelect Listbox. The difference is deleting and
printing the sheets exist.

Any help would be appreciated.

Thank you in advance.

WKWells

Adapt the following

'with a listbox and a commanbutton on the form

Private Sub CommandButton1_Click()
Dim i
Dim sName As String
Dim sht As Object
Dim ws As Worksheet

For i = Me.ListBox1.ListCount - 1 To 0 Step -1
If Me.ListBox1.Selected(i) Then
sName = Me.ListBox1.List(i)
On Error Resume Next
Set sht = ActiveWorkbook.Sheets(sName)
On Error GoTo 0

If Not sht Is Nothing Then
MsgBox sName & " already exists"

Else
Set ws = ActiveWorkbook.Worksheets.Add
ws.Name = sName
End If

Me.ListBox1.RemoveItem i

End If
Next
ListBox1_Change
End Sub

Private Sub ListBox1_Change()

Me.CommandButton1.Enabled = Me.ListBox1.ListIndex = 0

End Sub

Private Sub UserForm_Initialize()
Dim i As Long
Dim arr
Dim sht As Object

arr = Array("Apples", "Bananas", "Pears", "Oranges")
Me.ListBox1.List = arr
For i = UBound(arr) To i Step -1
For Each sht In ActiveWorkbook.Sheets
If LCase$(sht.Name) = LCase$(arr(i)) Then
Me.ListBox1.RemoveItem i
Exit For
End If
Next
Next
Me.CommandButton1.Enabled = False
Me.CommandButton1.Caption = "Add selected sheet(s)"

End Sub

Regards,
Peter T