You've missed something

[Aaron Howe]

I have a spreadsheet intended for use as a form, which
many users would fill out. Each of the required fields
have been validated for entry, and there is a simple piece
of code at the bottom with a conditional format to point
out items which should have been filled in and weren't.

I have a print macro which will warn users that if they
haven't filled out everything, they should go back and do
it now. What I would really love to do, is to point out
which items they missed, dependent on the type of form
they are completing. So, if for example they should enter
things in the following fields:

Name
Address
Postal Code
Telephone number

And they missed Name and Address, the print macro would
warn that those fields were missed out before printing. I
am not so good with setting variables in VB and would
appreciate any help you could give!!

 

[Frank Kabel]

Hi
can oyu post your existing print macro. It would be easier to add this
kind of code to your existing one (taking care of your catual
references)

 

[Aaron Howe]

Sure, here you go:

Sub Printer()
'Recorded by Aaron Howe for printing
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Before printing, check there is no missing
information in your form which may result in it being
returned. When you are ready press the 'OK'button"
Style = vbOKCancel + vbDefaultButton1
Title = "Ready to print?"
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then
ActiveSheet.PrintOut
Else
End If
End Sub

 

[Frank Kabel]

Hi
try something like the following (tests the cells A1:A10):

Sub test_complete()
Dim test_rng As Range
Dim ret_str As String
Dim cell As Range
Set test_rng = ActiveSheet.Range("A1:A10")
For Each cell In test_rng
If cell.Value = "" Then
If ret_str = "" Then
ret_str = cell.Address
Else
ret_str = ret_str & " and " & cell.Address
End If
End If
Next
If ret_str <> "" Then
MsgBox "No Printout: There are informations missing in cell(s): " &
ret_str
Else
ActiveSheet.PrintOut
End If
End Sub

 

[Guest]

Hi Frank,

I tried out your code and it worked very well. Only
trouble is, my users are going to be confused by the cell
references as the form is locked and hidden in places, and
the axes are going to be removed. Is there any way to
assign a name to the cells I wish to affect, so that the
following could happen:

Instead of: "You missed out the field $A$1"
you would see: "You missed out the field Address"

I tried naming the cell using Insert > Name but obviously
that didn't do the trick. Would I have to use a Set
command in VB?

TIA
Aaron