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?

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.

--
Rick (MVP - Excel)


"Robert Crandal" <(E-Mail Removed)> wrote in message
news:_vYYm.57218$(E-Mail Removed)...
>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?
>
>

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???


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:egMm$(E-Mail Removed)...
> 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.
>
> --
> Rick (MVP - Excel)
>
>

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.


"joel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> I think the excel application is in the background. the Userform should
> be brought to the front of the excel application but not to the front of
> other applications. So before doing the show I would bring the excel
> window to the foreground.
>
> ActiveWindow.Activate
> or
> Application.ActiveWindow.Activate
>
> Do yu have multiple workbooks opened in the same window and one of the
> other workbooks is the active workbook?
>
>

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)


"Robert Crandal" <(E-Mail Removed)> wrote in message
news:H80Zm.81018$(E-Mail Removed)...
>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???
>
>
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:egMm$(E-Mail Removed)...
>> 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.
>>
>> --
>> 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??


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%23zfB5$(E-Mail Removed)...
> 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)
>

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)


"Robert Crandal" <(E-Mail Removed)> wrote in message
news:f68Zm.164$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23zfB5$(E-Mail Removed)...
>> 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 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!


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>

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

"Robert Crandal" <(E-Mail Removed)> wrote in message
news:f68Zm.164$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23zfB5$(E-Mail Removed)...
>> 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)
>>
>