Flickering

[uk747]

I have 2 different excel workbooks when the second one is open and i start entering data in book 1 it flickers whenever i enter anything.

I do have macros on both. In Book 1 a couple of the macros are long and did flicker so I entered

Application.ScreenUpdating = False at the top of the macro


Application.ScreenUpdating = True at the bottom of the macro

that solved the problem on book 1, however when i now open Book 2 and enter stuff in Book 1 it flickers again

Is there a away to use something similar below to cover the whole book to stop it flickering

Application.ScreenUpdating = False


Application.ScreenUpdating = True

 

[uk747]

If you calculate any worksheet, it calculates all sheets and all workbooks in the entire application...

ok maybe i need to change the calculate function, isnt ther a formula which will paste special the reuslt in a new cell.

i.e. A1 = vlookup(d1,e1:g1000,3,0)

in A100 I want the paste special value of A1

 

[wigi]

No there is no such function. That's why it's a formula ( = calculated on the fly and always), instead of VBA-code (which can be used to change the output to hard values). That last thing could also be done manually with a Copy/Paste Special>Values action.

 

[uk747]

No there is no such function. That's why it's a formula ( = calculated on the fly and always), instead of VBA-code (which can be used to change the output to hard values). That last thing could also be done manually with a Copy/Paste Special>Values action.

Cant do the manual copy paste special as I have a macro which checks the contents then pastes somewhere else, somenow need something similar to the calculate function which wont cause the sheet to flicker

 

[uk747]

so is there any other way i can do this. ??

Recap , currently using

Private Sub Worksheet_Calculate()
Range("A101").Value = Range("A1").Value
End Sub

which paste specials the value only into A101, however when i use another excel workbook that on has afew calculationsa and acros in it and when i enter any values or test in cells it flickers alot.

If I remove the worksheet_calculate() from book 2 it doesnt flicker.

I either need an alternative formula/VBA or something to add to book 1 to stop it flickering like below which I used in Book1 to stop long macros from flickering

Application.ScreenUpdating = False at the top of the macro


Application.ScreenUpdating = True at the bottom of the macro

 

[wigi]

Try to turn off the calculation temporarily.

Application.Calculation = xlCalculationManual
'your code
Application.Calculation = xlCalculationAutomatic

during the Calculate event.