Excel not updating after macro

Question

My Excel workbook doesn't update after running a macro until a double click with the left mouse button despite having

Application.ScreenUpdating = True

At the end of the code. I tried selecting and activating a cell to no avail

Application.ScreenUpdating = True
Range("A6").Select
Range("A6").Activate

The macro is quite intensive in memory for about 40 seconds and then it is complete. I don't want to post the whole macro as some of it is proprietary. However it's a standard macro using built in Excel functions.

What do you mean "doesn't update"? Calculate? Screan update? What does your code do? This is not enough to reproduce the error. — vacip
– vacip, Commented Dec 19, 2015 at 15:42
A problem exists in that part of the code that you have chosen not to post. — Gary's Student
– Gary's Student, Commented Dec 19, 2015 at 15:42
Ok, just to be clear: the problem you mentioned is not an excel error. It is your macro. It does something wrong. We can't help you unless you post your code, or the part that produces the error. — vacip
– vacip, Commented Dec 19, 2015 at 15:52
The operations will complete without error, but the the workbook screen does not update to show the changes until an event such as a mouse double click. Everything that I disabled during the code is re-enabled in the final steps before the end sub. — kestertonj
– kestertonj, Commented Dec 19, 2015 at 16:01
The problem also only exits when the file size for importing and manipulation is large. There are no problems with a 1mb csv file but a 3.6mb csv file causes the issues which is why I don't think its a code problem. Is there any way that I can force Excel to update the screen once the userform has closed? — kestertonj
– kestertonj, Commented Dec 19, 2015 at 16:09

SilentRevolution · Accepted Answer · 2015-12-19 16:58:04Z

3

Are you using a Form Control button on a sheet with a macro assigned to it? If so I've been having issues with the sheets not being properly activated and your workaround of selecting a different sheet and then back again was the same workaround I initially employed.

If that is the case, try replacing the Button (Form control) with a Command Button (ActiveX control) and add the sub below to the correct sheet.

'You can create this by right clicking the button after placing it and click 'View Code'
Private Sub CommandButton1_Click() 'Or Private Sub WhatEverYourButtonIsCalled_Click()
    Call TheMacroYouWantToUse
End Sub

I understand this might be more comment worthy than answer worthy but I'm still 8 reputation points shy of being able to comment freely so I will apologize in advance.

answered Dec 19, 2015 at 16:58

SilentRevolution

1,5132 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NinjaLlama · Accepted Answer · 2015-12-19 16:18:09Z

1

I would check to see if the workbook is in manual calculation mode and then try and force the worksheet to recalculate at the end of the macro.

Worksheet(1).Calculate

answered Dec 19, 2015 at 16:18

NinjaLlama

1653 silver badges15 bronze badges

5 Comments

kestertonj Over a year ago

This does seem to update some of the cells to display but unfortunately not all of them. The workbook functions normally again if I switch to another Sheet and then Back again. All of the cell values exist but excel seems to have a graphical glitch that needs the worksheet reloading. Is there any workaround for this?

vacip Over a year ago

Have you tried your code on another computer? Maybe it is just you computer that gets fried.

NinjaLlama Over a year ago

@kestertonj What about adding a workbook refresh instead Workbooks(1).RefreshAll

kestertonj Over a year ago

Unfortunately this didn't give the desired effect either. I have just tried the macro on another version of Excel (Excel 2010) on another computer and it runs without any problems at all. Therefore the problem is either, Office 365 or my computer graphics. Thank you very much for your help.

vacip Over a year ago

Glad you figured it out. Since this question is not about programming, but a simple hardware error, please consider deleting it.

Bianary · Accepted Answer · 2016-01-31 19:35:02Z

1

As I can't comment due to not having enough posts, I have to post this as an answer: SilentRevolution's solution is correct, I was having this issue as well and after reading his answer realized it was only in workbooks where I had run a form control that I encountered it.

I only think it's important to post this because earlier answers dismissed this as even being a programming issue, but it is an Office 365 Excel VBA problem not a hardware problem.

It was not a problem in earlier versions of Excel, and will not be able to be duplicated in them.

edited Jan 31, 2016 at 19:35

answered Jan 31, 2016 at 18:58

Bianary

112 bronze badges

Comments

TimeToCode · Accepted Answer · 2017-02-22 23:29:39Z

1

I thought I had a similar problem producing Maths worksheets using VBA Excel 2016.

I used interconnected subroutines each using a home made rndb() function which itself uses the rnd() function. The first time after loading pressing the "new Q's type 1" button would seem to not update the sheet if the last use was "new Q's type 1", but would for other types and for type 1's on subsequent attempts.

It turned out it was generating new, but identical, questions (including a 6 digit random sheet reference number) the first time it was run due to rnd() ... cured by including "randomize" in amongst the plethora of, now known to be redundant, ScreenUpdating = True statements.

edited Feb 22, 2017 at 23:29

TimeToCode

9433 gold badges18 silver badges35 bronze badges

answered Feb 22, 2017 at 19:05

Jim Beckwith

111 bronze badge

Collectives™ on Stack Overflow

Excel not updating after macro

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related