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

Sorry, the error I get is Subscript out of range at the second to last row of
code

" Sheets(Array(PrintArray)).Select"

However, if you hover it shows that PrintArray = "SheetNumber1.Name" just as
I wanted.


"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

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

Thank you Dave. I will try in the morning and let you know how it works.



"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

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

If you're getting a subscript out of range error, then there is no worksheet in
the activeworkbook that has that name.

You may want to post the code you're using--and explain what sht and
testname.name are.

Troubled User wrote:

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


--

Dave Peterson

First, thanks SO much for your help. I know this is very close.

The string that is being built looks to be fine, but when it is passed to
the sheet function it doesn't appear to convert it. I have stripped this
down to only trying to evaluate one checkbox, generate the string and pass to
the select function.

I still get the subscript out of range error. Here is what I have:

In a test workbook I have one three sheets. Sheet (1) has been given a
friendly name of "Test Sheet" and a (Name) in VB of shtTestSheet. All sheets
are visible and there is no protection.

One user form called "UserForm1" with a checkbox control called "TestCheckbox"

I open the UserForm1 and press a button that calls the following code:

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.TestCheckbox = True Then
myStr = myStr & "," & "shttestsheet.name"
End If

MsgBox (myStr)

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

myStr = Mid(myStr, 2)

PrintArray = Split(myStr, ",")

Sheets(PrintArray).Select

End Sub

The message box shows that the string looks correct before it is split.
Then the subscript error.

Thanks.













"Dave Peterson" wrote:

If you're getting a subscript out of range error, then there is no worksheet in
the activeworkbook that has that name.

You may want to post the code you're using--and explain what sht and
testname.name are.

Troubled User wrote:

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


--

Dave Peterson

This is what I used:

If Me.CheckBox1 = True Then
myStr = myStr & "," & Sheet1.Name
End If

You added quotes around the name:

If Me.TestCheckbox = True Then
myStr = myStr & "," & "shttestsheet.name"
End If

You use a couple more double quotes than I do.

Troubled User wrote:

First, thanks SO much for your help. I know this is very close.

The string that is being built looks to be fine, but when it is passed to
the sheet function it doesn't appear to convert it. I have stripped this
down to only trying to evaluate one checkbox, generate the string and pass to
the select function.

I still get the subscript out of range error. Here is what I have:

In a test workbook I have one three sheets. Sheet (1) has been given a
friendly name of "Test Sheet" and a (Name) in VB of shtTestSheet. All sheets
are visible and there is no protection.

One user form called "UserForm1" with a checkbox control called "TestCheckbox"

I open the UserForm1 and press a button that calls the following code:

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.TestCheckbox = True Then
myStr = myStr & "," & "shttestsheet.name"
End If

MsgBox (myStr)

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

myStr = Mid(myStr, 2)

PrintArray = Split(myStr, ",")

Sheets(PrintArray).Select

End Sub

The message box shows that the string looks correct before it is split.
Then the subscript error.

Thanks.

"Dave Peterson" wrote:

If you're getting a subscript out of range error, then there is no worksheet in
the activeworkbook that has that name.

You may want to post the code you're using--and explain what sht and
testname.name are.

Troubled User wrote:

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


--

Dave Peterson


--

Dave Peterson