VBA Automation

[Ray C]

I have an Access application that automates an Excel worksheet. In other
words, the VBA code opens a new instance of Excel and plugs data into
worksheets using queries.

The user wants me to keep Excel visible while it's doing this. In other
words, my code sets the Visible property of the Excel object to True:

objExcel.Visible = True

It takes about 5 minutes for the code to complete the worksheet. However,
during these 5 minutes, if the user clicks on the worksheet with his mouse,
the VBA code crashes (stops executing). How can I keep Excel visible without
allowing the user to click on the worksheet?

 

[Ken Snell [MVP]]

I think you need to choose between the code working correctly (worksheet
invisible) or potentially erroring (worksheet visible). I don't know of a
way to "lock" the keyboard in such a situation. The best way to avoid this
problem is to keep the worksheet invisible.

It's possible that your code is contributing to this problem if the code is
using ActiveCell or ActiveSheet object references, because those objects
"change" when the user clicks on the worksheet. Rewriting your code to use
explicit references instead of these implicit references might help.