# URGENT: Excel 2003 form fields, tab order



## foxcole (Mar 18, 2009)

(Also posted in the Microsoft Excel discssion group: http://www.microsoft.com/communitie...2ba33de8be2f&lang=en&cr=US&sloc=en-us&m=1&p=1)

My customer sent me some Excel sheets that I'm meant to convert into forms. These are simple forms to facilitate user input (for service technicians to fill in checklists and create service estimates to be signed by customers) and will not be tied to any databases.

I'm having trouble figuring out how to do this. I'd want checkboxes and radio buttons for the checklist items, and textboxes for typed information. Simple enough, right? But, it isn't... or maybe I'm hopelessly ignorant. I haven't created an Excel form since Office 97, and then it wasn't this kind of form.

The Control toolbar does have all the field types I need and it's easy to create the fields on the Excel sheet... but I cannot see how to set a tab order for them.

So I next looked at just using the Excel cells instead of form fields, because I found a macro that creates a tab order... but we really do need the checkboxes and radio buttons, so this approach wouldn't work.

I've spent more than a day and a half trying to weed through information in Google searches and tutorials and Help files and discussion groups, but I cannot find this bit of information---not even anything to indicate it's not possible. All I can find are instructions on creating an Excel form tied to a database or on creating UserForm dialogs to populate spreadsheet columns.

We need to try to use as little VBA as possible, because the form owners won't be able to support it if anything changes or if problems arise. These forms need to be as up-front WYSIWYG as possible, just fill, print, sign and date.

Could someone point me in the right direction? Is it possible to do this simply? Have I just been looking in all the wrong places for the right information?

Also, just to note, we are not considering PDF forms; they need to be in
Excel. The Adobe Reader EULA limits each PDF form to 500 uses (not 500 per
user---500 total) before it's necessary to invest in Adobe's very expensive
server-based forms software. We have too many users for PDF forms to be
practical. (Adobe does not monitor form uses or number of distributions; it's
an honor system. But regardless of what others are doing or how undetectable our usage might be, I cannot consciously put our company in an indefensible position.)

I can't find any answers, and no one is responding in the other forum where I posted this query. I've been smacking up against brick walls for too long. It's starting to hurt, and these forms need to be done tomorrow.

Please, please, please help. Any advice at all would be gratefully received!

---Fox


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome.

In the VB Editor, highlight any of your controls, and make sure you can see the Properties window (if not press F4). In the list of properties for the control, look for TabIndex in the left column. You can change the number in the right column to put them in the order you need. Start with zero as your first stop and then continue from there.

Forms are very useful in Excel - you can control a great deal of the way they operate and I've created numerous forms for user input.


----------



## foxcole (Mar 18, 2009)

Thank you very much for replying!

I'm having a little trouble with this, though. Let's say I've drawn several textboxes from the Control Toolbox, named them and set cosmetic properties, and double-clicked one to open the VB editor where I have the Properties displayed. (Because these textboxes won't be doing anything but holding text, I haven't written any macro code.) I can use the drop-down list in the Properties dialog to display each text box.

The textboxes do not at this point have a TabIndex property. After SpecialEffect in the properties list is TabKeyBehavior, then Text.

Should I be doing something different to see a TabIndex property there?

Thanks!
---Fox
former piper and lifelong advocate of the wild haggis


----------



## Glaswegian (Sep 16, 2005)

Hi

*Don't double click* - just single click to highlight each control. The properties should appear in the bottom left hand pane - here's an image to illustrate










You can change the TabOrder value to suit.


----------



## foxcole (Mar 18, 2009)

Oh -- I see. I think perhaps we have a little miscommunication. As mentioned, I was adding controls from the Control toolbox (ActiveX)... but by your screenshot, you're doing user forms.

I'm not doing user forms because, from the little I understand, these are created with Data> Form, they require a spreadsheet range with labeled columns, and are meant for populating the range with data entered in a dialog box. But that's not the kind of thing I'm working with. 

The data in these forms isn't populating ranges, it's scattered around the form wherever something needs to be written in. It's just a means to make filling out the form a little easier for the tech... type, tab, select a checkbox, tab, calculate a couple of values based on what the tech types in, etc.... print, sign and go. The sort of thing that, if I had designed it, might have been done in Word, but these are in Excel because that's what the techs have.

So I was using the Control toolbox because with it, I could draw form fields wherever I needed them on the page and because I don't need to tie it to any database. If I'm wrong and UserForm can in fact be used to create this kind of form layout, please tell me where to find out how!

I apologize for asking this of you, but I've been trying to use the Excel help and find it severely lacking... too uninformative, too scattered, too difficult to find what I need. And it seems to insist that if you're creating a form, you must surely use Access. 

For example, according to the Help topic "About data entry forms" the worksheet form seems to be what I need:

If you need a sophisticated or specialized data entry form, you can create a worksheet or template to use as a form and then customize the worksheet form to meet your needs. *For example, you might create an expense report form for people to fill out online or in printed form.*[emphasis mine]​
However, it does not say where to find instructions on how to go about it, and the only "related topic" in that topic takes us right into setting up Access. I tried looking up "worksheet form" in Help... oof! Using quotes around the term, it only finds that same "About data entry forms" topic. Without quotes, it returns everything containing either word but nothing useful to me. Hopeless.:sigh:

So, it appears I'm still where I started, since there is no TabIndex property. (If one can create a field on a page, there must surely be a way to tab to it! And control which order to tab in.)


----------



## foxcole (Mar 18, 2009)

Unless we really are talking about the same thing but I just don't know how to create a user form like that from the fields I created.


----------



## foxcole (Mar 18, 2009)

CORRECTION -- 

I misread the help topic "About data entry forms". That isn't what I want after all. I overlooked the introductory sentence in that Help topic, which reads "Microsoft Excel provides the following types of forms to help you enter data in a worksheet range."
:redface: 
I'm not entering data in a worksheet range.


----------



## Glaswegian (Sep 16, 2005)

My apologies - I misunderstood.

If you are simply creating controls such as a textbox on a worksheet, then I'm not aware of any method to set a tab order. Using a form, as I detailed in my earlier posts, would allow you to set a tab order.


----------

