Excel macros work when got focus but not when lost it

[ktwclark]

I've got some strange goings on with my macros. There doesn't seem to be any specific instruction that's causing problems but there is definately a problem. I make Excel invisible when I run my macros as several workbooks are opened and things can get a bit flashy and sore on the eyes.

I've completed my code and am busy testing and have come across a bit of a problem. When I run my Excel macros and let Excel have the focus, they run fine, no probs, does what I want, doesn't do what I don't want, ideal.

However, when I run my macros and something else has the focus, say Outlook, ie Excel is running in the background, then I get errors. This seem to be with the Autofill command, or when inserting a full column, and sometimes when there is a With Selection.xxx commands.

Should macros be run in foreground or in the background, or does it depend on how they are coded?

Does anyone know of any issues?

 

[Dreamboat]

To my knowledge, the common way is not to make Excel invisible, but to use this line at the beginning of your code:

Application.ScreenUpdating = False

and this line at the end of your code:

Application.ScreenUpdating = True

That way, the screen just kinda sits there. I have not tried it, nor would I think it WOULD work, to go running around my programs while Excel code is running. I suspect that while your code is trying to make some type of selection, it could be choosing the selection you're making in Outlook or other programs. Anne Troy
Dreamboat@TheWordExpert.com
Anne@MrExcel.com

 

[ktwclark]

I still need to update the screen as I use userforms within Excel to keep the user informed as to what stage a process within my macros is at.

Since Windows is a multi-tasking operating system then the option of jumping about other applications while a macro in Excel, Word, Access, etc is running is a viable option and should be considered when programming.

I use Outlook as a mail client and have auto-notification on so that a window pops up every time I get a new email, if I'm running a macro and an email arrives, a window pops up and my macro stops responding. This is no good.

 

[xlbo]

But that's that way it works - there are certain operations that can be performed when excel doesn't have the focus - mainly ones that do not "re-create" human intervention.
eg With Sheets("MySheet&quot
.range("A1&quot.value = 12
End With
-this should work because it is internally telling excel to perform the task

Code that I know is likely to make the macro fall over when excel doesn't have the focus include Selections / Filters / Activates / printing - basically, anything that mimics human interaction - if you can get rid of that, you can run the macro without focus, if not, you're gonna have to keep focus on excel.
HTH
Rgds
~Geoff~

 

[sam12345]

I am looking for material on how to creat macros in Ms Excel. Kindly let me know where i can find it on the net... Thanx

 

[xlbo]

Sam 12345
a: Start your own threads - they're much more likely to get noticed
b: Check the excel help files - they're surprisingly good
c: Ask specific questions
d: Go to Tools>Macro>Record New Macro
Perform some actions
Go to the VB Editor
Look at what has been recorded Rgds
~Geoff~