VBA Macro fails when Excel Not in Foreground


Joined
May 18, 2010
Messages
1
Reaction score
0
Hi There,

I've been racking my brains on this for a few days now. I hope one of you gurus can help.

I have a very simple VBA Excel Macro that does some automatic cell validation and copying based on a Worksheet_Change Event. Everything works great as long as Excel is running in the foreground and the computer doesn't go into screen saver mode, sleep, another app, or whatever. As soon as Excel is NOT in the foreground, it fails on:

Worksheets("Sheet1").Select.

My code looks like this:

-------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$T$4" Then

DO SOME THINGS

--> Worksheets("Order Entry").Select
Cells(2, 2).Select
ActiveCell.Value = Range("B10").Value

End If
End Sub
--------------

I've tried all kinds of Activate methods, and I have concluded that when Excel is not in the foreground, the Macro can't get a reference to the appropriate worksheet, therefore failing.

It even switchs activesheets properly to Order Entry... as long as Excel is the foreground app.

This macro is installed on the Order Entry worksheet.

Many thanks!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top