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 said:
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.
:
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
