Printing Assembled Array Using Sheet Names
 

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

Printing Assembled Array Using Sheet Names
 

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

Printing Assembled Array Using Sheet Names
 

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

Printing Assembled Array Using Sheet Names
 

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

Printing Assembled Array Using Sheet Names
 

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

Printing Assembled Array Using Sheet Names
 

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

Printing Assembled Array Using Sheet Names
 

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

Printing Assembled Array Using Sheet Names
 

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

Printing Assembled Array Using Sheet Names
 

Thanks, it worked great! One other question, if you don't mind. I am running
this from multiple different locations (looking at multiple different
sheets). When I print to the MS Document Writer in some cases it will group
all of the selected sheets into one print job and in other cases it will save
each sheet in the array as a separate file. Do you know how to control this?
I was hoping to combine all of the individual sheets in the array to be one
file/jobe.

Thanks again. You are a lifesaver!



"Dave Peterson" wrote:

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

Printing Assembled Array Using Sheet Names
 

I don't think I've ever used the MS Document Writer--and in my simple tests
(with print preview), all the sheets I printed went as one job.

Sorry, I don't have a guess.

===
You are replacing this line:

Sheets(PrintArray).Select
with
Sheets(PrintArray).printout 'preview:=true

right?

Troubled User wrote:

Thanks, it worked great! One other question, if you don't mind. I am running
this from multiple different locations (looking at multiple different
sheets). When I print to the MS Document Writer in some cases it will group
all of the selected sheets into one print job and in other cases it will save
each sheet in the array as a separate file. Do you know how to control this?
I was hoping to combine all of the individual sheets in the array to be one
file/jobe.

Thanks again. You are a lifesaver!

"Dave Peterson" wrote:

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


--

Dave Peterson

Printing Assembled Array Using Sheet Names
 

Thanks for all your help! I will make a separate post.

"Dave Peterson" wrote:

I don't think I've ever used the MS Document Writer--and in my simple tests
(with print preview), all the sheets I printed went as one job.

Sorry, I don't have a guess.

===
You are replacing this line:

Sheets(PrintArray).Select
with
Sheets(PrintArray).printout 'preview:=true

right?

Troubled User wrote:

Thanks, it worked great! One other question, if you don't mind. I am running
this from multiple different locations (looking at multiple different
sheets). When I print to the MS Document Writer in some cases it will group
all of the selected sheets into one print job and in other cases it will save
each sheet in the array as a separate file. Do you know how to control this?
I was hoping to combine all of the individual sheets in the array to be one
file/jobe.

Thanks again. You are a lifesaver!

"Dave Peterson" wrote:

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


--

Dave Peterson