Can a non-modal form be viewed w/o Excel in the background?

Is it possible to show a form, task-switch to another application, then
somehow put focus on the form so that it shows but with the other
application in the background, rather than Excel in the background? The
form I am using is generated by a macro assigned to a custom button.

Thanks,
Chris

When launching the form, use the mode vbModeless
may also help to place the form at the margins of the screen
e.g.
myForm.Show vbModeless

within the form
Sub Userform_Activate
me.left = application.width - me.width - 6
me.top = application.top + 6
End Sub

Note that you cannot Alt-Tab to the form once it loses focus. Instead, the
other application needs to be restored to a size less than full screen so
that your form is visible when the other application has focus. Then use the
mouse to set focus back to the form

Kevin Beckham

"cjmd" wrote:

> Is it possible to show a form, task-switch to another application, then
> somehow put focus on the form so that it shows but with the other
> application in the background, rather than Excel in the background? The
> form I am using is generated by a macro assigned to a custom button.
>
> Thanks,
> Chris
>
>

try:

Option Explicit
Dim bVBE As Boolean
Dim bXLS As Boolean

Private Sub UserForm_initialize()
With Application
bVBE = .VBE.MainWindow.Visible
bXLS = .Visible
..VBE.MainWindow.Visible = 0
..Visible = 0
End With
End Sub

Private Sub UserForm_Terminate()
With Application
..Visible = bXLS
..VBE.MainWindow.Visible = bVBE
End With
End Sub

if you are hiding (not unloading) the form
be sure to specify the correct events.

For rescuing a hidden excel incase things go wrong
I've a special file on my desktop

open notepad and type

getobject(,"excel.application").visible=true

then save as xlVisible.vbs



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


cjmd wrote in
<news:<[email protected]>

> Is it possible to show a form, task-switch to another application,
> then somehow put focus on the form so that it shows but with the other
> application in the background, rather than Excel in the background?
> The form I am using is generated by a macro assigned to a custom
> button.
>
> Thanks,
> Chris

Thanks for the reply. I must be doing something wrong as I get
"Run-time error '1004': Programmatic access to Visual Basic Project is
not trusted" when I try to run the macro.

I added the With Application . . . End with to my
UserForm_initialize(), and added the UserForm_Terminate() function. I
tried declaring the Boolean variables in General Declarations and then
in the initialize() function but get the run-time error either way.

Thoughts?
Chris

Chris,

You need to change a setting in Excel:

Tools|Macros|Security|Trusted Publishers and then check Trust Access to
Visual Basic Project

hth,

Doug

"cjmd" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the reply. I must be doing something wrong as I get
> "Run-time error '1004': Programmatic access to Visual Basic Project is
> not trusted" when I try to run the macro.
>
> I added the With Application . . . End with to my
> UserForm_initialize(), and added the UserForm_Terminate() function. I
> tried declaring the Boolean variables in General Declarations and then
> in the initialize() function but get the run-time error either way.
>
> Thoughts?
> Chris
>