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.

0
kamenl (2)
6/18/2006 5:55:34 PM
excel 39879 articles. 2 followers. Follow

6 Replies
754 Views

Similar Articles

[PageSpeed] 2

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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"heavyuser" <kamenl@gmail.com> wrote in message 
news:1150653334.193809.318980@c74g2000cwc.googlegroups.com...
> 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.
> 


0
6/18/2006 6:09:17 PM
This is a multi-part message in MIME format.

------=_NextPart_000_001F_01C692C7.E94F18A0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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.

------=_NextPart_000_001F_01C692C7.E94F18A0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7036.0">
<TITLE>Re: Easiest way to create forms with Excel?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Dear HeavyUser:</FONT>
</P>

<P><FONT SIZE=3D2>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.</FONT></P>

<P><FONT SIZE=3D2>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.</FONT></P>

<P><FONT SIZE=3D2>This all requires someone, if not you, to code all =
those kinds of conditions into the form/VBA code.</FONT>
</P>

<P><FONT SIZE=3D2>Joe P.</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>

<BR><FONT SIZE=3D2>From: heavyuser [<A =
HREF=3D"mailto:kamenl@gmail.com">mailto:kamenl@gmail.com</A>] </FONT>

<BR><FONT SIZE=3D2>Posted At: Sunday, June 18, 2006 10:56 AM</FONT>

<BR><FONT SIZE=3D2>Posted To: microsoft.public.excel</FONT>

<BR><FONT SIZE=3D2>Conversation: Easiest way to create forms with =
Excel?</FONT>

<BR><FONT SIZE=3D2>Subject: Easiest way to create forms with =
Excel?</FONT>
</P>

<P><FONT SIZE=3D2>Hi all - apologies if this is a FAQ question, was =
unable to find good</FONT>

<BR><FONT SIZE=3D2>answers there.</FONT>
</P>

<P><FONT SIZE=3D2>My organization often needs to distribute various =
'forms' designed in</FONT>

<BR><FONT SIZE=3D2>Excel. People will fill them in, (often print a copy =
and) send the</FONT>

<BR><FONT SIZE=3D2>filled-in sheets back to us.</FONT>
</P>

<P><FONT SIZE=3D2>There are a lot of problems with this: people will =
modify stuff where</FONT>

<BR><FONT SIZE=3D2>they're not supposed to, will leave 'mandatory' =
options blank, and the</FONT>

<BR><FONT SIZE=3D2>process of copying/pasting data for further =
processing once we get the</FONT>

<BR><FONT SIZE=3D2>filled in sheet is laborious.</FONT>
</P>

<P><FONT SIZE=3D2>For various reasons we need to stay with Excel (i.e. =
Access, or redoing</FONT>

<BR><FONT SIZE=3D2>everything as web-based, is not an option).&nbsp; =
What is the best way to</FONT>

<BR><FONT SIZE=3D2>design forms in Excel, provide some constraints to =
fields (e.g.</FONT>

<BR><FONT SIZE=3D2>'required', or 'must be a number') without extensive =
VBA coding, and</FONT>

<BR><FONT SIZE=3D2>without having to lock/unlock and password protect =
everything?</FONT>
</P>

<P><FONT SIZE=3D2>I imagine lots of people have this problem.&nbsp; Any =
3rd party solutions,</FONT>

<BR><FONT SIZE=3D2>free or not free, are appreciated too.</FONT>
</P>

</BODY>
</HTML>
------=_NextPart_000_001F_01C692C7.E94F18A0--

0
me4069 (7)
6/18/2006 6:11:14 PM
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.

0
kamenl (2)
6/18/2006 6:18:22 PM
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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"heavyuser" <kamenl@gmail.com> wrote in message 
news:1150654702.526523.147180@i40g2000cwc.googlegroups.com...
> 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.
> 


0
6/18/2006 7:08:28 PM
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

"heavyuser" <kamenl@gmail.com> wrote in message
news:1150654702.526523.147180@i40g2000cwc.googlegroups.com...
> 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.
>


0
6/19/2006 2:51:52 AM
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

heavyuser wrote:
> 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.

0
jkend69315 (38)
6/19/2006 2:37:08 PM
Reply:

Similar Artilces:

Create Invoice Permission
Guys, a strange one here..... I have users creating Invoices from sales orders OK for some Sales Orders but not for others - they get a message saying they have insufficient permissions. I cant figure out why they have permission on some but not all !! Any ideas ? Ian Hi Ian, Checkout the role of the user. Do he have create rights on invoices of all organization or only on some BU. Role can have rights on only to user owned, BU owned, BU and Child BU owned or organization owned. If User's role have only rights on BU owned invocies then he cannot create invoices for any orders...

Excel automation to access a workbook
I want to utililze Excel Automation to obtain properties from an Excel workbook. Given path of the Excel workbook, how can I get the properties of a workbook without making the Excel application or the workbook visible. Then obviously close the Excel workbook and the Excel Object after I get the properties Any help with this would be appreciated Here is some code I am using. I need help getting it to work. When I run it, I get an error message "Run-time error '-2147417851 (80010105)' Method 'Open' of object 'Workbooks' failed" I am in a form in Acc...

Margin is out of page when open using Excel 2003
All my excel file which saved on Excel XP are out of order when open using Excel 2003. Please advise! ...

Excel should allow chart axes to be set from sheet data.
I often chart account balances where I have x axis of dates and y axis of monetary amounts. It would be helpful if there was a facility to allow the x axis minimum and maximum to be set to some value as calculated from the data itself (or even calculated using a formula). I like to keep a "rolling" picture of the last three months (so, today()-90 to today()) and the next three months (today() to today() + 90) (on two separate charts). I have to manually update the x axis's minimum and maximum each day. you can set up formulas in cells to calculated your desired max/mins a...

How do I show hidden column and row headings in excel?
How can I display hidden column and row headings in excel? Excel displays only unhidden rows and columns, and their headings. But if you make the width or height small enough, it might still be possible to see the heading and not the data. However, this becomes more difficult as you move to column AB or row 101. "Mildred" <Mildred@discussions.microsoft.com> wrote in message news:D7D83F4B-289A-49A6-9057-23365EAFB300@microsoft.com... > How can I display hidden column and row headings in excel? ...

SOS! how do I run Excel without running the VBA in AUTO_OPEN?
Hi all, I am asking this for my friend. He has the following urgent problem. He put some VBA code into AUTO_OPEN (AUTO_RUN), basically it execute some other functions, and then automatically saves the closes out the Excel sheet. The problem now is that he wants to open that sheet to edit some stuff, now he's stuck -- whenever he opens the sheet, the sheet always auto- closes, and he had no way of operating it... What can he do? Is there a way to open a sheet without executing that AUTO_OPEN (AUTO_RUN) function? thanks a lot! Hold down the Shift key when ope...

export data from excel to adobe
If you have 2007, Microsoft has a free add-in that will add the ability to print to a file in .pdf format that works across all Office 2007 applications: http://www.microsoft.com/downloads/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en If you don't have 2007, then one of the easiest to use .pdf file creators is probably PDFCreator from SourceForge: http://sourceforge.net/projects/pdfcreator/ it installs as a virtual printer on your system and when you wish to create a .pdf file you simply print to that virtual printer. Instead of a sheet of paper, y...

Large shared excel files
I have an excel file that I use to manage clients. I have 1067 clients. I have 8 people in my department that edit the file. I have another 200 staff that have read only access to the file. Most of the time the file is about 1.3 MB in size. However, on occassion the file gets very slow and swells to about 25 mb. When I go to "tools" "Shared Workspace" it will show everyone who is currently signed on to edit the file. Sometimes however, it shows the same guy on three times. When this happens, the file creates a tmp file in the directory. It seems to me that excel ...

Data entry form
Hi, I wonder if there is possibility to make list field interactive, like when user put firs letter then form showing resluts where first letter = "user letter" ? Becouse I have over 800 records and it is very dificult for user to find the one is looking for. regards Peter Piotr This macro will do what you want. This is how it works. When the user puts a character, say c, in B1, the entire sheet will scroll automatically to put the first row that starts with a "c" at the top of the screen. Note that this is a sheet macro and must be placed in the sheet module ...

accessing outlook data using excel macro
Hey all, I'm very new with all of this, however, my problem is this: I have a bunch of data in excel which I then want to compare to certain "task" fields in outlook (ie. the "Due Date" column in Tasks for example), then update those outlook fields with new data. I was able to do it in an Outlook macro, but not in the Excel macro. I get a "User defined type not defined" error when trying to define a namespace variable as "Outlook.Namespace" Any help would be greatly appreciated. Thanks. ...

Remove file Protection from an Excel workbook file from others
I have an older excel file created by someone who is no longer with my company. I want to use that file as a starting point to created another file. The file is set up as "read only". If I try to change any of the cells, I get the message that the "cell or chart is protected and therefore read only" and further tells me to unprotect before attempting the change. When I got to tools/protection/Unprotect Sheet I get the request for a password. I don't know the password as the file was created by another. I have tried "save as", from tools making sur...

Linked Excel table
Hi, Hope someone can help me. I have weird printing problem that I can' seem to fix! Here's the story ... I am using Windows XP and Offic 2003. I link excel tables into a word document (paste special/as link) There is color font in the excel table. The color always appears on th screen, but disappears when I print it to pdf or printer, *but onl from certain PC's* Hence, the problem! I've compared the settings o Word and Excel between the PC that can print the colored text and th ones that cannot. To no avail. Any help would be appreciated. Thanks, Philomen -- Message posted f...

YAPTQ: Mixed data types in custom groups (Excel 2003)
Hello, I'm trying to get the following accomplished in Excel 2003: I have a column in a table that keeps track of how late a form was turned in. It can have an integer (for the number of days it was late) or one of several text strings ("On time," "Cannot determine," "Outside report dates.") I need to be able to generate the totals for: On time or within 7 days late, 8-15 days, More than 15 days Cannot determine Outside report dates It seems like it should be a straightforward procedure, where one can specify a range of numbers, or a list of values, but I ...

outlook 2003 custom form no RTF
I was using Outlook 2002 sp3 till now. Used to create custom forms, publish it (with the option, save form defination with item as 'NO') in the personal forms library. Send the custom form to myself, and when I receive it, it used to be in rich text format(RTF) and wud display the custom form as it is. But now, in the same machine, am using outlook 2003,and suddenly, the RTF, is not being displayed. Instead, the inbox has html file, so I can view the custom form.. Pls help.. Thanks in advance What's the value of the MessageClass property for the item you received? = Has your m...

Cut and paste from Excel to Powerpoint
Hi If I'm copying a table from Excel to Powerpoint, I usually use Paste Special, then select Picture (Enhanced Metafile) to give me flexibility to edit later in Powerpoint. However, I find that if my table has more than about 15 columns, the 'pasted' version is truncated. If I select another paste option (eg Bitmap), the whole table is pasted, but this naturally reduces the quality and flexibility of the output. Does anyone know how I can ensure that the whole table is pasted, whilst retaining Enhanced Metafile format? Many thanks, O. --- Message posted from http://www.Exce...

Is there no way to view individual messages in Exchange 03?
After much Googling, I've learned: 1) Exchange 2003 disabled the "M drive" feature, and 2) Messing around with individual messages in the M drive is a bad idea anyhow. So, is there anyway from the Exchange 03 System Manager to view/open/manage individual email messages? Or must I use Outlook or OWA to do this? Thanks... N2 You need Outlook or OWA to do it... -- -- Brian Desmond Windows Server MVP desmondb@payton.cps.k12.il.us Http://www.briandesmond.com "N2" <none@nunya.com> wrote in message news:_VNud.4773$ve.2836@fed1read06... > After much Go...

Does AutoHotKey record mouse in correct Vista compatible way?
Does anyone know if the free open source (I think) program AutoHotKey records keystrokes and mouse movements in a correct Vista compatible way? I know using Journal Hooks isn't the way to go anymore. www.autohotkey.com (it's a popular free program, so I thought perhaps someone here knows of it and might know details of how to record mouse & keyboard for playback.) ...

How to create a pie chart with 793 numbers? Possible?
Hi, I've been trying to create a pie chart with 793 numbers on one single colum, the column consists of various numbers and i would like to take a percentage of numbers on the column, for example there are 100 tens ranging from cell A1 to A99 and 500 1's ranging from cell A100 to A699 and other numbers, is it possilbe to create a pie chart with that many numbers? -- Every time i tried to create the pie chart using the chart wizard, the finished chart will always shown with so many different slices, any tips on how to create the pie chart would be greatly appreciated thanks in adv...

Excell: Column Changes
The Excell program changed the Column labels from alpha characters to numbers. This makes it somewhat difficult to use formulas. Any ideas on how to changes back the numbers to alphabet characters ??? Tools|options|General Tab|Uncheck R1C1 reference style Doug wrote: > > The Excell program changed the Column labels from alpha characters to numbers. This makes it somewhat difficult to use formulas. Any ideas on how to changes back the numbers to alphabet characters ??? -- Dave Peterson ec35720@msn.com Tools -- Options. On the View tab, under Settings, uncheck R1C1 style. Rgds, And...

Password Protecting Excel Sheet
Hi, Is it possible to create a password in order to get in to an excel file. When a user clicks on this file, they would be prompted for a password. I know how to password protect things once in the file, but I also want to lock anyone out who does not have a password. Thanks, Chuck Chuck, File/SaveAs In the upper right of the dialog box..... Tools/General Options. John "ChuckW" <anonymous@discussions.microsoft.com> wrote in message news:0d4101c3db78$a3a1e340$a301280a@phx.gbl... > Hi, > > Is it possible to create a password in order to get in to > an ex...

Linking to Excel from MS Project
I was wondering if there is a way so that I can link the Gant Chart (Picture) made in Project to a sheet in Excel... I know I can copy the image over and paste it into the sheet, but I wanted something that would be able to dynamically change on an update and I dont have to copy and paste all the time. Any help is appreciated. ...

Convert Excel to comma delimited text #3
Thank you! That did the trick. -- deacs ------------------------------------------------------------------------ deacs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2321 View this thread: http://www.excelforum.com/showthread.php?threadid=266960 ...

TAb forms in Access 2007
I am new to using Access 2007 and I would like to be able to split a large table across many forms using tabs as a way of doing it - like a normal filing system. E.g. tab for name, then contact details, training information, etc. I would be extremely grateful if someone could help me out, as I do not see a method in 2007. Barry, With you form open in design view, click on the Design Tab in the ribbon. One of the control selection buttons looks like a tab control. It should be on the right of the Check mark, but the tool tip will tell you. Click on it then click on your form. To...

customize view and form for KB articles
Hi all, I have found URLs on this newsgroup for (unsupported) customization of several CRM views and forms that don't offer this by default. However, the URL's for customization of the view and form for KB articles were not included. Can someone supply those? Thank you for replying. Basman Hi Basman, If you want to do unsupported customization of views (at your own risk) you can browse SavedQueryBase table in CRM database to find views and their ID-s. I found knowledge base views using following query: SELECT * FROM SavedQueryBase WHERE (Name LIKE '%KB%') ...

How do I combine several sheets into one document in Excel?
As you did not specify otherwise I presume there is something like Shee 1 has rows 1 to 250, sheet 2 has rows 1 to 500, and sheet 3 has rows to 250 etc. To join those you would highlight and Copy the rows in sheet 2, then then in sheet 1 click in cell A251 and Paste, then repeat the proces for sheet three into cell A751 etc. If you wish to match-and-merge data on the same rows from columns i another sheet that could be more difficult -- Bryan Hesse ----------------------------------------------------------------------- Bryan Hessey's Profile: http://www.excelforum.com/member.php?act...