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

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


wrote in message oups.com...
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

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

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

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


wrote in message ups.com...
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

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