vba macro for print button that brings up print screen selection window

[kellie1]

I have a print button already created and the macro brings up the print screen selection window for the user but I need this window to already be populated with 'entire workbook' selection. The workbook is emailed to the user and they click the print button.
Thank you

'CREATE PRINT BUTTON
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, 11.25, 76.5, 51, 17.25).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "PRINT"
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 5). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignLeft
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 5).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange.ScaleWidth 0.8529411765, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.1304347826, msoFalse, msoScaleFromTopLeft
    'Selection.OnAction = "PRINTWKBK"
    Range("A9").Select
    
'add print macro to button
ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
    Selection.OnAction = "PERSONAL.xlsb!ON_CLICK_PRINT"
    Range("A8").Select

[Greg M]

Hi there,

I'm afraid the code you've posted (which seems to work correctly ) isn't much use to us - we need to see the code for the routine "ON_CLICK_PRINT" which is apparently in your "PERSONAL.xlsb" workbook.

Regards,

Greg M

[kellie1]

I believe that is where I am not understanding how to get the print button to work once the spreadsheet is emailed to the user. The on click is in my personal so how does one get it into the workbook that is being emailed?

[Sub ON_CLICK_PRINT()
Application.Dialogs(xlDialogPrint).Show
End Sub]

[Greg M]

Hi again,

Thanks for that information.

See if the following code does what you need:

Option Explicit


'=========================================================================================
'=========================================================================================



Sub PrintWorkbook()

    Const dHEIGHT   As Double = 1.2
    Const dWIDTH    As Double = 0.9

    Dim shp As Shape

    Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 11.25, 76.5, 51, 17.25)

    With shp

        .Name = "btnPrint"

        .TextFrame2.TextRange.Characters.Text = "PRINT"

        With .TextFrame2.TextRange.Characters(1, 5)

            .ParagraphFormat.FirstLineIndent = 0
            .ParagraphFormat.Alignment = msoAlignLeft

            With .Font

                .NameComplexScript = "+mn-cs"
                .NameFarEast = "+mn-ea"
                .Size = 11
                .Name = "+mn-lt"

                With .Fill

                    .ForeColor.ObjectThemeColor = msoThemeColorLight1
                    .ForeColor.TintAndShade = 0
                    .ForeColor.Brightness = 0
                    .Transparency = 0
                    .Visible = msoTrue
                    .Solid

                End With

            End With

        End With

        .ScaleHeight 1.1304347826, msoFalse, msoScaleFromTopLeft
        .ScaleWidth 0.8529411765, msoFalse, msoScaleFromTopLeft

        .OnAction = "PRINTWKBK"         '     <<< Insert the appropriate routine name here

    End With

    ActiveSheet.Range("A9").Select

End Sub


'=========================================================================================
'=========================================================================================


Sub PRINTWKBK_Version_1()

'   This routine prints the entire workbook without using the "Print" DialogBox
    ActiveWorkbook.PrintOut

End Sub


'=========================================================================================
'=========================================================================================


Sub PRINTWKBK_Version_2()

'   This routine displays the "Print" DialogBox with the "EntireWorkbook" option selected
    Application.Dialogs(xlDialogPrint).Show , , , , , , , , , , , 3

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M