Count elements in array

[Jason Morin]

Hello and TIA. Users input a string into a InputBox in
the #,# format. For example:

SheetsToPrint = "1,3,8,14"

This indicates which sheets (index #) they want to print.
The macro is printing but I'm having issues determing the
total elements in my array, and thus all sheets selected
won't print:

SheetsToPrintSplit = Split(SheetsToPrint, ",")

For iCounter = 0 To UBound(SheetsToPrintSplit)
Sheets(CInt(SheetsToPrintSplit(iCounter))).PrintOut
iCounter = iCounter + 1
Next

 

[Bob Phillips]

That looks okay. What is (not) happening?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Thanks Bob. If I input "1,2,3,4", it only prints the
first and third sheet. If I do "3,5,6", it only prints
the third and sixth (there are 6 total). Here's all the
code:

Option Explicit
Sub PrintSheets()
Dim ws As Worksheet
Dim SelectSheets() As String
Dim InputMsg As String
Dim InputSheetList As String
Dim SheetsToPrint As String
Dim SheetsToPrintSplit As Variant
Dim TotalSheets As Integer
Dim iCounter As Integer
Dim SheetNdx As Integer

InputMsg = "Enter the number of the sheets you " & _
"wish to print. Separate the numbers by a comma. " &
vbCrLf & vbCrLf & _
"For example: 1,2,5,8,14" & vbCrLf & vbCrLf

TotalSheets = ActiveWorkbook.Worksheets.Count
ReDim SelectSheets(TotalSheets - 1)

SheetNdx = 1
For Each ws In ActiveWorkbook.Worksheets
SelectSheets(iCounter) = SheetNdx & ": " & ws.Name
iCounter = iCounter + 1
SheetNdx = SheetNdx + 1
Next

For iCounter = 0 To ActiveWorkbook.Worksheets.Count - 1
InputSheetList = InputSheetList & SelectSheets
(iCounter) & vbCrLf
Next

SheetsToPrint = InputBox(InputMsg &
InputSheetList, "Print:")
If SheetsToPrint = "" Then Exit Sub

SheetsToPrintSplit = Split(SheetsToPrint, ",")

'On Error GoTo EndMacro
iCounter = 0
For iCounter = 0 To UBound(SheetsToPrintSplit)
Sheets(CInt(SheetsToPrintSplit(iCounter))).PrintOut
iCounter = iCounter + 1
Next
Exit Sub

'EndMacro:
'MsgBox "Sorry, there was an error."

End Sub

 

[Tom Ogilvy]

SheetsToPrintSplit = Split(SheetsToPrint, ",")

For iCounter = 0 To UBound(SheetsToPrintSplit)
Sheets(Clng(SheetsToPrintSplit(iCounter))).PrintOut
Next


You don't need to increment icounter.

 

[Tom Ogilvy]

I say again, don't increment the loop counter inside the loop.

 

[Bob Phillips]

Sorry Jason,

Wood for the trees.

In your print loop, you also increment the counter. There is no need, a loop
does it automatically.

Remove the line

iCounter = iCounter + 1

You might also want to take a look at this alternative.



Sub PrintSheets()
Const nPerColumn As Long = 35 'number of items per­ column
Const nWidth As Long = 7 'width of each lette­r
Const nHeight As Long = 18 'height of each row
Const sID As String = "___WorksheetPrint" 'name of dialog shee­t
Const kCaption As String = " Select worksheets to print"
'dialog caption


Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cLeft As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox

Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For i = 1 To ActiveWorkbook.Worksheets.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.ActiveSheet
cLetters = Len(ActiveWorkbook.Worksheets(i).Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = ActiveWorkbook.Worksheets(i).Name
TopPos = TopPos + 13

Next i

.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.CheckBoxes
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).PrintOut
End If
Next cb
Else
MsgBox "No sheets selected"
End If
Application.DisplayAlerts = False

.Delete

End With

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Bob/Tom-
Thanks for the help. I had been staring at the code too
long too realize my error. Bob, thanks for that code on
creating the userform from scratch each time. I'll study
it.
Jason

 

[Bob Phillips]

Jason,

It's a dialog, not a userform, the old pre-97 type of forms.

Got it originally from John Walkenbach.

Regards

Bob

Bob/Tom-
Thanks for the help. I had been staring at the code too
long too realize my error. Bob, thanks for that code on
creating the userform from scratch each time. I'll study
it.
Jason