Excel causing user form to be slow opening.

[bigfishyuk]

Im not sure whether this is an Excel or Word issue as we currently have data in Excel which is then loaded into a user form in Word.

This has worked fine for a long time but recently we have developed an issue where the user form takes a few minutes to open if the spreadsheet containing the data is open. If is isnt open it takes seconds to open. It is the same on other userform we have in other documents so im sure its not the code that has been changed.

We can get round the issue by continually opening and closing Excel but it isnt the most convenient process.

Any thoughts would be appreciated.

Thanks

Nigel

 

[Yongle]

Suggested workaround

wb1 = your original workbook
doc = your word document

Create wb2 = new workbook which contains data required for userform in doc
Amend doc to point at wb2 instead of wb1

Everytime wb1 is saved auto-overwrite wb2 with latest data
- use Workbook_BeforeSave procedure
- prevent users from opening wb2 (password protect?)

 

[bigfishyuk]

Thanks Yongle. we set the excel file to auto back up and pointed the userform to the back up file and all works great.

 

[Yongle]

Glad you found a solution