CODEWITHSUNDEEP
vbams-word
Crouzilles
Crouzilles

Reputation: 813

Macro freezes word

I have a very basic macro as shown below:

Option Explicit

Sub ReplaceNumReviewers()
   Application.ScreenUpdating = False
   Dim objExcel As New Excel.Application
   Dim exWb As Excel.Workbook

   Set exWb = objExcel.Workbooks.Open("C:\Users\crouz\Desktop\data.xlsx")



   ThisDocument.numManager.Caption = exWb.Sheets("FeedbackData").Cells(7, 2)
   ThisDocument.numPeers.Caption = exWb.Sheets("FeedbackData").Cells(8, 2)
   ThisDocument.numDirectReports.Caption = exWb.Sheets("FeedbackData").Cells(9, 2)
   ThisDocument.numTotal.Caption = exWb.Sheets("FeedbackData").Cells(10, 2)

   exWb.Close
   Set exWb = Nothing

   Application.ScreenUpdating = True
End Sub

When I run it via a button added to the Quick Access Toolbar, it runs, replaces the values then freezes the word document. I can use other applications without problems except MS Word which I have to kill via the Process Manager.

I am very new to VBA and macros and was wondering what could be causing this?

Upvotes: 2

Views: 4491

Answers (3)

Crouzilles
Crouzilles

Reputation: 813

Ok, I found out how to solve the problem.

I had to replace

exWb.Close

with

exWb.Close SaveChanges:=False

Now it does not hang anymore. Thank you all for the help and putting me in the right direction with the debugger.

I found the answer here: vba script hangs at Workbook.Close

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

Handle errors.

Sub DoSomething()
    On Error GoTo CleanFail
    Application.ScreenUpdating = False

    'your code here

CleanExit:
    Application.ScreenUpdating = True
    'rest of cleanup code here
    Exit Sub

CleanFail:
    'handle errors here
    Resume CleanExit
End Sub

That way you're sure that ScreenUpdating is always set back to True whether or not a runtime error occurs (which is probably the case here).

Upvotes: 1

NotGac
NotGac

Reputation: 47

At the end of your macro, you have 

application.screenupdating=false

Is that a typo? If not, change it to True to unfreeze Word.

Upvotes: 2

Related Questions