Thread: Print Macro

I've added a "print" button with a simple recorded macro into a spreadsheet. When the user hits the print button, I need something that looks at cell P14 and shows an error message if the value = 1, also it needs to look at P15 and show a different error message if that value = 1. If either are one, the print macro should halt until both values return to 0. HELP! Thanks!!!

Hello pduback,

Welcome to the Forum!

It would help to see the recorded macro code. What do you want each error message to say?

Sorry, forgot to paste it in:

Sub Print_Macro()
'
' Print_Macro Macro
'

'
    Sheets("Fee Calculator").Select
    Sheets("Printed Schedule").Visible = True
    Sheets("Fee Calculator").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Printed Schedule").Select
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Sheets("Printed Schedule").Select
    Sheets("Fee Calculator").Visible = True
    Sheets("Printed Schedule").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

Hello pduback,

Try this version of the macro. Change the message for P15. It is marked in bold.

Sub Print_Macro()
'
' Print_Macro Macro
'

'
    Sheets("Fee Calculator").Select
    Sheets("Printed Schedule").Visible = True
    Sheets("Fee Calculator").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Printed Schedule").Select
    If Range("P14") = 1 Then 
       MsgBox "Fee schedules must be entered in descending order, please adjust..." , vbExclamation+vbOKOnly
       Exit Sub
    End If
    If Range("P15") = 1 Then 
       MsgBox "Fee schedules must be entered in descending order, please adjust..." , vbExclamation+vbOKOnly
       Exit Sub
    End If
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Sheets("Printed Schedule").Select
    Sheets("Fee Calculator").Visible = True
    Sheets("Printed Schedule").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

Hello pdubuck,

Could be we are not checking the correct sheet. The macro is using P14 and P15 on the "Printed Schedule". Is that the correct sheet?

Hello pduback,

Okay, this should work.

Sub Print_Macro()
'
' Print_Macro Macro
'

'
    Sheets("Fee Calculator").Select
    Sheets("Printed Schedule").Visible = True
    Sheets("Fee Calculator").Select
    If Range("P14") = 1 Then 
       MsgBox "Fee schedules must be entered in descending order, please adjust..." , vbExclamation+vbOKOnly
       Exit Sub
    End If
    If Range("P15") = 1 Then 
       MsgBox "Fee schedules must be entered in descending order, please adjust..." , vbExclamation+vbOKOnly
       Exit Sub
    End If
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Printed Schedule").Select
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Sheets("Printed Schedule").Select
    Sheets("Fee Calculator").Visible = True
    Sheets("Printed Schedule").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

Hello pduback,

In this case, yes. The Range property applies to the ActiveSheet unless it has been qualified with a worksheet name like below.

    Worksheets("Sheet1").Range("A1")

Since "Fee Calculator" is made active before the IF...THEN statements, the Range is assumed to be on the active sheet which is "Fee Calculator".