Progress Indicator - Help Counting Number of Worksheets Formatted

[sowetoddid]

I am using the following code to format all sheets in a workbook to the 11x17. I have many different workbooks that use this code and each workbook has a different number of sheets. How would I count the number of sheets that are being formatted? I am trying to use the progress indicator idea from j-walk's webpage...

http://j-walk.com/ss/excel/tips/tip34.htm

Sub PrintWorkbook_Tabloid()
    For Each xWorksheet In ActiveWorkbook.Worksheets
        With xWorksheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.5)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.25)
            .BottomMargin = Application.InchesToPoints(0.25)
            .HeaderMargin = Application.InchesToPoints(0.5)
            .FooterMargin = Application.InchesToPoints(0.5)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .CenterHorizontally = True
            .CenterVertically = True
            .Orientation = xlLandscape
            .PaperSize = xlPaperTabloid
            .BlackAndWhite = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintErrors = xlPrintErrorsDisplayed
        End With
    Next xWorksheet
    
    Msg = "This file is about to be printed in Tabloid-size on" & _
        Chr(13) & Chr(13) & Application.ActivePrinter & _
        Chr(13) & Chr(13) & "Would you like to continue?"
    Style = vbYesNo + Exclamation + vbDefaultButton1
    Title = "Print Confirmation"
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
        ActiveWorkbook.PrintOut
        Exit Sub
    End If
End Sub

Thank you.

[swatsp0p]

Something like this should work:

For ii = 1 To Workbooks(1).Sheets.Count
Next ii

Msg = "This file is about to be printed in Tabloid-size on" & _
Chr(13) & Chr(13) & Application.ActivePrinter & _
Chr(13) & Chr(13) & "A total of "& ii &" pages. "&_Chr(13) & Chr(13) &"Would you like to continue?"
Style = vbYesNo + Exclamation + vbDefaultButton1


HTH

[sowetoddid]

Got it, thank you, Bruce.

It is working!!

[swatsp0p]

I'm glad that worked for you. Thanks for the feedback.

Cheers!

Bruce