Populating Two Listboxes with Sheets Based on Cell Value & Printing Their Selection

**TBrooker** · 07-29-2009, 06:05 AM

Hello, once again I have hit a brick wall (I seem to do it a lot).

What I would like to do is have 2 listboxes. In the first listbox I would like the name of all the worksheets which contain the words "elective class: " in cell C7.

In the second listbox I would like the name of all the worksheets which do not contain the words "elective class: " in C7. I need this only to source from worksheet 7 onwards however.

The listboxes are called ListBox1 and ListBox2 respectively.

Also I am using this to print and I have a button which currently selects all the data in the first listbox and another button which prints all the selected data. The code being used for this is:

Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 If ListBox1.Selected(iloop - 1) = True Then
   Sheets(ListBox1.List(iloop - 1, 0)).PrintOut
   ListBox1.Selected(iloop - 1) = False
 End If
 Next

What would I need to change this to to print anything selected in either listbox?

Finally out of interest since I am doing all of this to learn more than anything, is there a way to define which printer to print to so that even if the default printer is for example a non-colour one. Pressing a button saying perhaps "print colour" would always print to the colour printer?

Thanks always,

Tom

**TBrooker** · 07-29-2009, 08:28 AM

A few codes I have tried so far but have not worked are:

Private Sub UserForm_Initialize()
    Dim i As Integer
    ListBox1.Clear
    i = 1
    For i = 7 To ActiveWorkbook.Sheets.Count
        Sheets(i).Select
        If Range("C7").Text = "elective class: " Then
            ListBox1.AddItem Sheets(i).Name
        ElseIf Range("C7").Text = "" Then
            ListBox2.AddItem Sheets(i).Name
        End If
    Next
    i = 0
End Sub

Dim sSheet

For Each sSheet In Sheets
If LCase(sSheet.Range("C7").Value) = "elective class: " Then
            ListBox1.AddItem sSheet.Name
ElseIf LCase(sSheet.Range("C7").Value) = "" Then 
            ListBox2.AddItem sSheet.Name
        End If
    Next sSheet

Also have done a couple variations of the second of the two codes including counters and an index to start the population from the seventh worksheets along but I had no luck.

I am guessing I have made a basic error or if there is a completely different formula which would work better, then I am more than happy with that also. I'll probably turn screen updating off for both of these too.

For the printing which I haven't had a chance to test yet as I haven't populated both listboxes this is the code I have put in:

Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 If ListBox1.Selected(iloop - 1) = True Then
 Sheets(ListBox1.List(iloop - 1, 0)).PrintOut
 ListBox1.Selected(iloop - 1) = False Then

For iloop = 1 To ListBox2.ListCount
 If ListBox2.Selected(iloop - 1) = True Then
 Sheets(ListBox2.List(iloop - 1, 0)).PrintOut
 ListBox2.Selected(iloop - 1) = False
          EndIf
Next

I'm pretty sure its wrong...

Many thanks,

Tom

**TBrooker** · 08-14-2009, 03:45 AM

In case anybody wanted to do similar I have now worked out how to do what I wanted, the code used is as follows:

Populating the Listboxes

Private Sub UserForm_Initialize()
    Dim i As Integer
    Application.ScreenUpdating = False
    i = 1
    For i = 7 To ActiveWorkbook.Sheets.Count
        Sheets(i).Select
        If Range("C7").Text = "elective class: " Then
            ListBox1.AddItem Sheets(i).Name
        Else
            ListBox2.AddItem Sheets(i).Name
        End If
    Next
    i = 0
    Application.ScreenUpdating = True
End Sub

Quite an easy code to modify if need be.

To add a second condition instead of just adding any worksheets without a value in cell C7 you can change the Else line to

ElseIf Range("C7").Text = "something: " Then

The line ListBox1.Clear caused the problem if you were using it in the initialization however if you wanted a refresh button this and ListBox2.Clear are the lines you can add to code for one.

Printing

For printing from both listboxes the code used is:

Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 If ListBox1.Selected(iloop - 1) = True Then
   Sheets(ListBox1.List(iloop - 1, 0)).PrintOut
   ListBox1.Selected(iloop - 1) = False
 End If
 Next
 For iloop = 1 To ListBox2.ListCount
 If ListBox2.Selected(iloop - 1) = True Then
   Sheets(ListBox2.List(iloop - 1, 0)).PrintOut
   ListBox2.Selected(iloop - 1) = False
 End If
 Next

Tom