Satisfy Cell Requirements Before Printing
 

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

Satisfy Cell Requirements Before Printing
 

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

Satisfy Cell Requirements Before Printing
 

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

Satisfy Cell Requirements Before Printing
 

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

Satisfy Cell Requirements Before Printing
 

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

Satisfy Cell Requirements Before Printing
 

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