• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multi-Select List Box Issue

Lizzie

Lizzie

New Member
Hi All,

I would appreciate any assistance provided to me for the following issue:

The workbook that is attached to the dropbox link below contains a "Create Documents" worksheet that encompasses three buttons. I am presently working within the "Submission Document" button on the first two listbox items named "Property" and "General Liability". If the user selects any of these items a new workbook is to be produced that defines an array specific to the listbox item selection. If the user selects "Property" a new workbook is generated that contains a "Client_Profile" worksheet and a "SubmissionProperty" worksheet as specified in the array. If the user selects "General Liability" a new workbook is generated that contains a "Client_Profile" worksheet and a "SubmsissionLiability" worksheet also specified by the array. The code performs up to this point.

However, when the user selects "Property" and "General Liability", the requirement for one new workbook that contains a "Client_Profile" worksheet, a "SubmissionProperty" worksheet, and a "SubmissionLiability" worksheet, fails. What is produced are two workbooks. One workbook that contains a "Client_Profile" worksheet and a "SubmissionProperty" worksheet. The other workbook contains a "Client_Profile" worksheet and a "SubmsissionLiability" worksheet. The code resides in the SubmissionSelector user form.

Thanks for any and all assistance!

https://www.dropbox.com/s/8aw4bz8bx...arison_TemplateMasterMasterBuildv73.xlsm?dl=0
 
Hi Lizzie,

Please try like below. I have written basic logic. While using variables it is recommended not use built in names, objects and methods. e.g. Dim ThisWorkbook as workbook.
ThisWorkbook is built-in object.

Code: 
Private Sub CMDSubSelector_Click()
SubmissionSelector.Hide

On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

Sheets("SubmissionProperty").Visible = True
    Worksheets("Property").Activate
    Range("D7:D9").Select
    Selection.Copy
    Worksheets("SubmissionProperty").Activate
    Range("C5:C7").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    Worksheets("Property").Activate
    Range("D11:D97").Select
    Selection.Copy
    Worksheets("SubmissionProperty").Activate
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Range("A1").Select
Sheets("SubmissionProperty").Visible = False

    Sheets("SubmissionLiability").Visible = True
    Worksheets("General_Liability").Activate
    Range("D7:D9").Select
    Selection.Copy
    Worksheets("SubmissionLiabilty").Activate
    Range("C5:C7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    Worksheets("General_Liability").Activate
    Range("D11:D97").Select
    Selection.Copy
    Worksheets("SubmissionLiabilty").Activate
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("SubmissionLiability").Visible = False

'\\ Modified portion start   
Dim NewWorkbook As Workbook, SourceWorkbook As Workbook
Set SourceWorkbook = ThisWorkbook
Dim j As Long
SourceWorkbook.Sheets("Client_Profile").Copy
Set NewWorkbook = ActiveWorkbook

For j = 0 To Me.Submissionlist.ListCount - 1
    If Me.Submissionlist.Selected(j) Then
        SourceWorkbook.Sheets(Me.Submissionlist.List(j)).Copy NewWorkbook.Sheets(NewWorkbook.Sheets.Count)
    End If
Next
'\\ Modified portion end.
               
If Me.Submissionlist.Value Then Unload Me
Application.ScreenUpdating = True

End Sub
 
Thank you for the feedback. It reminded me of those financial planning templates I had to fill up for the planner :).
 
You must log in or register to reply here.
Back
Top