CODEWITHSUNDEEP
excelprintingvba
Bodhi
Bodhi

Reputation: 548

how to add not print option in excel macro inside the vba?

I have macro button in excel which has the following code and printing accordingly

Private Sub CommandButton10_Click()

MsgBox "SET YOUR PRINTER &  CLICK OK"
    Range("B18:B58").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-33
    Range("bf18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveSheet.PageSetup.PrintArea = "$L$775:$AN$818"
    'ActiveWindow.SelectedSheets.printout Copies:=1, Collate:=True
    'ActiveSheet.PageSetup.PrintArea = "$ay$520:$be$523"
    'Range("A1").Select
'ActiveWindow.SelectedSheets.printout Copies:=1, Collate:=True

ActiveWindow.SelectedSheets.PrintPreview
End Sub

. if the button clicks this will print to the default printer directly,but i want to make an option to cancel the printer as if someone clicks the button can go back also ..how to do it with the above code

thnk u

Upvotes: 0

Views: 63

Answers (1)

Richard
Richard

Reputation: 347

MsgBox can return what was pressed, so you can use it in an if statement.

So;

Private Sub CommandButton10_Click() 'Give this a name!
    If MsgBox("SET YOUR PRINTER & CLICK OK", vbOKCancel) = vbOK Then
        Range("B18:B58").Select
        Selection.Copy
        ActiveWindow.SmallScroll Down:=-33
        Range("bf18").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select
        ActiveSheet.PageSetup.PrintArea = "$L$775:$AN$818"
        'ActiveWindow.SelectedSheets.printout Copies:=1, Collate:=True
        'ActiveSheet.PageSetup.PrintArea = "$ay$520:$be$523"
        'Range("A1").Select
        'ActiveWindow.SelectedSheets.printout Copies:=1, Collate:=True

        ActiveWindow.SelectedSheets.PrintPreview
    End If
End Sub

Upvotes: 1

Related Questions