Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute Worksheet_Change before recalculation
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute Worksheet_Change before recalculation
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 -- Regards, Tom Ogilvy "BigJimmer" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute Worksheet_Change before recalculation
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 In article , BigJimmer wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute Worksheet_Change before recalculation
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! "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "BigJimmer" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recalculation | Excel Programming | |||
Worksheet_Change event won't fire to execute Macro??? | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |