Mandatory Field Checker

[Sessions]

I have a form that has some mandatory fields that must be filled i
before it gets sent out. Is there a simple macro that could check th
specific cells and if there is nothing entered, it would return
message to tell you to fill in that specific cell?

EG:

(A1) Name (Req'd)

(A1) (Empty)

"--STOP-- You need to fill out the name" (Alert msg)

Does that make sense?

Thank you.

S

 

[beeawwb]

It would be something like...


dim newbox
Sub Checkinga1()
'Check if A1 is empty
If [A1] = "" Then
'Advise Null
newbox = MsgBox("--STOP-- You need to fill out the name", vbCritical,
"Warning")
End
Else
'Verify Data entered and complete macro
Beep
End If
End Sub

At least, that's the way I'd do it, from the way I understand what
you've written. I put that in a button which calls the macro
Checkinga1, but it could also be put in a macro that copied the data to
a printout and then printed / faxed it, where you would have a whole
bunch of validations, and if all of them completed, it would print,
otherwise it would end.

Hope that helps you,

Bob

 

[beeawwb]

And Because I'm bored and at work with nothing else to do, here's how to
put it into a fax / print.

Module1
'Global definitions
dim newbox

Sub Checkinga1()
'Check if A1 is empty
If [a1] = "" Then
'Advise Null
newbox = MsgBox("--STOP-- You need to fill out the name", vbCritical,
"Warning")
End
Else
'Verify Data entered and complete macro
Beep
End If
End Sub

Sub Checkingb1()
'Check if B1 is empty
If [b1] = "" Then
'Advise Null
newbox = MsgBox("--STOP-- You need to fill out the date", vbCritical,
"Warning")
End
Else
'Verify Data entered and complete macro
Beep
End If
End Sub

Sub verifyfax()
Call Checkinga1
Call Checkingb1
[Sheet2!A1] = [Sheet1!A1]
[Sheet2!B1] = [Sheet1!B1]
Sheets("Sheet2").Select
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub


Verify fax is the macro that is called by the "Submit" button, and just
copy-paste checking modules that would verify all mandatory data, then
copy it into the sheet with the fax where it needs to go. I've tested
all this on my local machine, I hope it's what you need.

Hope it helps you.

-Bob

 

[Sessions]

Thanks alot for that. It works really well. One more small question
(as I am fairly new to this) if I want to check say...A1 F4 N32, how
would I work that into this code?

Sub Check()
'Check if A1 is empty
If [A1] = "" Then
'Advise Null
newbox = MsgBox("Please ensure that all the boxes are filled in!",
vbCritical, "Warning")
End
Else
'Verify Data entered and complete macro
Beep
End If
End Sub

Thanks again for your help. I do appreciate it.

 

[beeawwb]

I'm new again to this myself. It's been quite a while since I've done
anything in VBA, it's great to start thinking in terms of problems
again.

So,

You want it to check A1 F4 N32 in one function, all for name?

If that's what you want, code would look something like...

*opens Excel...*

Sub Check()
'Check if A1, F4 or N32 are empty
If ([a1] = "") Or ([f4] = "") Or ([n32] = "") Then
'Advise Null
newbox = MsgBox("Please ensure that all the boxes are filled in!",
vbCritical, "Warning")
End
Else
'Verify Data entered and complete macro
Beep
End If
End Sub

Just so you know, you can take the "Beep" code out. That's just
something I put into every function I write, so that I know what point
the code gets to before it hits an error. So, if I hear a beep, I know
it's working fine.

If on the other hand, you meant how do you check each one seperately,
copy->paste Sub Check() as Checka1, Checkf4 and Checkn32, replacing the
If [CELL]="" with what you need to check. Change the msgbox to how you
want it to read, and in your button, just call the checks you were
using.

If you're online between the hours of 8:30am (Adelaide Australia time)
and 5pm, I can respond to any questions you've got as soon as I read
them.

Hope it helps.

-Bob