VBA Multi Select ListBox Issues

[LizCorbert]

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:


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

[snb]

Please use code tags !

[LizCorbert]

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

[LizCorbert]

I edited the post. Thanks for correcting me!!

[mancubus]

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.

[LizCorbert]

THANKS!!!!

[mancubus]

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

[SamT]

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

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

[LizCorbert]

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!!!!!!!

[snb]

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

[LizCorbert]

Thank you, I will ty this out.

[LizCorbert]

Hi snb,

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

Thank you

[snb]

Replace your total code by mine & test it.

[LizCorbert]

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

[snb]

crossposted:

http://www.excelguru.ca/forums/showthread.php?5472-Multi-Select-List-Box-Issue&p=22423&viewfull=1#post22423

[SamT]

Cross Posting (http://www.vbaexpress.com/forum/showthread.php?18537-Cross-Posting&p=137004&viewfull=1#post137004)