Printing multiple pages based on values in ActiveX ComboBox

[ivanlost]

Hi!

I have ActiveX combo box populated from other sheet. Based on value in combo box some cells in active sheet (where combo box is) are populated. There is over 100 values in Combo Box. Question is is it possible to print pages with populated cells based on combo box value with one click on button?

 

[Logit]

Using other control ...

Option Explicit

Private Sub PrintButton1_Click()
Dim i As Integer
Dim TotalPages As Integer
Dim CurrentPage As Integer
Dim CheckBox3 As Object
'Close userform
UserForm1.Hide

'Count Pages
TotalPages = 0
CurrentPage = 1

With ListBox1
  For i = 0 To .ListCount - 1
    If .Selected(i) Then TotalPages = TotalPages + 1
  Next i
End With


With ListBox1
  For i = 0 To .ListCount - 1
    If .Selected(i) Then
      Worksheets(.List(i)).Activate
         
      If CheckBox1 Then
        ActiveSheet.PrintPreview 'for debugging
      Else
        ActiveSheet.PrintOut 'Printout
      End If
    End If
  Next i
 
End With
Sheets("Sheet1").Select
End Sub

Private Sub CommandButton2_Click()
  UserForm1.Hide
End Sub

Sub UserForm_Activate()

Dim i As Integer
Dim PL As String, PS As String
Dim SheetCount As Integer
Dim CurrentSheet As Worksheet
Application.ScreenUpdating = False

'Setup Userform
ListBox1.Clear
CheckBox1.Value = False
Label2.Caption = Application.ActivePrinter

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
  MsgBox "Workbook is protected.", vbCritical
  Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet

SheetCount = 0

Dim strArray() As Variant
ReDim strArray(1 To ActiveWorkbook.Worksheets.Count, 1 To 3)

' Add the items to the listbox
For i = 1 To ActiveWorkbook.Worksheets.Count
  Set CurrentSheet = ActiveWorkbook.Worksheets(i)
 
  ' Skip empty sheets and hidden sheets
  If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then
    SheetCount = SheetCount + 1
    strArray(i, 1) = CurrentSheet.Name
   
    Select Case CurrentSheet.PageSetup.PaperSize
    Case 1
      PS = "Letter"
    Case 2
      PS = "Letter Small"
    Case 3
      PS = "Tabloid"
    Case 4
      PS = "Ledger"
    Case 5
      PS = "Legal"
    Case 6
      PS = "Statement"
    Case 8
      PS = "A3"
    Case 9
      PS = "A4"
    Case 11
      PS = "A5"
    Case Else
      PS = "A4"
      CurrentSheet.PageSetup.PaperSize = xlPaperA4
    End Select
    strArray(i, 2) = PS
   
    'PL = "Landscape"
    CurrentSheet.PageSetup.Orientation = xlLandscape
 
    'If CurrentSheet.PageSetup.Orientation = 1 Then
    '  PL = "Landscape"
    'Else
    '  PL = "Landscape"
    'End If
    'strArray(i, 3) = PL
   
  End If
Next i

'Load the Listbox
ListBox1.List = strArray

End Sub

Private Sub CheckBox2_Click()
  Call Toggle_Selections
End Sub

Function Toggle_Selections()
Dim i As Integer

'Reverse all Listbox Selections
With ListBox1
  For i = 0 To .ListCount - 1
    .Selected(i) = Not (.Selected(i))
  Next
End With
End Function

Download : Internxt Drive

 

[ivanlost]

Using other control ...

Option Explicit

Private Sub PrintButton1_Click()
Dim i As Integer
Dim TotalPages As Integer
Dim CurrentPage As Integer
Dim CheckBox3 As Object
'Close userform
UserForm1.Hide

'Count Pages
TotalPages = 0
CurrentPage = 1

With ListBox1
  For i = 0 To .ListCount - 1
    If .Selected(i) Then TotalPages = TotalPages + 1
  Next i
End With


With ListBox1
  For i = 0 To .ListCount - 1
    If .Selected(i) Then
      Worksheets(.List(i)).Activate
        
      If CheckBox1 Then
        ActiveSheet.PrintPreview 'for debugging
      Else
        ActiveSheet.PrintOut 'Printout
      End If
    End If
  Next i
 
End With
Sheets("Sheet1").Select
End Sub

Private Sub CommandButton2_Click()
  UserForm1.Hide
End Sub

Sub UserForm_Activate()

Dim i As Integer
Dim PL As String, PS As String
Dim SheetCount As Integer
Dim CurrentSheet As Worksheet
Application.ScreenUpdating = False

'Setup Userform
ListBox1.Clear
CheckBox1.Value = False
Label2.Caption = Application.ActivePrinter

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
  MsgBox "Workbook is protected.", vbCritical
  Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet

SheetCount = 0

Dim strArray() As Variant
ReDim strArray(1 To ActiveWorkbook.Worksheets.Count, 1 To 3)

' Add the items to the listbox
For i = 1 To ActiveWorkbook.Worksheets.Count
  Set CurrentSheet = ActiveWorkbook.Worksheets(i)
 
  ' Skip empty sheets and hidden sheets
  If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then
    SheetCount = SheetCount + 1
    strArray(i, 1) = CurrentSheet.Name
  
    Select Case CurrentSheet.PageSetup.PaperSize
    Case 1
      PS = "Letter"
    Case 2
      PS = "Letter Small"
    Case 3
      PS = "Tabloid"
    Case 4
      PS = "Ledger"
    Case 5
      PS = "Legal"
    Case 6
      PS = "Statement"
    Case 8
      PS = "A3"
    Case 9
      PS = "A4"
    Case 11
      PS = "A5"
    Case Else
      PS = "A4"
      CurrentSheet.PageSetup.PaperSize = xlPaperA4
    End Select
    strArray(i, 2) = PS
  
    'PL = "Landscape"
    CurrentSheet.PageSetup.Orientation = xlLandscape
 
    'If CurrentSheet.PageSetup.Orientation = 1 Then
    '  PL = "Landscape"
    'Else
    '  PL = "Landscape"
    'End If
    'strArray(i, 3) = PL
  
  End If
Next i

'Load the Listbox
ListBox1.List = strArray

End Sub

Private Sub CheckBox2_Click()
  Call Toggle_Selections
End Sub

Function Toggle_Selections()
Dim i As Integer

'Reverse all Listbox Selections
With ListBox1
  For i = 0 To .ListCount - 1
    .Selected(i) = Not (.Selected(i))
  Next
End With
End Function

Download : Internxt Drive

For some reason it won't work... I have ComboBox.
Correct me if I'm wrong (most probably I am). This code is for macro which I should connect to button?

 

[Logit]

Yes, create a CommandButton on your worksheet, connected to a macro that opens the user form. You can place the following macro into a MODULE in the VBE where the
UserForm is located :

Sub Printer()
  UserForm1.Show
End Sub

You can look at the sample download file to see the locations.
Then, when you click on the command button, the UserForm displays and you can progress from there. Understand this project uses a ListBox on the UserForm to select which sheets you want printed.