Macro to print multiple sheets

[queenstarks]

I have a spreadsheet set up with multiple worksheets. I have one worksheet (Form tab) created that will pull data from the other sheets that I want to print. Have macros set up to gather sheets which put the sheet names in a combobox and to printform. Can't get the print feature to work correctly. It only prints the sheet that's selected but it's not cycling properly. Any assistance someone can provide will be greatly appreciated. Not sure what's going wrong. Same code I use every year.

Sample spreadsheet attached

[jolivanes]

Cell C4 on Sheet Form should be 1 or more. Sheet indexes don't have 0 (zero) AFAIK

Sub PrintForms()
    Dim StartSheet As Integer
    Dim EndSheet As Integer
    Dim Msg As String
    Dim i As Integer
    
    Sheets("Form").Activate
    StartSheet = Range("StartSheet")
    EndSheet = Range("EndSheet")
    
    If StartSheet > EndSheet Then
        Msg = "ERROR" & vbCrLf & "The starting sheet must be less than the ending sheet!"
        MsgBox Msg, vbCritical, APPNAME
    End If
    
    For i = StartSheet To EndSheet
        Range("SheetIndex") = i
        If Range("Preview") Then
            Sheets(i).PrintPreview    '<----- Changed
        Else
            Sheets(i).PrintOut    '<----- Changed
        End If
    Next i
End Sub

[queenstarks]

How do i get that value to change?

[jolivanes]

It works here. Do you have the ComboBox 1 Cell Link set to L1?

[queenstarks]

This is my current code for the combobox:

Private Sub ComboBox1_Change()
Sheets("Form").Cells(3, 3).Value = ComboBox1.ListIndex
End Sub


Not sure why L1 isn't updating when print function is ran. Only changes if I select from dropdown.

[queenstarks]

Thanks but that didn't work. The problem I'm having is that Cell L1 isn't changing which drives the data for M2:M10.

[queenstarks]

It's not allowing me to link to a cell. That option isn't available. Working in 2010

[jolivanes]

Re: Not sure why L1 isn't updating when print function is ran
Should it and why?
The way it is set up, it prints the sheets as in cells C4 and C5.
The "Form" sheets is updated when you select from the combobox.
Or do you want to print just the 2 sheets, the combobox selection and the Form sheet, when you click on the print button?

[queenstarks]

When I click print, it should print all sheets within range selected in C4 and C5 without me having to select it from the ComboBox. It should be automatic. It worked last year; not sure what's going on. Maybe it's a 2010/XP thing. Thanks for all your assistance.

[jolivanes]

I don't know what to say because it works here on 2007/XP.
Good luck though.

[queenstarks]

Anyone familiar with coding this same situation using Excel 2011 for Mac? I'm trying it on a Mac now but combobox won't work since active x. Thx

[protonLeah]

"... When I click print, it should print all sheets within range selected in C4 and C5 without me having to select it ..."
But:

    For i = StartSheet To EndSheet
        Range("SheetIndex") = i
        If Range("Preview") Then
            ActiveSheet.PrintPreview
        Else
            ActiveSheet.PrintOut
        End If
    Next i

Does not actively select nor change the active sheet. Therefore, you get EndSheet - StartSheet number of copies of the same sheet.
The workbook posted has various sheets numbered from 1 to 87 so I don't know what the i-loop is refering to. There is no indication that #i should activate a particular sheet by name or by number. For instance there is no sheet2...

For some reason you have combobox1 with:
BoundColumn @ 2
ColumnCount @ 2
ColumnWidths @16,16 which is to narrow to show the selection
HideSelection =True (?)
ListFillRange = error since you did not include that sheet in the sample
TextColumn @ 2
If you want the combobox items linked to member names (tabs) you can use:

Private Sub Workbook_Open()
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    Application.EnableEvents = False
    
    Dim x
    With Sheets("Form").ComboBox1
        .ListFillRange = ""
        .LinkedCell = ""
        .Clear
    End With
    
'Dropdown list filled with member names only

    For Each x In ThisWorkbook.Worksheets
        If x.Name <> "Form" _
            And x.Name <> "HelpSheet" _
            And x.Name <> "helpmod" _
            And x.Name <> "printmod" Then
                Sheets("Form").ComboBox1.AddItem x.Name
        End If
    Next x
    Application.EnableEvents = True
    
End Sub

You need to determine which sheets you want to print. (Probably based on sheet names). That Fir i..Next i loop isn't going to work as written.

Form sheet code mod:

Private Sub ComboBox1_Change()
    Sheets("Form").Cells(3, 3).Value = ComboBox1.ListIndex
    Sheets("form").Range("L1").Value = ComboBox1.Value
End Sub

[queenstarks]

Thanks for this code. Tried it and it didn't work. Only problem I'm having with my spreadsheet is the print feature. What is suppose to happen is when I click printforms, It will open preview screen of the record identified in startsheet. When I close that one, the next sheet would open. Problem I'm getting is that the sheets are opening just not with the correct information. Example: the current sheet is on name 1. I hit print and all the info for name 1 (cells m2:m10) are displayed; when it's time for next sheet to print, it is still showing name 1 information. That's the hiccup I'm running into.

Thanks

[queenstarks]

Anyone figure this one out?