Using a String for a Print Array

[Jamie2002]

Hi.

I've made a userform where you click on the pages(sheets) you wish to print.

I have created a strtemp as string that makes up the print range......

Then by including this in a select function want to pick up the selected sheets...

Sheets(Array(StrTemp)).Select

But I'm getting the error message

Run-Time Error 9
Subscript out of range

Please help.

Thanks

 

[ide]

StrTemp is a string typed variable, I presume.
Use:

Sheets(StrTemp).Select

If StrTemp is an array of strings..., here a code to understand...:

Dim strSheets(1) As String
Dim vTemp() As Variant

strSheets(0) = "Sheet1"
strSheets(1) = "Sheet3"

ReDim vTemp(UBound(strSheets)) 'set the size of vTemp (max index) to the size of strSheets

For i = 0 To UBound(strSheets)
If strSheets(i) <> &quot;&quot; Then
vTemp(i) = strSheets(i)
End If
Next i

Sheets(vTemp).Select


Sheets(<parameter>)
<parameter> must be a number (index of one sheet), a string (name of one sheet), or a variant typed array
..if i'm right

ide

 

[Jamie2002]

This is my code below......

First I count how many check boxes are ticked this is just for making sure the &quot; are correct around the whole string. Then taking each in turn I say if checkbox is true then put the sheet name in strtemp....

....next checkbox...the same and add name to strtemp so it builds up to a string, eg &quot;Cover&quot;, &quot;Contents&quot; but then at the bottom - Sheets(strtemp).Select produces an error...

Thanks for any help.


Private Sub PrintButton_Click()

Dim CheckCounter As Integer
Dim No1 As Integer
Dim No2 As Integer
Dim No3 As Integer
Dim No4 As Integer

CheckCounter = 0
No1 = 0
No2 = 0
No3 = 0
No4 = 0

Dim CheckCounter2 As Integer
Dim StrTemp As String

CheckCounter2 = 0
StrTemp = &quot;&quot;

If CheckBox1.Value = True Then
No1 = 1
End If
If CheckBox2.Value = True Then
No2 = 1
End If
If CheckBox3.Value = True Then
No3 = 1
End If
If CheckBox4.Value = True Then
No4 = 1
End If


CheckCounter = No1 + No2 + No3 + No4

If CheckCounter <> 0 Then




If CheckBox1.Value = True Then
CheckCounter2 = CheckCounter2 + 1
If CheckCounter2 <= CheckCounter Then
If Len(StrTemp) = 0 Then
StrTemp = &quot;Cover&quot;
If CheckCounter2 <> CheckCounter Then
StrTemp = StrTemp & &quot;&quot;&quot;&quot;
End If
Else: StrTemp = &quot;, &quot; & &quot;&quot;&quot;&quot; & &quot;Cover&quot;
If CheckCounter2 <> CheckCounter Then
StrTemp = StrTemp & &quot;&quot;&quot;&quot;
End If
End If
End If
End If

If CheckBox2.Value = True Then
CheckCounter2 = CheckCounter2 + 1
If CheckCounter2 <= CheckCounter Then
If Len(StrTemp) = 0 Then
StrTemp = &quot;Contents&quot;
If CheckCounter2 <> CheckCounter Then
StrTemp = StrTemp & &quot;&quot;&quot;&quot;
End If
Else: StrTemp = StrTemp & &quot;, &quot; & &quot;&quot;&quot;&quot; & &quot;Contents&quot;
If CheckCounter2 <> CheckCounter Then
StrTemp = StrTemp & &quot;&quot;&quot;&quot;
End If
End If
End If
End If

If CheckBox3.Value = True Then
CheckCounter2 = CheckCounter2 + 1
If CheckCounter2 <= CheckCounter Then
If Len(StrTemp) = 0 Then
StrTemp = &quot;Summary&quot;
If CheckCounter2 <> CheckCounter Then
StrTemp = StrTemp & &quot;&quot;&quot;&quot;
End If
Else: StrTemp = StrTemp & &quot;, &quot; & &quot;&quot;&quot;&quot; & &quot;Summary&quot;
If CheckCounter2 <> CheckCounter Then
StrTemp = StrTemp & &quot;&quot;&quot;&quot;
End If
End If
End If
End If

If CheckBox4.Value = True Then
CheckCounter2 = CheckCounter2 + 1
If CheckCounter2 <= CheckCounter Then
If Len(StrTemp) = 0 Then
StrTemp = &quot;KPI's&quot;
If CheckCounter2 <> CheckCounter Then
StrTemp = StrTemp & &quot;&quot;&quot;&quot;
End If
Else: StrTemp = StrTemp & &quot;, &quot; & &quot;&quot;&quot;&quot; & &quot;KPI's&quot;
If CheckCounter2 <> CheckCounter Then
StrTemp = StrTemp & &quot;&quot;&quot;&quot;
End If
End If
End If
End If

TextBox1.Value = StrTemp

Sheets(StrTemp).Select

MsgBox &quot;Selected report now printing....&quot;, vbInformation + vbOKOnly, &quot;PRINT STATUS&quot;

Else: MsgBox &quot;You have not selected any pages to print&quot;, vbInformation + vbOKOnly, &quot;PRINT STATUS&quot;

End If
End Sub

 

[ide]

ohh! i'm an idiot!

in StrTemp variable (if it's a plain string) isn't the proper string. How do you yield the value?

pls insert a line:
msgbox StrTemp

before the
Sheets(Array(StrTemp)).Select
line, and check the value.

 

[Jamie2002]

The value comes back looking something like.....

Cover&quot;, &quot;Contents&quot;, &quot;Summary

missing the &quot; off the start and end because a string puts these on anyway.

When you run the code the (Array(strTemp)) is properly quoted and seperated but it just wont have it ?

 

[Jamie2002]

As well if you only select one sheet to print......it works.....!

 

[ide]

something like this; I simplified the value adding with a new fnAddArData sub:


Private Sub PrintButton_Click()

Dim strTemp() As Variant
ReDim strTemp(0)
'...
If CheckCounter <> 0 Then

If CheckBox1.Value = True Then
CheckCounter2 = CheckCounter2 + 1
If CheckCounter2 <= CheckCounter Then
Call fnAddArData(strTemp, &quot;Cover&quot
End If
End If


If CheckBox2.Value = True Then
CheckCounter2 = CheckCounter2 + 1
If CheckCounter2 <= CheckCounter Then
Call fnAddArData(strTemp, &quot;Contents&quot
End If
End If

'...
End If
'...
End Sub
Sub fnAddArData(arWork As Variant, strVal As String)
'append value to the end of variant typed arWork array
If UBound(arWork) = 0 And IsEmpty(arWork(0)) Then
arWork(0) = strVal
Else
ReDim Preserve arWork(UBound(arWork) + 1)
arWork(UBound(arWork)) = strVal
End If
End Sub



i hope it helps (and works
ide