Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT- Form design and field tab-order problems
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.) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT- Form design and field tab-order problems
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 "Foxcole" wrote: 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.) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT- Form design and field tab-order problems
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" 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.) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT- Form design and field tab-order problems
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT- Form design and field tab-order problems
"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" 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT- Form design and field tab-order problems
That's great! Thanks! I can work with that... looks like it's exactly
what I needed. On Mar 25, 7:12*pm, "Jim Cone" wrote: "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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Active X Controls Tabbing from Form Field 2 Form Field | Excel Discussion (Misc queries) | |||
Order form design | Excel Discussion (Misc queries) | |||
how to design a form | New Users to Excel | |||
Design a form | New Users to Excel | |||
design form? | New Users to Excel |