How To Omit Specific Worksheets From ActiveX Combo Box List

[bearwires]

I have managed to wangle together this VBA code to select & print preview certain worksheets from the workbook. It works great, but I want to exclude certain sheets from the list.

I am hoping some VBA guru here can help me out please?

Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String

 'ERROR HANDLING
 Dim errMsg As String
On Error GoTo errHandler
 errMsg = "You Need To Select RAMS & COSHH To Print"
errHandler:
    MsgBox errMsg
    Exit Sub
'
       With ActiveSheet.ListBoxSh
           
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                        c = c + 1
            End If
        Next i

        Sheets(SheetArray()).PrintPreview

    End With

End Sub

I want to exclude the following list of worksheets and make sure there are no blank items in the ActiveX combo box print list which is located on the Project Details worksheet:

• Project Details
• RA
• Lists
• COSHH Assessments

Any help will be much appreciated.

Thanks

 

[bearwires]

I have managed to wangle together this VBA code to select & print preview certain worksheets from the workbook. It works great, but I want to exclude certain sheets from the list.

I am hoping some VBA guru here can help me out please?

Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String

 'ERROR HANDLING
 Dim errMsg As String
On Error GoTo errHandler
 errMsg = "You Need To Select RAMS & COSHH To Print"
errHandler:
    MsgBox errMsg
    Exit Sub
'
       With ActiveSheet.ListBoxSh
         
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                        c = c + 1
            End If
        Next i

        Sheets(SheetArray()).PrintPreview

    End With

End Sub

I want to exclude the following list of worksheets and make sure there are no blank items in the ActiveX combo box print list which is located on the Project Details worksheet:

• Project Details
• RA
• Lists
• COSHH Assessments

Any help will be much appreciated.

Thanks

Sorry, pasted the wrong version of the vba code.
This is the correct version I need assistance with:

Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String
Dim errMsg As String
errMsg = "You Need To Select RAMS & COSHH To Print"

On Error GoTo errHandler


    With ActiveSheet.ListBoxSh

        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
            End If
        Next i

        Sheets(SheetArray()).PrintPreview

    End With


'ERROR HANDLING

Done:
    Exit Sub

'
errHandler:
    MsgBox errMsg

End Sub

 

[bearwires]

Sorry, pasted the wrong version of the vba code.
This is the correct version I need assistance with:

Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String
Dim errMsg As String
errMsg = "You Need To Select RAMS & COSHH To Print"

On Error GoTo errHandler


    With ActiveSheet.ListBoxSh

        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
            End If
        Next i

        Sheets(SheetArray()).PrintPreview

    End With


'ERROR HANDLING

Done:
    Exit Sub

'
errHandler:
    MsgBox errMsg

End Sub

I got this to work by specifying actual worksheets I wanted included rather than trying to omit what I didnt want from the whole workbook.
This is for a combo box with specified worksheets listed which can be used for quick print previewing using a macro button.

This is the methodology:

In VBA editor, On Worksheet code module where Combo Box is Located:

Private Sub Worksheet_Activate()
'
Dim Sh As Worksheet
Set ws = Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3"))     [I]'Add  as many sheets as you want in this array to show in the combo box[/I]
'
Me.ListBoxSh.Clear
'
For Each Sh In ws
'For Each Sh In ThisWorkbook.ws

    Me.ListBoxSh.AddItem Sh.Name

Next Sh
'

End Sub

Then, I created a separate module and used this VBA code and allocated it to a Printer icon macro button:

Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String
Dim errMsg As String
errMsg = "[I]Your Error Message Goes Here[/I]"

On Error GoTo errHandler


    With ActiveSheet.ListBoxSh



            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
            End If
        Next i

        Sheets(SheetArray()).PrintPreview

    End With


'ERROR HANDLING

Done:
    Exit Sub

'
errHandler:
    MsgBox errMsg

End Sub