Go back to required cell to fill

[erutherford]

Have the basics down, now just trying to make it easier for the end user. Cell C21 is a required cell to be fill, not not a Message pops up. I would like the vba to take them right to that cell.
Here is the code I am using.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Cells(21, 3).Value = "" Then

MsgBox "License No. requires input before print"

Cancel = True

End If

End Sub
​

thanks in advance

 

[erutherford]

I want to make sure that if someone else looks at this thread they can see a solid solution. So I went back in and deleted the modules created. I have a single worksheet now. I want the end user to be required to enter info in C21 BEFORE they print out the sheet. If they try to print out the sheet without the required info, then they are prompted to do so with a MsgBox. That is when I would like "vba" to take me back to C21. Once the data is entered then I am allow to print (specific range).

So where is the best place to assign the function? Within the sheet or in a Module? Does it matter if I use a cmd button or a form control button?

Here is the code I have been manipulating

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'If Cells(21, 3).Value = "" Then

'MsgBox "License No. requires input before print"
'Cells(21, 3).Select
'Cancel = True

'End If



I haven't figure out where the cell range should go.

 

[MARK858]

If it is a WorkBook module as you have posted then it goes in the ThisWorkbook module.

 

[erutherford]

Getting closer! What is needed in this code to allow me to enter in the data in C21? With C21 empty, hit the print button (CmdButton1)the MsgBox popsup, but once I select ok, it prints the range without allowing me to fill in the data.


Private Sub CommandButton1_Click()
If Cells(21, 3).Value = "" Then

MsgBox "License No. requires input before print"
Cells(21, 3).Select
Cancel = True

End If
Range("A1:I50").PrintOut
End Sub

 

[MARK858]

If you want it to stop to input data then put in an inputbox line after the if test rather than/additional to the msgbox.

 

[erutherford]

Private Sub CommandButton1_Click()
If Cells(21, 3).Value = "" Then

MsgBox "License No. requires input before print"
Cells(21, 3).Select
Cancel = True

End If
PLACE IT HERE?
Range("A1:I50").PrintOut
End Sub

 

[erutherford]

It now works properly

Private Sub CommandButton1_Click()
If Cells(21, 3).Value = "" Then

MsgBox "License No. requires input before print"
Cells(21, 3).Select
Cancel = True
End If
myValue = InputBox("gimme") - 'use any expression here
Range("C21").Value = myValue
Range("A1:I50").PrintOut
End Sub

Thanks to everyone that replied!!!!!!

 

[My Aswer Is This]

Your script should look like this:

Private Sub CommandButton1_Click()
Dim ans As Long
 If Cells(21, 3).Value = "" Then
    MsgBox "License No. requires input before print"
    ans = InputBox("Enter Your License number")
    Cells(21, 3).Value = ans
 End If
 Range("A1:I50").PrintOut
 End Sub

OK

 

[erutherford]

looks like one more issue. If we cancel the input box, the page will still print!
What is required then?

Private Sub CommandButton1_Click()
If Cells(21, 3).Value = "" Then

MsgBox "License No. requires input before print"
Cells(21, 3).Select
Cancel = True
End If
myValue = InputBox("Enter License Plate Info")
Range("C21").Value = myValue
Range("A1:I50").PrintOut
End Sub

 

[erutherford]

Nope that gives me a Debug error

 

[Rick Rothstein]

looks like one more issue. If we cancel the input box, the page will still print!
What is required then?

Private Sub CommandButton1_Click()
If Cells(21, 3).Value = "" Then

MsgBox "License No. requires input before print"
Cells(21, 3).Select
Cancel = True
End If
myValue = InputBox("Enter License Plate Info")
Range("C21").Value = myValue
Range("A1:I50").PrintOut
End Sub

Maybe this...

Private Sub CommandButton1_Click()
  If Cells(21, 3).Value = "" Then
    MsgBox "License No. requires input before print"
    Cells(21, 3).Select
    [B][COLOR="#FF0000"]Cancel = True[/COLOR][/B]
  End If
  myValue = InputBox("Enter License Plate Info")
  If Len(Trim(myvaluue)) Then
    Range("C21").Value = myValue
    Range("A1:I50").PrintOut
  End If
End Sub

I left the highlighted code line in, but since this code is no longer in a event procedure, it does nothing useful any more.