Bring my modeless userform to the foreground???

[Robert Crandal]

I am currently using a special type of modeless userform that
remains visible even when the Excel application is minimized.
I load the form with a pushbutton that calls "Userform1.Show vbModeless".
Additionally, this userform is defined as follows:
---------------------------------------------------------------------------------
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLongA Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Private Const GWL_HWNDPARENT As Long = -8
----------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
hWnd = FindWindow("ThunderDFrame", Me.Caption)
SetWindowLongA hWnd, GWL_HWNDPARENT, 0&
End Sub
----------------------------------------------------------------------------------


Since the above userform can sometimes get hidden underneath Excel in the
background, I wanted to be able to bring the form into the foreground if
my pushbutton is pressed again. I use the following code to (attempt)
to achieve this:

Sub Button1_Click()
UserForm1.Visible = True ' Set visible as True will bring to
foreground???
End Sub


The problem is, if I attempt to run the above code in "Button1_Click()", I
get
the following compile error:

"Function or interface marked as restricted, or the function uses an
Automation
type not supported in Visual Basic"

Does anybody know why this error occurred??? What does it mean?? And
what is the best way to bring my userform to the foreground?

 

[Rick Rothstein]

Just showing it again like this...

UserForm1.Show

It is already modeless, so you don't need anything more than the line above
to bring it to the front.

 

[Robert Crandal]

I noticed that if I press my button several times that multiple instances
of the same modelless userform will get loaded. I figured this was
a side effect of the special modeless form that I was using.... It
seems to be a random bug, and I'm not sure exactly why it happens.

So, I figured, if Userform1.Show is gonna show my form multiple
times, I was just wondering if there was an alternative to bringing
the form into the foreground???

 

[joel]

I think the excel application is in the background. the Userform shoul
be brought to the front of the excel application but not to the front o
other applications. So before doing the show I would bring the exce
window to the foreground.

ActiveWindow.Activate
or
Application.ActiveWindow.Activate

Do yu have multiple workbooks opened in the same window and one of th
other workbooks is the active workbook

 

[Robert Crandal]

My userform is special, because it is modeless AND it can also float
behind the Excel application. Even when Excel is minimized, my userform
remains visible on the screen.

I thought maybe I could send it some sort of "Activate" message.
It appears that using Userform1.Show() might be my only option.

 

[Rick Rothstein]

UserForm1.Show, by itself, does not create new instances of your UserForm...
you must be running the code you showed us each time you click the button
too... don't do that... put an If..Then test to see if the UserForm is
visible... if it is, just execute the UserForm1.Show command, otherwise run
the code you posted previously to load/show it.

--
Rick (MVP - Excel)

I noticed that if I press my button several times that multiple instances
of the same modelless userform will get loaded. I figured this was
a side effect of the special modeless form that I was using.... It
seems to be a random bug, and I'm not sure exactly why it happens.

So, I figured, if Userform1.Show is gonna show my form multiple
times, I was just wondering if there was an alternative to bringing
the form into the foreground???

 

[Robert Crandal]

So, would my code roughly look like this:

If Not MySpecialFormLoaded("UserForm1") Then
UserForm1.Show vbModeless
Else
UserForm1.Show ' Just show it if it's already loaded
End If

Is that what you mean??

 

[Rick Rothstein]

No, that is not what I meant, but that is not your fault, it is mine for
forgetting how you implemented your special modeless implementation. I had
tested UserForm1.Show when I made my initial response to you and then forgot
about it when I last responded to you. Let me start over. Since you use the
Initialize event to create your special form, then that code will only be
executed the first time the form is loaded... just showing the form does not
trigger the Initialize event... only loading or showing it for the first
time does that, so I don't see why you are getting multiple instances of
your UserForm. Show me **all** the code in your button's procedure that
loads/shows your UserForm.

--
Rick (MVP - Excel)

So, would my code roughly look like this:

If Not MySpecialFormLoaded("UserForm1") Then
UserForm1.Show vbModeless
Else
UserForm1.Show ' Just show it if it's already loaded
End If

Is that what you mean??

 

[Robert Crandal]

I must admit, I omitted some lines of code in my original posting
for the sake of trying to keep my explanation as brief and easy
to understand as possible. Sorry bout that!

What I didnt explain earlier was that I actually have 2 userforms
in my project, Userform1 and Userform2. Userform1 has a
command button named ButtonA and Userform2 has a command
button named ButtonB.

There is also a pushbutton on my Sheet1 (named Button1) that
when pressed it will load Userform1.

Additionally, if a user presses ButtonA on top of Userform1,
I wanted to unload Userform1 and replace it with Userform2.

I think I have just identified my problem. If Userform1 gets
swapped out with Userform2, then a user presses Button1
on my spreadsheet, it will see that Userform1 is unloaded, so
it will reload it again, hence Userform1 and Userform2 will be
on the screen at the same time. I know that's confusing, but
I think I just solved my own problem, haha.

BTW, thanks for all your great help Rick!

 

[Peter T]

I haven't followed all this thread but if the form is already loaded as
modeless you must include the modeless argument again to re-show it (because
the default is modal).

If the objective is to bring it to the front there are API ways but
following can simply solve some other difficulties.

With UserForm1
..Hide
..Show vbModeless
End With

This assumes you already know the form is loaded and visible

Regards,
Peter T

So, would my code roughly look like this:

If Not MySpecialFormLoaded("UserForm1") Then
UserForm1.Show vbModeless
Else
UserForm1.Show ' Just show it if it's already loaded
End If

Is that what you mean??