Easiest way to create forms with Excel?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

heavyuser - 18 Jun 2006 18:55 GMT

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.

Reply to this Message

Nick Hodge - 18 Jun 2006 19:09 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

> Hi all - apologies if this is a FAQ question, was unable to find good
> answers there.
[quoted text clipped - 16 lines]
> I imagine lots of people have this problem. Any 3rd party solutions,
> free or not free, are appreciated too.

Reply to this Message

heavyuser - 18 Jun 2006 19:18 GMT

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.

Reply to this Message

Nick Hodge - 18 Jun 2006 20:08 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

> Thanks Joseph & Nick,
>
[quoted text clipped - 6 lines]
> spreadsheet for modifications, and having only the actual fill-in
> fields unlocked.

Reply to this Message

Paul B - 19 Jun 2006 03:51 GMT

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

Signature

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

> Thanks Joseph & Nick,
>
[quoted text clipped - 6 lines]
> spreadsheet for modifications, and having only the actual fill-in
> fields unlocked.

Reply to this Message

Joseph R. Pottschmidt - 18 Jun 2006 19:11 GMT

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:kamenl@gmail.com]
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.

Reply to this Message

jkend69315@aol.com - 19 Jun 2006 15:37 GMT

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

Reply to this Message