Userform Question

[Steph]

Hi everyone. I have a userform that has 200+ combo boxes and text fields.
(The form is a multipage - one tab of 40 fields, 12 pages for each month).

When I write the data from the form to the database (excel sheet), I am
currently doing it one field at a time. Is there an easier, more efficient
way to write block of data? Each tab of the form represents a month, and
each month will be on a seperate line in the datasheet.

Thanks!

 

[Harald Staff]

Hard to say without running your app and knowing how fast/slow it is. At a
philosophical level; at one point each single object must be evaluated, one
by one, so it may well be perfect as is.

The by far slowest part of operations like these is the user interaction, so
if you can make your code store the user's choice in the right place while
she she releases her mouse or while the keyboard key is on its way up, then
it's as fast as it possibly gets.

HTH. Best wishes Harald

 

[Guest]

Hi everyone. I have a userform that has 200+ combo boxes and text fields.
(The form is a multipage - one tab of 40 fields, 12 pages for each month).

When I write the data from the form to the database (excel sheet), I am
currently doing it one field at a time. Is there an easier, more efficient
way to write block of data? Each tab of the form represents a month, and
each month will be on a seperate line in the datasheet.

Thanks!

You probably already have this, but if not it will speed up immensely if you sandwhich your code with

application.calculation = xlmanual
application.screenupdating = false
....
....
application.calculation = xlautomatic
calculate
application.screenupdating = true

 

[Tom Ogilvy]

basically, the computer will have to execute an individual write for each
control.

There is no method to write a block of data unless you loop through your
controls and populate an array, then write the array to the worksheet.