Execute Worksheet_Change before recalculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BigJimmer - 19 Sep 2007 16:56 GMT

Hi,

I have an application where new data is pasted into a worksheet, and since
the data changes slightly each time, the data types need to be reset after
each import. I have VBA code to reformat the data types based on what is in
the newly imported data.

I need to run this reformatting before the workbook recalculates, otherwise
there are errors that can occur in using this new data.

I had included the reformat code in Worksheet_Change, and found that this
runs after the recalculation is completed. I have tried this in other events
also with the same result.

I have also tried trapping the resulting error (13 - type mismatch) and then
having the error logic run the reformat code. This didn't work either, as
the data types will not update when run at this time. I'm thinking it is
due to being withn the recalculation step, though I'm not sure.

I considered setting recalculation to manual, however, since an end user
could change the setting back to automatic, I wanted to find another way
around this.

Any ideas on how this can be handled?

Thanks!

Reply to this Message

Tom Ogilvy - 19 Sep 2007 17:12 GMT

You could use the selection change event to set calculate to manual if the
user is about to paste

' global variable in a Standard module
Public Calcmode as Long

' in the sheet module of the sheet where you need this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

if application.Cutcopymode <> False then
calcmode = Application.Calculation
Application.Calculation = xlManual
End if

End Sub

and then set it back to the users selection at the appropriate time (using
the calcmode variable).

Application.Calculation = calcmode

Signature

Regards,
Tom Ogilvy

> Hi,
>
[quoted text clipped - 22 lines]
>
> Thanks!

Reply to this Message

BigJimmer - 19 Sep 2007 17:34 GMT

I had tried something like this in the change event as I struggled with the
timing issue. Didn't occur to me to use the selection change event.

Thanks, Tom!

> You could use the selection change event to set calculate to manual if the
> user is about to paste
[quoted text clipped - 44 lines]
> >
> > Thanks!

Reply to this Message

JE McGimpsey - 19 Sep 2007 17:23 GMT

One way to maintain calculation in manual while your workbook is active:

Put this in a regular code module:

Public nOldCalcMode As Long

Put this in your ThisWorkbook code module:

Private Sub Workbook_Open()
nOldCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = nOldCalcMode
End Sub

Private Sub Workbook_Activate()
nOldCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
End Sub

Private Sub Workbook_Deactivate()
Application.Calculation = nOldCalcMode
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox Application.Calculation
Application.Calculation = xlCalculationManual
End Sub

> I considered setting recalculation to manual, however, since an end user
> could change the setting back to automatic, I wanted to find another way
> around this.

Reply to this Message