Hi there, I have a set of macros that run and then I need to turn calc back
on. This all worked fine until I introduced a UserForm (that I use to control
the flow of the work and for monitoring). Now, whenever I execute
Application.Calculation = -4105
either by a code line or into the immediate window Excel hangs for anything
up to 3 or 4 minutes (the status bar says "Calculating Cells 100%) and then
the hourglass comes up and Excel is completely frozen. From there the only
way out is to crash it through the Task Manager.

BTW, the UserForm is brought up with Show 0 so that it's modal.

Oddly enough, sometimes it just requires me to activate the Task Manager,
and I can then go back to the spreadsheet and continue working.

Even more odd is that in the immediate window I can either calculate a sheet
or just do Calculate and it only takes a moment (as you would expect).

What can cause this behaviour (anything to do with UserForms?) and how can I
solve it please? Regards, Brett.

Are you using VISTA and HOW much memory is installed????????

The program is probably saving a backup of the file in the TEMP folder.
Part of the process of saving the file requires storing the data into memory.
If you don't have a lot of memory on your PC the computer starts performing
SWAPS of memory storing blocks of memory on your hard drive to free up
memory. The swapping to the hard drive is a very slow process. VISTA
requires lots of memory and when you have a large application running (excel
with lots of data) there is a conflict with the memory. Operating systems
like Windows/Vista must free up memory and the process is to store unused
blocks of data in memory to a temp area on the harddrive. The access to the
harddive is much slower the reads/writes to memory.

"Brett" wrote:

> Hi there, I have a set of macros that run and then I need to turn calc back
> on. This all worked fine until I introduced a UserForm (that I use to control
> the flow of the work and for monitoring). Now, whenever I execute
> Application.Calculation = -4105
> either by a code line or into the immediate window Excel hangs for anything
> up to 3 or 4 minutes (the status bar says "Calculating Cells 100%) and then
> the hourglass comes up and Excel is completely frozen. From there the only
> way out is to crash it through the Task Manager.
>
> BTW, the UserForm is brought up with Show 0 so that it's modal.
>
> Oddly enough, sometimes it just requires me to activate the Task Manager,
> and I can then go back to the spreadsheet and continue working.
>
> Even more odd is that in the immediate window I can either calculate a sheet
> or just do Calculate and it only takes a moment (as you would expect).
>
> What can cause this behaviour (anything to do with UserForms?) and how can I
> solve it please? Regards, Brett.

Hi joel, thanks for reply : XP 2003 and from memory(?) 4GB of ram so I'm
thinking that that is not the problem(?). Regards, Brett

"Brett" wrote:

> Hi there, I have a set of macros that run and then I need to turn calc back
> on. This all worked fine until I introduced a UserForm (that I use to control
> the flow of the work and for monitoring). Now, whenever I execute
> Application.Calculation = -4105
> either by a code line or into the immediate window Excel hangs for anything
> up to 3 or 4 minutes (the status bar says "Calculating Cells 100%) and then
> the hourglass comes up and Excel is completely frozen. From there the only
> way out is to crash it through the Task Manager.
>
> BTW, the UserForm is brought up with Show 0 so that it's modal.
>
> Oddly enough, sometimes it just requires me to activate the Task Manager,
> and I can then go back to the spreadsheet and continue working.
>
> Even more odd is that in the immediate window I can either calculate a sheet
> or just do Calculate and it only takes a moment (as you would expect).
>
> What can cause this behaviour (anything to do with UserForms?) and how can I
> solve it please? Regards, Brett.