How to select some sheets to print with VBA (Based on a user's choice)

[Pierre Archambault]

Hi,

I wish to give the user the opportunity to choose the sheets he wants to
print via a listbox in a userform.

I recorded a macro to see what was generated but it gives nothing but a
string of sheet names separated by commas.

Sheets(Array("Title Page", "Deployment", "Other Cots")).Select
Sheets("Title Page").Activate

With my code, I managed to reproduced the string exactly as it appeared in
the recorded macro (including the imbedded quotation marks and commas) but I
receive an error when I try to run the code. "Index does'nt belong to
selection".

Can somebody help me please.

Thanks

Pierre

 

[Tom Ogilvy]

I suspect you are doing something like this
sStr = """Title Page"", ""Deployment"", ""Other Cots"""
Sheets(Array(sStr).Select

But that will give a subscript out of range which is probably what you mean
by index does not belong to selection.

Sub Tester2()
Dim varr as Variant
Dim sStr as String
sStr = """Title Page"", ""Deployment"", ""Other Cots"""
varr = Evaluate("{" & sStr & "}")
Sheets(varr).Select
End Sub


would work for a small number of sheets.

 

[Bernie Deitrick]

Pierre,

Try the code below, which shows how to select multiple sheets (commented
out) or simply print multiple sheets from a comma delimited string of sheet
names.

HTH,
Bernie
MS Excel MVP

Sub test()
Dim mySheets As Variant
Dim i As Integer
Dim SelectedSheets As String

'This could be set to your returned string....the one without the internal
quote marks
SelectedSheets = "Sheet1,Sheet2,Sheet3"
mySheets = Split(SelectedSheets, ",")

''To select the sheets
'Sheets(mySheets(0)).Select
'For i = 1 To UBound(mySheets)
'Sheets(mySheets(i)).Select False
'Next i

'To Print the sheets
For i = 0 To UBound(mySheets)
Sheets(mySheets(i)).PrintPreview
Next i
End Sub