Easiest way to create forms with Excel?

H

heavyuser

Hi all - apologies if this is a FAQ question, was unable to find good
answers there.

My organization often needs to distribute various 'forms' designed in
Excel. People will fill them in, (often print a copy and) send the
filled-in sheets back to us.

There are a lot of problems with this: people will modify stuff where
they're not supposed to, will leave 'mandatory' options blank, and the
process of copying/pasting data for further processing once we get the
filled in sheet is laborious.

For various reasons we need to stay with Excel (i.e. Access, or redoing
everything as web-based, is not an option). What is the best way to
design forms in Excel, provide some constraints to fields (e.g.
'required', or 'must be a number') without extensive VBA coding, and
without having to lock/unlock and password protect everything?

I imagine lots of people have this problem. Any 3rd party solutions,
free or not free, are appreciated too.
 
N

Nick Hodge

Certainly for your extensive list of requirements to 'block' extraneous
users actions, you will need a lot of VBA code and controls. There is
certainly no easy UI way, which is I think what you seek.

Access in this scenario would possibly be a much more suitable tool

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
H

heavyuser

Thanks Joseph & Nick,

I was probably unclear. I don't really need lots of complex processing
- we do the occasional bit of VBA but we really need this to work
without programming.

Something that just assures people have entered all the 'required'
fields would be sufficient. I can live with having to lock/unlock the
spreadsheet for modifications, and having only the actual fill-in
fields unlocked.
 
P

Paul B

heavyuser, maybe this will get you started, you could put it in a before
print, save, workbook close, event also

Sub Check_For_Data()
Dim test_rng As Range
Dim ret_str As String
Dim cell As Range
Set test_rng = ActiveSheet.Range("A1:A5,B6") '***Change to your range
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 "There is data missing in cell(s): " & ret_str
Else
MsgBox "Your code here if all data is put in"
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
J

jkend69315

Heavy,
We do custom Excel userforms and the cost is very reasonable. Drop
me an e-mail if you want to discuss. [email protected]
Just remove the nospamZZZ from the address. James
 
Top