VBA Multi Select ListBox Issues

Hi All,

Can someone please assist me. I have created a multi select listbox in VBA that will create new workbooks based on the user selection of listbox items. I have written the following code which works great for single selections. However if index item 0 and 1 are selected two new workbooks are produced. I need one workbook to be produced based off of the array provided. Instead of a workbook that produces three sheets (Client_Profile, SubmissionProperty, and SubmissionLiability. The result returned generates a new workbook containing Client_Profile and SubmissionProperty, and another workbook containing Client_Profile and SubmissionLiability. Please review the code for me:

Code:

---

Dim ThisWorkbook As Workbook
Set ThisWorkbook = ActiveWorkbook


Dim selCount AsLong
selCount=-1


Dim R AsLong
R=0
Dim S AsLong
S=1
Dim I AsLong
I=0&1


For R = R To Me.Submissionlist.ListCount -1
If Me.Submissionlist.Selected(R)Then
Sheets("SubmissionProperty").Visible =False
ThisWorkbook.Worksheets(Array("Client_Profile","SubmissionProperty")).Copy
Sheets("SubmissionProperty").Visible =True
Worksheets("SubmissionLiability").Visible =False
Worksheets("Client_Profile").Move Before:=Worksheets(1)
Worksheets("Client_Profile").Activate
Range("A1").Select
End If
If selCount =-1Then
Me.Submissionlist.Selected(R)=False
Me.Submissionlist.Clear
End If
Exit For
Next


For S = S To Me.Submissionlist.ListCount -1
If Me.Submissionlist.Selected(S)Then
Sheets("SubmissionLiabilty").Visible =False
ThisWorkbook.Worksheets(Array("Client_Profile","SubmissionLiabilty")).Copy
Sheets("SubmissionLiabilty").Visible =True
Worksheets("Client_Profile").Move Before:=Worksheets(1)
Worksheets("Client_Profile").Activate
End If
If selCount =-1 Then
Me.Submissionlist.Selected(S)=False
Me.Submissionlist.Clear
End If
Exit For
Next


For I = R & S To Me.Submissionlist.ListCount -1
If Me.Submissionlist.Selected(I)=TrueThen
Sheets("SubmissionProperty").Visible =False
Sheets("SubmissionLiabilty").Visible =False
ThisWorkbook.Worksheets(Array("Client_Profile","SubmissionProperty","SubmissionLiabilty")).Copy
Sheets("SubmissionProperty").Visible =True
Sheets("SubmissionLiabilty").Visible =True
Worksheets("Client_Profile").Move Before:=Worksheets(1)
Worksheets("Client_Profile").Activate
End If
If selCount =-1Then
Me.Submissionlist.Selected(I)=False
Me.Submissionlist.Clear
End If
Exit For
Next


If Me.Submissionlist.Value Then Unload Me
Application.ScreenUpdating =True


End Sub

---

Please use code tags !

Sorry, I don't know what you mean. I apologize if I posted this incorrectly. How do I use code tags?

I edited the post. Thanks for correcting me!!

welcome to the forum.

all spaces have been removed from the code.

edit your post.
delete the code.
paste it again. select it and click # button.

THANKS!!!!

upload your workbook (via Go Advanced, Manage Attachments buttons). replace any sensitive data.
and clearly define your requirement.

& is a string function. If R = 1 and S = 2, then R & S = "12"

Code:

---

For I = R & S To Me.Submissionlist.ListCount -1

---

The Create Documents worksheet have three buttons. I am presently working on the Submission Document button and the first two list items in the multi-select list box (Property and General Liability). If the user selects Property a new workbook should be generated that contains a Client_Profile worksheet and a SubmissionProperty worksheet. If the user selects General Liability a new workbook should be generated that contains a Client_Profile worksheet and a SubmsissionLiability worksheet. So far the code performs. However, when the user selects Property and General Liability, the requirement is for a new workbook that contains a Client_Profile worksheet, SubmissionProperty worksheet, and SubmissionLiability worksheet.

What is produced is two workbooks. One workbook a Client_Profile worksheet and a SubmissionProperty worksheet, and the other workbook has a Client_Profile worksheet and a SubmsissionLiability worksheet.

The code is embedded in the SubmissionSelector user form.


Thank you so much for looking at this for me!!!!!!!

Code:

---

Private Sub CMDSubSelector_Click()
  SubmissionSelector.Hide

  For j = 0 To Submissionlist.ListCount - 1
      If Submissionlist.Selected(j) Then c00 = c00 & "|" & Submissionlist.List(j)
  Next
  Sheets(Split(Mid(c00, 2), "|")).Copy
End sub

---

Thank you, I will ty this out.

Hi snb,

Can you please explain this code to me. I don't know how to incorporate it.

Thank you

Replace your total code by mine & test it.

Okay, I will try it out again. THANKS!!

crossposted:

http://www.excelguru.ca/forums/showt...ll=1#post22423

Cross Posting