Dave,

Everything in this looks good until it runs the final select command
[Sheets(PrintArray).select ]. The array using the .name(s) is perfect but
then I get the Subscript out of Range Error. My actual sheet names (that I
am testing is "sht
TestSheet.name" with corresponding on sheet name of Sheet("Test Sheet"). It
is visible and unprotected and when I typed it in the code I typed it in all
lower case and it converted it to upper/lower camel case.

I have tested this on multiple different sheets (renaming and starting from
scratch) and I can't get this select to work. Any thoughts?

Thank you.


"Dave Peterson" wrote:

Your printarray contains a longgggggg string--it's not an array.

It's the equivalent of using:

Dim myArr as variant
myArr = Array("""this"",""is a"",""long"",""string""")

This doesn't make it an array with 4 elements--it's just an array with that
single element:

"this","is a","long","string"

=======
You have a couple of choices.

You can build the string you want, then create the array using Split (xl2k and
higher):

Option Explicit
Private Sub CommandButton1_Click()

Dim myStr As String
Dim PrintArray As Variant

'Set to string to blank
myStr = ""

'Check the individual values for true
If Me.CheckBox1 = True Then
myStr = myStr & "," & Sheet1.Name
End If

If Me.CheckBox2 = True Then
myStr = myStr & "," & Sheet2.Name
End If

If Me.CheckBox3 = True Then
myStr = myStr & "," & Sheet3.Name
End If

If myStr = "" Then
MsgBox "nothing checked"
Exit Sub
End If

myStr = Mid(myStr, 2)

PrintArray = Split(myStr, ",")

'printarray is already an array--don't wrap it in array(printarray)
Sheets(PrintArray).Select

End Sub

Or you could build the array when you find a checkbox that's checked.

Option Explicit
Private Sub CommandButton1_Click()

Dim PrintArray() As String
Dim pCtr As Long

'big enough to hold all the sheets in the workbook
ReDim PrintArray(1 To Me.Parent.Sheets.Count)

pCtr = 0
'Check the individual values for true
If Me.CheckBox1 = True Then
pCtr = pCtr + 1
PrintArray(pCtr) = Sheet1.Name
End If

If Me.CheckBox2 = True Then
pCtr = pCtr + 1
PrintArray(pCtr) = Sheet2.Name
End If

If Me.CheckBox3 = True Then
pCtr = pCtr + 1
PrintArray(pCtr) = Sheet3.Name
End If

If pCtr = 0 Then
MsgBox "nothing checked"
Exit Sub
End If

'remove any "unused sheets"
ReDim Preserve PrintArray(1 To pCtr)

Sheets(PrintArray).Select

End Sub



Troubled User wrote:

I have a VB sheet that has multiple (in the example below I have 3) different
check boxes. When the user checks the different boxes and then presses a
button on the form I want to assemble the different Sheets (named as
ShtNumber1, ShtNumber2 and ShtNumber3) below into a print array. I have
tried this multiple ways and can get it to work if it is hard-coded to the
equivalent of this assembled array, but it will not print.

Please notice that I am appending a ", " on the end of each selected sheet
name, so at the end of this code I trim it off of the end .

Any help or better way to accomplish this would be greatly appreciated.
Thank you.

Dim PrintArray As String
Dim ArrayLength As Integer

'Set to array to blank
PrintArray = ""

'Check the individual values for true
' If CheckBox1 = True Then
PrintArray = PrintArray & "ShtNumber1.Name, "
' End If

' If CheckBox2 = True Then
PrintArray = PrintArray & "ShtNumber2.Name, "
' End If

' If CheckBox3 = True Then
' PrintArray = PrintArray & "ShtNumber2.Name, "
' End If

ArrayLength = Len(PrintArray)

If ArrayLength 2 Then

€˜Gets rid of comma and space

PrintArray = Left(PrintArray, ArrayLength - 2)

Else

End If

Sheets(Array(PrintArray)).Select

ActiveWindow.SelectedSheets.PrintOut

--

Dave Peterson