Input Box BeforePrint with input in foot

[q1911]

Hello- I had a VBA code to display an error message to an user based on a cell. I am trying to change it from a MsgBox to an input box and then put that entered input into into the left footer.

so a user gets the message, they input comments, if they hit OK, it prints with the comments in the left.footer, if they hit Cancel it goes back to the sheet.

The two areas to focus on are:

1. 'this is the dialog box
2. 'this is the left footer

TIA

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'This macro runs when the sheet is printed. It checks to see if wages are being made,
'if wages are not made, a popup will conifrm RVP approval with the user.
'It will print a footer stating it was printed not making wages


'This removes all footers prior
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
End With

'This checks if wages are made
If Range("L1") >= 1 Then

'this is the dialog box
Dim i As Variant
With Application
    .EnableEvents = 0
i = InputBox("You are scheduling to miss budgeted wages. RVP approval is needed.", "Missing Wages!", "Enter RVP's approval/comments here")
If Not Confirm = vbOK Then Cancel = True
    .EnableEvents = 1
    
End With
End If

'This adds the version to the footer
With ActiveSheet.PageSetup
    .RightFooter = Worksheets("Change Log").Range("G4")
End With

With ActiveSheet.PageSetup
    .CenterFooter = "&BPrinted: &B&D &I&T"
End With

'this is the left footer
With ActiveSheet.PageSetup
    .LeftFooter = "Printed not making wages with the following comments:" & i
End With


End Sub

 

[q1911]

I got it all working except, I need to make it so when they hit cancel, it doesn't print:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'This macro runs when the sheet is printed. It checks to see if wages are being made,
'if wages are not made, a popup will conifrm RVP approval with the user.
'It will print a footer stating it was printed not making wages


'This removes all footers prior
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
End With

'This checks if wages are made
If Range("L1") >= 1 Then

'this is the dialog box
Dim i As Variant
With Application

i = InputBox("You are scheduling to miss budgeted wages. RVP approval is needed.", "Missing Wages!", "Enter RVP's approval/comments here")
If Not Confirm = ybYes Then Cancel = True
 
      
End With

With ActiveSheet.PageSetup
    .LeftFooter = "Comments:" & i
End With

End If

'This adds the version to the footer
With ActiveSheet.PageSetup
    .RightFooter = Worksheets("Change Log").Range("G4")
End With

With ActiveSheet.PageSetup
    .CenterFooter = "&BPrinted: &B&D &I&T"
End With



End Sub

 

[HaHoBe]

Hi q1911,

you assign the variable i to the InputBox but you check for confirm.

Try

'this is the dialog box
    varRet = InputBox("You are scheduling to miss budgeted wages. RVP approval is needed.", "Missing Wages!", "Enter RVP's approval/comments here")
    If Len(varRet) = 0 Then
      Cancel = True
      Exit Sub
    End If
End If

Ciao,
Holger

 

[q1911]

Hi q1911,

you assign the variable i to the InputBox but you check for confirm.

Try

'this is the dialog box
    varRet = InputBox("You are scheduling to miss budgeted wages. RVP approval is needed.", "Missing Wages!", "Enter RVP's approval/comments here")
    If Len(varRet) = 0 Then
      Cancel = True
      Exit Sub
    End If
End If

Thank y

This works, thank you!!!!

 

[HaHoBe]

like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'This macro runs when the sheet is printed. It checks to see if wages are being made,
'if wages are not made, a popup will conifrm RVP approval with the user.
'It will print a footer stating it was printed not making wages
Dim varRet As Variant

'This removes all footers prior
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
End With

'This checks if wages are made
If Range("L1") >= 1 Then

'this is the dialog box
    varRet = InputBox("You are scheduling to miss budgeted wages. RVP approval is needed.", "Missing Wages!", "Enter RVP's approval/comments here")
    If Len(varRet) = 0 Then
      Cancel = True
      Exit Sub
    End If
End If

'This adds the version to the footer
With ActiveSheet.PageSetup
    .RightFooter = Worksheets("Change Log").Range("G4")
End With

With ActiveSheet.PageSetup
    .CenterFooter = "&BPrinted: &B&D &I&T"
End With

'this is the left footer
With ActiveSheet.PageSetup
    .LeftFooter = "Printed not making wages with the following comments:" & varRet
End With

End Sub

Holger