Populate Worksheet Listbox with Worksheet Names based on Cell Value then select to print as one document

Bayport_Mama

New Member
Joined
Sep 8, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello!
I've searched high and low across multiple forums for this specific answer, but I can't seem to find it based on cell value. I can find the VBA code for ALL worksheet names to be listed in the ListBox, but not specific ones based on cell value to be used as selections for printing.

Here's what I have so far:

I have 2 Comboboxes within my worksheet, which are populated by a named range.
- cbAccountType listfillrange = Account_Type, with linkedcell of A2 for selection
-cbServiceSchedule listfillrange = Service_Schedule with linkedcell of A3 for selection

I then use the Filter function within A4 to populate the appropriate worksheet names
=IF(OR($A$2="Select",$A$3="Select"),"",FILTER(Lists!BO:BO,(Lists!BM:BM=$A$3)*(Lists!BN:BN=$A$4),"")) (a range is found on the Lists worksheet within columns BM:BO)
This filtered range is then named "Worksheets"

From there, I have Listbox1 which uses the listfillrange of "Worksheets". Listbox1 is also setup to MultiSelectExtended to be able to select multiple sheets

This all works perfectly until I want to print preview the appropriate sheets that are selected. I had found VBA code to use on a Command Button when clicked, but it's for all worksheets within the workbook, which I don't want. For reference, this is what I used to pull all worksheet names into the Listbox and print from there:

VBA Code:
Sub PrintForms()

    Dim i As Long, c As Long
    Dim SheetArray() As String
    
    With ActiveSheet.ListBox1
        
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
            End I
        Next i
    End With
Sheets(SheetArray()).PrintPreview

End Sub

How can I change this code to recognize the selected items in my Listbox, that was populated from a named range, and select those worksheets to combine into a single document for Print Preview?

Sorry if I missed some pertinent information. I'm fairly new at posting within the boards. Let me know if you need any additional info!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I am lost with your explanation.

What do you really have in your listbox?
Do you have the names of the sheets in the book?
That is, if in your book you have the sheets "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6" in the listbox I will see something like this:

1691631109871.png


If the above is correct, the macro you put in your initial post works for the sheets selected in the listbox.

Or what error does the macro send you or what does it show you in the printpreview?


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0

Forum statistics

Threads
1,223,289
Messages
6,171,233
Members
452,391
Latest member
BHG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top