Reputation: 11

Excel Refreshing while Userform is open

Here is a background of my project.

I have an arduino with an LCD screen. This arduino is connected to an excel spreadsheet through a user form. The LCD screen displays values from the excel spreadsheet. If I change the value in excel, it changes the value on the LCD screen.

Taking it a step further, All of our data (for now) is in MS access, so, I imported the data from access to excel. Now, if I change a value in MS access, it changes it in excel, then changes it on the LCD screen.

This works great, but I have one issue. There is a user-form that will be continuously running (this is the connection between the arduino and excel sheet). When this form is open, the excel spreadsheet will not refresh when something in access is changed. Once I close the form, the linked table in excel will refresh.

Any ideas how to have the excel refresh the link with the form open? I have tried refreshall, updatelinks, etc. Thank you!

Upvotes: 1

Answers (3)

Michael

Reputation: 4883

For anyone that finds this question trying to deal with the actual issue of a background query not refreshing while a userform is open, there are 2 solutions:

Open the userform Modeless
Don't perform the refresh as a Background refresh

Excel won't perform a background refresh when a Modal userform is open, even if the refresh is triggered by an action in the userform.

To open the userform Modeless:

frm.Show 0

But if you need the userform to be Modal, then you can perform a foreground refresh. This can be done by a couple of methods.

Explicitly perform a foreground query when calling the refresh from code:

Sheet1.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

Disable background refreshes for the query from Table Design > Properties > Connection Properties (the Workbook Structure must be unlocked to allow the change to be made)