Easiest way to create forms with Excel?

[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.

 

[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
(e-mail address removed)

 

[Joseph R. Pottschmidt]

Dear HeavyUser:

Since you are asking for something that will need some kind of custom
design, you don't leave a lot of room for development time, or cost of
development. If you want to design forms, then you'll also have to be
able to put the code behind it to do error checking as well as any other
processes that you wish to have happen.

There are a lot of companies that have forms that have been written in
excel and have many functions, but the problem that I think you're going
to run into is what information is on the form and how you want to
process it.

This all requires someone, if not you, to code all those kinds of
conditions into the form/VBA code.

Joe P.


-----Original Message-----
From: heavyuser [mailto:[email protected]]
Posted At: Sunday, June 18, 2006 10:56 AM
Posted To: microsoft.public.excel
Conversation: Easiest way to create forms with Excel?
Subject: Easiest way to create forms with Excel?

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.

 

[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.

 

[Nick Hodge]

If you can assemble the 'form' on the worksheet, you best bet is data
validation (Data>Validation...)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[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

 

[jkend69315]

Heavy,
We do custom Excel userforms and the cost is very reasonable. Drop
me an e-mail if you want to discuss. (e-mail address removed)
Just remove the nospamZZZ from the address. James