How Invisible Can You Make Your Code???

[Chuckles123]

This is my mission: users will run my two macros on a daily basis (BDs only) as a W XP Pro Scheduled Task. The macros are digitally signed and the certificate will be 'accepted' by the users. My preference is for there to be zero windows displayed on the users' monitors when the macro is triggered by the Scheduled Task. One macro sends out an e-mail (selects one of five depending upon results of a calculated cell) and saves three worksheet files to a specified location; the other macro prints a specified range on each of three worksheets.

I am using 'Application.Visible = False' at the beginning of each macro and 'Application.Visible = True' immediately preceding each of the 'Application.Quit's. In each of the macro files, the 'IsAddin' Property of 'ThisWorkbook' is set to 'True' (apparently, whether the files are saved with .xlA or .xlS is irrelevant).

The above is accomplishing most of my wishes. When each macro is triggered, the grey screen appears for 2 seconds <-- this is due to 'Handshaking' re: the digital certificate, then it disappears, the macro continues to run for about 30 seconds (as files are opened and closed, there are zero application buttons opening and closing on the W XP Pro Taskbar), then one of two things happens depending if it is Macro 1 or Macro 2: the e-mail draft appears briefly before it is 'Sent'; or three pop-ups appear in rapid succession indicating that each of the three worksheet print jobs has been sent to the printer -- lastly, immediately before each instance of Excel is shut down, a conventional Excel blank worksheet window (with cell divisions) appears for about one second <-- this is due to the 'Application.Quit'. (Upon inserting a 'MsgBox' before the 'Application.Quit', I see that the grey screen window is displayed -- this is the result of the 'Application.Visible = True'. When running at full-speed, this second grey screen is 'invisible'.)

Any ideas on eliminating the 2-second grey screen and the 1-second conventional Excel blank worksheet screen?

Chuckles123

[Jim Rech]

>>I am using ... 'Application.Visible = True' immediately preceding each of
>>the
'Application.Quit's.

Why? Keep it invisible.

--
Jim
"Chuckles123" <[email protected]>
wrote in message
news:[email protected]...
|
| This is my mission: users will run my two macros on a daily basis (BDs
| only) as a W XP Pro Scheduled Task. The macros are digitally signed
| and the certificate will be 'accepted' by the users. My preference is
| for there to be zero windows displayed on the users' monitors when the
| macro is triggered by the Scheduled Task. One macro sends out an
| e-mail (selects one of five depending upon results of a calculated
| cell) and saves three worksheet files to a specified location; the
| other macro prints a specified range on each of three worksheets.
|
| I am using 'Application.Visible = False' at the beginning of each macro
| and 'Application.Visible = True' immediately preceding each of the
| 'Application.Quit's. In each of the macro files, the 'IsAddin'
| Property of 'ThisWorkbook' is set to 'True' (apparently, whether the
| files are saved with .xlA or .xlS is irrelevant).
|
| The above is accomplishing most of my wishes. When each macro is
| triggered, the grey screen appears for 2 seconds <-- this is due to
| 'Handshaking' re: the digital certificate, then it disappears, the
| macro continues to run for about 30 seconds (as files are opened and
| closed, there are zero application buttons opening and closing on the W
| XP Pro Taskbar), then one of two things happens depending if it is Macro
| 1 or Macro 2: the e-mail draft appears briefly before it is 'Sent'; or
| three pop-ups appear in rapid succession indicating that each of the
| three worksheet print jobs has been sent to the printer -- lastly,
| immediately before each instance of Excel is shut down, a conventional
| Excel blank worksheet window (with cell divisions) appears for about
| one second <-- this is due to the 'Application.Quit'. (Upon inserting
| a 'MsgBox' before the 'Application.Quit', I see that the grey screen
| window is displayed -- this is the result of the 'Application.Visible =
| True'. When running at full-speed, this second grey screen is
| 'invisible'.)
|
| Any ideas on eliminating the 2-second grey screen and the 1-second
| conventional Excel blank worksheet screen?
|
| Chuckles123
|
|
| --
| Chuckles123
| ------------------------------------------------------------------------
| Chuckles123's Profile:
http://www.excelforum.com/member.php...o&userid=14948
| View this thread: http://www.excelforum.com/showthread...hreadid=389166
|

[Chuckles123]

Jim,

Your post does eliminate the 1-second conventional Excel blank worksheet screen.
However, I remember reading several posts on this site indicating bad things happen (like not being able to make Excel visible again) if macros did not contain matched pairs of App.Visible=F/T. Any comments?

Also, as I implied in my first sentence, I made your suggested deletion. When stepping through my code (using F8), it works perfectly. However, at full speed, it is doing some strange things like sending out e-mails based on both results of an If statement. I will continue to research.

Chuckles123

Any hopes of eliminating the 2-second 'Handshaking' screen?

[Jim Rech]

>>Any comments?

I have been doing an application.visible = False as part of my Excel apps
for years when users quit my apps. The reason is to seem to shutdown in a
snap rather than side there for 3 or 4 seconds running the closing routine.
Of course Excel takes the same amount of time to truly shutdown visible or
hidden (I assume). No bad side effects in 10 years so I swear by it. There
always could be some action you take in a closing macro that requires Excel
to be visible for some reason, but I haven't hit one.

Blocking the printing message box is a tough one. I don't know of a way to
do that.


--
Jim
"Chuckles123" <[email protected]>
wrote in message
news:[email protected]...
|
| Jim,
|
| Your post does eliminate the 1-second conventional Excel blank
| worksheet screen.
| However, I remember reading several posts on this site indicating bad
| things happen (like not being able to make Excel visible again) if
| macros did not contain matched pairs of App.Visible=F/T. Any
| comments?
|
| Also, as I implied in my first sentence, I made your suggested
| deletion. When stepping through my code (using F8), it works
| perfectly. However, at full speed, it is doing some strange things
| like sending out e-mails based on both results of an If statement. I
| will continue to research.
|
| Chuckles123
|
| Any hopes of eliminating the 2-second 'Handshaking' screen?
|
|
| --
| Chuckles123
| ------------------------------------------------------------------------
| Chuckles123's Profile:
http://www.excelforum.com/member.php...o&userid=14948
| View this thread: http://www.excelforum.com/showthread...hreadid=389166
|

[Chuckles123]

Still researching the prob with the macro ignoring an 'App.Quit'; there may be some type of prob with the code returning from a 'Call' stmt.

You misunderstood one of my queries. I am not trying to eliminate the pop-ups re: reports sent to printer. If possible, I am trying to eliminate the pop-ups re: 'Handshaking' (this is a combination of: using Windows' Scheduled Tasks to trigger the macro; naming the macro 'Auto_Open'; and having the users previously accept the digital signature of the macro). This is the 2-second pop-up at the very beginning.

Chuckles123

[Chuckles123]

Jim,

I discovered that I need an 'End' after each 'Application.Quit'; so, I deleted all of the 'Application.Visible=True' stmts and there are no pop-ups at the end of macro processing.

Still interested in your comments on the 2-second 'Handshaking' pop-up.

Chuckles123