I have created a data request form for the company in Excel. The form has quite a lot going on (e.g. frames, option buttons, tick boxes etc). I've created all the necessary code and event handlers so that the form behaves as it should.
In an attempt to record the user's input, I've then gone on and written a bit of code, which executes when the user clicks OK after completing the form. This bit of code loops through each control on the form and, depending on the control type, writes its caption or value to a blank spreadsheet. The issue I'm having is the order in which it loops through the controls. The tab order for the whole form, including that within frames, is spot on, yet this bit of code loops through some of the controls in the wrong order. I've noticed that some controls, which I added towards the end of this little project, but who's tab order is way up the top, appear last when I loop through. Similarly, if I loop through all the controls, writing their name and type to the debug window, controls I most recently added appear at the bottom. Is it therefore to do with the order in which you added a control
Can anyone recommend a better way of permanently recording a user's input into the form
Cheers for any help!
Keith
Userform Controls
chaza
Ray Hayes
Hi Duck Thing,
I know the tab order has nothing to do with the order in which the controls were added to the form, however, I would expect a routine to loop through the controls on a form according to the tab order, which seems logical to me, but it's not doing this. It seems to be doing it based on the order I added them. That's firstly what I'm trying to establish, and if so, can you change the order in which it loops through
I'll give your solution a go though and see how I get on.
Cheers for your help!
Keith
jitendra badkas
anders_sms
Alright,
Use the tag property of the control. You can put some information in a control's tag property so that when you loop over the controls you can use that information to help decide where to store it's data.
Thomas Ivarsson
Keith, you're on the right track here. The tab order has nothing to do with the order in which the controls were added. I'd recommend storing the data in a format that notes both the name of the control and its value. Something like this (pseudocode):
open filename for output as filenum
for each currentcontrol in myform.controls
print #filenum, currentcontrol.name & "," & currentcontrol.value
next currentcontrol
When you re-load the form:
open filename for input as filenum
do
get next line from file
parse comma-delimited fields into ControlName and ControlValue
myform.controls(ControlName).value = ControlValue
loop
The advantage here is that, in addition to making your data file easier to understand, you can now add new controls to your form without breaking anything.