Prevent Printing Unless Drop Down Menu is used

[soranandivan]

I am trying to prevent someone from printing an excel form unless they have filled in all the necessary fields.
The part I am stuck on is with drop down menu's I do not know how it reads the return.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim oCell As Range
Dim NoBlanks As Range

Set NoBlanks = ActiveSheet.Range("c15")

For Each oCell In NoBlanks
If oCell.Value = "" Then
Cancel = True
MsgBox "Not all required cells have been filled", vbCritical + vbOKOnly, "Error"
Exit Sub
End If
Next

End Sub

That's my Code I've also tried
If oCell.Value = "Select Action Required From Drop Down" Then

As the statement Select Action Required From Drop Down is the default statement in that drop down menu (not blank). Neither restricts printing.
C15 is the cell in which the drop down menu is located.

[Bernie Deitrick]

The term "drop-down menu" is ambiguous - does the cell have a Data Validation List, or is it linked to a Listbox?

For troubleshooting / development, just show what the value currently is, using

Please Login or Register  to view this content.

[soranandivan]

Thanks for the help Bernie, I'm still not getting it.
It is a Listbox that I am using

I'm not sure what your line
If MsgBox("The current value is """ & NoBlanks.Value & """ - Continue?", vbYesNo) = vbNo Then Exit Sub
does,

The Listbox calls on 3 options (from Sheet6)
Selection Action Required from Drop Down (Default)
Request JTP
Remove JTP

my objective is ban printing if "Selection Action Required from Drop Down" is still selected
but to allow printing if Request JTP or Remove JTP is selected

[Bernie Deitrick]

This assumes that you have linked the listbox to cell C15 of the sheet that you want to print:

Please Login or Register  to view this content.

[soranandivan]

c15.PNG
Listbox.PNG
Sheet6.PNG

Above are snips on my file.
The code you sent unfortunately did not work. I can print in all situations. I can't quite interpret the code you've written so I'm not really sure where the logic is failing.

I really do appreciate the help!

[Bernie Deitrick]

Are you putting the code into the codemodule of the ThisWorkbook object?

Do you have a data validation list (it seems that you do) and not a listbox?

If so, try this version

Please Login or Register  to view this content.

[soranandivan]

Yes that Worked!
Thank-you