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]

Delete that stuff, save your file, close it and reopen again.

Is it still there?

Its still there.

In fact even if i close it and open a blank excel sheet then right click on Sheet1 view code under General and declarations nothing is there but when i click on the drop down and change to worksheet the right hand side changes to SelectionChange and below


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

 

[wigi]

Yeah, obviously, that is how it works...

Why do you change the dropdowns if you do NOT want code in there... ?

 

[uk747]

Yeah, obviously, that is how it works...

Why do you change the dropdowns if you do NOT want code in there... ?

I dont want code in there, all I need is for the 1st workbook to stop flickering when I enter text in it.

When I view code its now blank but still get the flickering

all the macros in book 1 are just ones I recorded to copy cells and remove data etc. the longer ones I added the

Application.ScreenUpdating = False and
Application.ScreenUpdating = True

to stop it flickering slightly, this stopped it. Now I use another spreadsheet (book2), when this is open the flickering starts.

In Book 2 under view code I have two codes

Private Sub Worksheet_Change(ByVal Target As Range)

and


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

could either of these be causing the flickering in Book 1, if so can i add anything to them to stop the flickering either amend something in Book 2 or add something in Book1

 

[wigi]

Again, delete the events you do not use ! (but it will no stop the flickering).

One guess from me is that your Calculate event is updating cell A101, which fires the calculation, which updates A101, and so on.

Are there cells containing formulas based on A101?

 

[uk747]

I've found the cause for the flickers


However I need the code below as I was having trouble pasting some info below.

I have a formula in A1 which puts the result in cell A101, however I need a paste special of A1 to go in A101 is there another way to do it

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

 

[wigi]

I've found the cause for the flickers

So, what is it?

 

[uk747]

Again, delete the events you do not use ! (but it will no stop the flickering).

One guess from me is that your Calculate event is updating cell A101, which fires the calculation, which updates A101, and so on.

Are there cells containing formulas based on A101?

Not in Book 1. A101 just refers to Book 2 , it takes the result of the formula in A1 then paste special into A101.

I temporarily removed the code in book 2 and resaved it and when both reopened the flickering stopped so def.. the code above

 

[wigi]

Try to disable the events temporarily, while pasting to values.

 

[uk747]

Try to disable the events temporarily, while pasting to values.

Not sure what you mean. Shouldnt the code on Sheet 1 on Book 2 only refer to Book 2 or does it apply to all active excel books that are open

I even changed the ranges to A1001 and F1001 so the numbers arent the problem just the Private Sub Worksheet_Calculate() function

 

[wigi]

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