Excel 2003 - VBA - Forms

[Craig Brandt]

I am starting to use "userforms" to collect data. It can help ensure that
the data is valid and enforce placement.

When I inserted code "on field change" that transfered that data to the
spread sheet, there was a pause, unacceptable during data entry. I therefore
deferred changing the cells in the spreadsheet until all input was done in
the forms. This time the pause occured upon closure of the userforms, but at
17 secs, was a bit excessive.
My exit routine had a lot of the following types statements to transfer the
data: Sheets("AllAcct").Cells(2,3) = Userforms2.StartDate

Is there a better way of handling the data transfer?

Thanks,
Craig

 

[Jim Rech]

Sounds like calc mode is automatic. If so, change to manual at least while
your code is putting data in the worksheet:

Application.Calculation = xlCalculationManual
...place data
Application.Calculation= xlCalculationAutomatic

Also, if you have any event code running, like Worksheet_Change, you might
want to turn that off too.

--
Jim
|I am starting to use "userforms" to collect data. It can help ensure that
| the data is valid and enforce placement.
|
| When I inserted code "on field change" that transfered that data to the
| spread sheet, there was a pause, unacceptable during data entry. I
therefore
| deferred changing the cells in the spreadsheet until all input was done in
| the forms. This time the pause occured upon closure of the userforms, but
at
| 17 secs, was a bit excessive.
| My exit routine had a lot of the following types statements to transfer
the
| data: Sheets("AllAcct").Cells(2,3) = Userforms2.StartDate
|
| Is there a better way of handling the data transfer?
|
| Thanks,
| Craig
|
|

 

[Craig Brandt]

Jim:

In my younger years I sometimes tossed advice aside, assuming that it didn't
fit my application. As I get older, I find that it is best to restrict that
type thinking only to areas where I am an expert (still looking for that
area).

My first thought was that your advice didn't fit because the data being
transferred wasn't being used in any calculations. Still, following my own
advice, I tried it and it worked much better.

Excel sometimes defies logic.

Thanks a bunch,
Craig