Satisfy Cell Requirements Before Printing

[[email protected]]

Hello.

My worksheet has three cells that are currently configured with the
Data Validation "List" criteria (with a default value of "Choose").
I would like to require the user to select another value before
printing the worksheet.

Does anyone know how I might code this so that: If the contents of
cell A1 and A2 and A3 are not equal to "Choose" then PRINT; otherwise
display a message box instructing the user to enter another value?

Thanks!!
Jessi

[Ron de Bruin]

Hi Jessi

Ok, Try this in the thisworkbook module for a sheet named Sheet1
It will check A1:C1

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
If Application.WorksheetFunction.CountIf(Range("A1:C1"), "Choose") > 0 Then
MsgBox "change A1:C1"
Else
With ActiveSheet
.PrintOut
End With
End If
Application.EnableEvents = True
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


<[email protected]> wrote in message news:[email protected]...
> Hello.
>
> My worksheet has three cells that are currently configured with the
> Data Validation "List" criteria (with a default value of "Choose").
> I would like to require the user to select another value before
> printing the worksheet.
>
> Does anyone know how I might code this so that: If the contents of
> cell A1 and A2 and A3 are not equal to "Choose" then PRINT; otherwise
> display a message box instructing the user to enter another value?
>
> Thanks!!
> Jessi
>

[[email protected]]

Thank you, Ron:

If I put the first two lines (1) If ActiveSheet.Name = "Sheet1" Then
(2) Cancel = True, along with the argument in the Sub name (Cancel As
Boolean), then it returns the error "ARGUMENT NOT OPTIONAL"

If, however, I eliminate the above lines, it still PRINTS (but does NOT
display the message box) if the word "(Choose)" is still entered into
the cells.

If I modify the code from ">" to "=" in the following line:
If Application.WorksheetFunction.CountIf(Range("$C$16:$C$19"),
"(Choose)") = 0 Then
then it DISPLAYS the message box but does NOT print (regardless of what
is entered into the cells).

The code, as I currently have it, is listed in its entirety below:

Sub PrintAllSections()

'Unhide Sheet
Sheets("All Sheets").Visible = True
Sheets("All Sheets").Select
'Print the selected sheet
Application.EnableEvents = False
If Application.WorksheetFunction.CountIf(Range("$C$16:$C$19"),
"(Choose)") = 0 Then
MsgBox "Change Divestiture Values"
Else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
Application.EnableEvents = True
'Hide the Sheet
Sheets("All Sheets").Visible = False
Sheets("Barcode").Select
Range("C5").Select
'Protect the Sheet
ActiveSheet.Protect
End Sub

Did I miss something? If it matters, three of my cells are currently
formatted as text; the fourth cell is formatted as a date.

Thanks so much for your help!
Jessi

[[email protected]]

I kept playing with this, and was able to make it work with ONE of the
cells (C16) by modifying the worksheet.range line. How could I get it
to also work with the other cells (C17, C18, and C19)?

Cell C18 is formatted as a date, but I had still typed "Choose" in the
cell as a default.

My code so far:

Sub PrintAllSections()

'Unhide Sheet
Sheets("All Sheets").Visible = True
Sheets("All Sheets").Select
'Print the selected sheet
If Worksheets("Barcode").Range("C16") = "Choose" Then
MsgBox "Change Divestiture Values"
Else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
'Hide the Sheet and Move the Cursor
Sheets("All Sheets").Visible = False
Sheets("Barcode").Select
Range("C16").Select
'Protect the Sheet
ActiveSheet.Protect
End Sub

Thanks!
Jessi

[Ron de Bruin]

Hi

If you want to use a macro try this

"(Choose)"
Is this correct an not
"Choose")

Sub PrintAllSections()
If Application.WorksheetFunction.CountIf(Worksheets("Barcode"). _
Range("C16:C19"), "(Choose)") > 0 Then
MsgBox "Change Divestiture Values"
Else
With Sheets("All Sheets")
.Visible = True
.PrintOut
.Visible = False
End With
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


<[email protected]> wrote in message news:[email protected]...
>I kept playing with this, and was able to make it work with ONE of the
> cells (C16) by modifying the worksheet.range line. How could I get it
> to also work with the other cells (C17, C18, and C19)?
>
> Cell C18 is formatted as a date, but I had still typed "Choose" in the
> cell as a default.
>
> My code so far:
>
> Sub PrintAllSections()
>
> 'Unhide Sheet
> Sheets("All Sheets").Visible = True
> Sheets("All Sheets").Select
> 'Print the selected sheet
> If Worksheets("Barcode").Range("C16") = "Choose" Then
> MsgBox "Change Divestiture Values"
> Else
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> End If
> 'Hide the Sheet and Move the Cursor
> Sheets("All Sheets").Visible = False
> Sheets("Barcode").Select
> Range("C16").Select
> 'Protect the Sheet
> ActiveSheet.Protect
> End Sub
>
> Thanks!
> Jessi
>

[[email protected]]

This worked perfectly!! Thanks so much.

P.S. My default cell entry did originally have parenthese around it,
but I deleted them because it was causing confusion.

Thanks again!

Jessi