Screen Flickering with this VBA

[robertk93]

Hi,

Please see link to a file sharing site with a workbook inside of it. I am trying to solve the screen flickering within this workbook. The password to unprotect is test.

Thank you!

Book.xlsx

 

[robertk93]

Please see updated link!
test.xlsm

 

[Alex Blakenburg]

Try putting this after the Screenupdating True/False lines

' At the start
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


' At the end
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

 

[robertk93]

Thank you! The further down the worksheet I go, the worse the flickering and lagging gets. Any idea?

Try putting this after the Screenupdating True/False lines

' At the start
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


' At the end
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

 

[Joe4]

Hmmm... I entered a handful of values and do not get any flickering.
Can you tell us exactly what you do that causes screen flickering to happen?
What values are you entering in on which sheet/range?

Give us a very DETAILED and SPECIFIC example, so we can try to replicate what you are seeing.

Note that it might more sense that instead of having code that runs every time an update is made (and a lot of updates are being made at once), to have them make all the updates and then click some sort of button (like "Submit" or "Process") that runs the VBA code just once then.

Or create a User Entry form where they can enter the values, and then once submitted, the VBA code places them where they belong and runs the hidden row logic.

 

[robertk93]

As I enter a value on the Proforma sheet in column B, and then hit enter to enter the next value, the blue loading circle populates and then flickers.

As I move down the sheet to the Payables Services or Wire Transfer Services, the flickering gets worse.

I like your idea of the submit or a user entry form where they enter the values and it runs at once.

How would I go about that?

Thanks,

Rob

 

[Joe4]

As I enter a value on the Proforma sheet in column B, and then hit enter to enter the next value, the blue loading circle populates and then flickers.

As I move down the sheet to the Payables Services or Wire Transfer Services, the flickering gets worse.

That is not quite what I had in mind. I wanted actual examples I could copy (which is which I capitalized DETAILED and SPECIFIC), i.e. enter 123 in cell A23, then 456 in cell J23, etc.
I want to do EXACTLY what you are doing, because when I just start entering in numbers in random spots on the sheet, it works just fine for me (no lag or screen flickering).
So I wanted to do the EXACT same steps (values and cells) that you are doing.

I have a feeling that it might not be an Excel thing at all, but perhaps a computer or network issue on your side.

I like your idea of the submit or a user entry form where they enter the values and it runs at once.

How would I go about that?

Google "Create Userform in Excel VBA". You will find many different tutorials on the topic.
Here is one to get you started: Userform in Excel VBA

 

[robertk93]

Gotcha- sorry about that.

Enter the number 1 B85 and then hit enter and enter the number 1 in B86 and then hit enter and put ones until B87

Go down to B89 and enter 1 and then hit enter and do the same on B90 through B96.

Go down to B99 and do the same as above.

Thank you.

 

[Joe4]

OK, it wasn't actually working before because Microsoft's new security settings disabled the macros for me.
I re-enabled them, and I think I see what is going on. I think you are seeing that flickering because you are bouncing back and forth between sheets.
You are entering the values on one sheet, and then going to the other sheet to unhide rows, and then come back.
I do not know of any way to get rid of that.

However, if you do try the User Form method, or the other one I suggested, at least them you could just run through the code once after all the entries, instead of after each and every entry.

 

[robertk93]

Got it. It is automatically unhiding the rows, I am not toggling the sheets. Thank you for your help!