CODEWITHSUNDEEP
vbaexcel
kestertonj
kestertonj

Reputation: 11

Excel not updating after macro

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.

Upvotes: 0

Views: 11748

Answers (4)

Jim Beckwith
Jim Beckwith

Reputation: 11

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.

Upvotes: 1

Bianary
Bianary

Reputation: 11

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.

Upvotes: 1

SilentRevolution
SilentRevolution

Reputation: 1513

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.

Upvotes: 3

NinjaLlama
NinjaLlama

Reputation: 165

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

Upvotes: 1

Related Questions