URGENT- Form design and field tab-order problems

[Foxcole]

I've tried asking this question in other forums but either got no
response, or the one responder I did have couldn't provide an answer.
Maybe I've been asking the question the wrong way. Let's try this:

A requester sent several Excel files that have been formatted to print
out as forms. These forms should be filled out electronically by
service technicians in the field, then printed and signed by
customers.

The reason the forms are in Excel is that all service techs already
have and use Excel.

The forms do not need to fill in any worksheet ranges or databases.
All they need to do is provide clickable checkboxes, radio buttons,
and fields for entering comments, customer information and dates.

The service tech needs to be able to tab from field to field. Not
every field will need to be filled out each time the form is used.

Problems:
Because the forms do not use worksheet ranges or database, I chose to
create fields from the Control toolbox.

But I don't know how to set a tab order for these fields. I also don't
know what other types of actions are usually coded for the types of
fields I need, let alone where to find code I can use.

I imagine I should set a background shade when a textbox or checkbox
field is in focus, but how?
What considerations do checkboxes require?
How are radio buttons handled?
How can I control where the focus goes next when the user hits Tab?

I've spent almost two weeks trying to research this online. Microsoft
help pages are severely lacking in this level of detail. It took three
or four days just to discover that the Control toolbox creates ActiveX
controls.

Learning that finally enabled me to understand that I need to use VBA
to work with these fields---so I tried to sit down with the VBA help
files and start learning how to code them, but I just don't have time
to sift through it all.

Please help, if you can!! I'm desperate -- this project is several
days overdue now, and I have to get it done so I can move on. (It has
not helped at all that the Microsoft newsgroups have had connection
problems, "service temporarily unavailable", making my searches even
less fruitful and slower than they might have otherwise been.)

 

[JCS]

Hi,

From I can see you cannot accomplish what you want in the manner you are
doing it. One way to accomplish the tabbing from field to field is by
creating an Excel User Form, but this would require you to get into the
Visual Basic Editor (Alt-F11). Then you would select Insert from the menu
bar and then UserForm. This would then display the Toolbox and you would
place your controls on the form. Then in the properties box to the left
there is a TabIndex property for each control you could set. You would have
to create a macro button in the spreadsheet with a macro assigned to it that
launches the form. The only problem with this method (and it is significant
in your case) is that there is no other way to print the form other than a
print screen and copy to Word. The only other thing you can do is to create
a form in Word. The Word forum should be able to help you with this.

John

 

[Jim Cone]

What does "create fields from the Control toolbox" mean ?
That statement is completely foreign to me and I have used Excel
for at least a couple of months. Is it something from xl2007 ?

As far as tabbing from one input cell to another...
The input cells should be set up so the "next" cell is to the
right or below the previous one.
Then unlock those cells.
Then protect the workbook; with the options... "Select locked cells " off
and "Select unlocked cells" on.
--
Jim Cone
Portland, Oregon USA


Foxcole" <[email protected]>
wrote in message
I've tried asking this question in other forums but either got no
response, or the one responder I did have couldn't provide an answer.
Maybe I've been asking the question the wrong way. Let's try this:
A requester sent several Excel files that have been formatted to print
out as forms. These forms should be filled out electronically by
service technicians in the field, then printed and signed by customers.
The reason the forms are in Excel is that all service techs already
have and use Excel.
The forms do not need to fill in any worksheet ranges or databases.
All they need to do is provide clickable checkboxes, radio buttons,
and fields for entering comments, customer information and dates.
The service tech needs to be able to tab from field to field. Not
every field will need to be filled out each time the form is used.

Problems:
Because the forms do not use worksheet ranges or database, I chose to
create fields from the Control toolbox.
But I don't know how to set a tab order for these fields. I also don't
know what other types of actions are usually coded for the types of
fields I need, let alone where to find code I can use.

I imagine I should set a background shade when a textbox or checkbox
field is in focus, but how?
What considerations do checkboxes require?
How are radio buttons handled?
How can I control where the focus goes next when the user hits Tab?

I've spent almost two weeks trying to research this online. Microsoft
help pages are severely lacking in this level of detail. It took three
or four days just to discover that the Control toolbox creates ActiveX
controls.
Learning that finally enabled me to understand that I need to use VBA
to work with these fields---so I tried to sit down with the VBA help
files and start learning how to code them, but I just don't have time
to sift through it all.
Please help, if you can!! I'm desperate -- this project is several
days overdue now, and I have to get it done so I can move on. (It has
not helped at all that the Microsoft newsgroups have had connection
problems, "service temporarily unavailable", making my searches even
less fruitful and slower than they might have otherwise been.)

 

[Foxcole]

Hello, Jim. Thanks for replying!

What does "create fields from the Control toolbox" mean ?
That statement is completely foreign to me and I have used Excel
for at least a couple of months.  Is it something from xl2007 ?

Excel 2003 and as far as I know, at least back to Excel 97... View>
Toolbars> Control Toolbox.

As far as tabbing from one input cell to another...
The input cells should be set up so the "next" cell is to the
right or below the previous one.

I seem to be having great difficulty creating an accurate picture of
what I'm working with so people can understand the requirements.

I can't sent up input cells so the next cell is adjacent to the
previous one. In this particular document (unlike another I've asked
about in a different thread) I'm not using input cells. I'm trying to
"tab" or, more accurately, control focus from one field to the next in
a prescribed order, when the user hits the Tab key.

These fields or controls are merely sitting on top of a page layout
that the sender created in Excel... which is otherwise a paper form
just like any other paper form, and could be printed and filled out
entirely by hand if the service tech chose to do that.

I have no say in how the form looks or where the fields are located.
The form isn't tied to any worksheet range or database or anything.
It's just a way to put marks on the form page. My understanding is
that these are entirely handled with VBA, so I'm hoping for help
coding how each field looks when it's in focus, and how to pass focus
to the next -- and previous -- field when the user hits Tab.

 

[Jim Cone]

"Field" in a broad sense is another name for "Column".
A "Control" is Not a field.
Apparently you have an Excel worksheet with controls on it.
I would assume buttons, listboxes and combo boxes (dropdowns).
You want to move between the controls (in a particular order) using the Tab key?
'--
The following code goes in the module for the sheet with the controls.
It assumes there are 3 controls on the sheet: CommandButton1, ScrollBar1
and ComboBox1. (Every control on a sheet needs the associated "KeyDown"
code - 10 controls needs 10 sets of code in the module.)
For each control the "next" and "previous" control must be entered in the code.
The code come from a post by Rob Bovey in 2003 (with slight modification).
--
Jim Cone
Portland, Oregon USA

'------
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim bBackwards As Boolean
Const ctlPrev As String = "CommandButton1"
Const ctlNext As String = "ScrollBar1"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
bBackwards = CBool(Shift) 'JBC
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If bBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub
'--
Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim bBackwards As Boolean
Const ctlPrev As String = "ScrollBar1"
Const ctlNext As String = "ComboBox1"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
bBackwards = CBool(Shift) ' JBC
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If bBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub
'--
Private Sub ScrollBar1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim bBackwards As Boolean
Const ctlPrev As String = "ComboBox1"
Const ctlNext As String = "CommandButton1"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
bBackwards = CBool(Shift) ' JBC
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If bBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub
'-----------




"Foxcole" <[email protected]>
wrote in message
Hello, Jim. Thanks for replying!

What does "create fields from the Control toolbox" mean ?
That statement is completely foreign to me and I have used Excel
for at least a couple of months. Is it something from xl2007 ?

Excel 2003 and as far as I know, at least back to Excel 97... View>
Toolbars> Control Toolbox.

As far as tabbing from one input cell to another...
The input cells should be set up so the "next" cell is to the
right or below the previous one.

I seem to be having great difficulty creating an accurate picture of
what I'm working with so people can understand the requirements.

I can't sent up input cells so the next cell is adjacent to the
previous one. In this particular document (unlike another I've asked
about in a different thread) I'm not using input cells. I'm trying to
"tab" or, more accurately, control focus from one field to the next in
a prescribed order, when the user hits the Tab key.

These fields or controls are merely sitting on top of a page layout
that the sender created in Excel... which is otherwise a paper form
just like any other paper form, and could be printed and filled out
entirely by hand if the service tech chose to do that.

I have no say in how the form looks or where the fields are located.
The form isn't tied to any worksheet range or database or anything.
It's just a way to put marks on the form page. My understanding is
that these are entirely handled with VBA, so I'm hoping for help
coding how each field looks when it's in focus, and how to pass focus
to the next -- and previous -- field when the user hits Tab.

 

[Foxcole]

That's great! Thanks! I can work with that... looks like it's exactly
what I needed.

"Field" in a broad sense is another name for "Column".
A "Control" is Not a field.

Sorry for the confusion. I'm an old-school mainframe programmer, cut
my teeth on machine code and COBOL. Anything that was set up to
receive input or display data was/is called a field. Apparently I'm
not familiar enough with Excel terminology to have realized the
difference in meaning, but am grateful you took the time to look past
the exact terms to understand what I was trying to describe.

You've been very, very helpful. Thanks!