Fun with Userforms!!!!

[Robert Crandal]

I normally create and show a basic userform by using the following
code:

Userform1.Show ' Show the basic userfom


However, I have recently discovered a new way to display
a userform which involves using the code and definitions
below:
--------------------------------------------------------------------------------
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()
Dim hWnd As Long
hWnd = FindWindow("ThunderDFrame", Me.Caption)
SetWindowLongA hWnd, GWL_HWNDPARENT, 0&
End Sub
-------------------------------------------------------------------------------

Then I load this "ThunderDFrame" form using the following call:

UserForm1.Show (vbModeless) ' Show ThunderDFrame as modeless



So..... I was wondering if it's possible to transform the "basic" userform
style described above into the "ThunderDForm" descibed above??? I am
basically interested in toggling my userform back and forth between
the basic style and ThunderDFrame styles when the form is loaded??
I think this is possible with the Win32 API & Visual C++...so I'm wondering
if I can do it with VBA as well???

Thank you everyone!

 

[Bob Phillips]

Sorry, I am not getting what hyou think this does. What is the point as far
as you are concerned?

 

[Peter T]

" I have recently discovered " !

I believe I gave you that code in response to your request "to create a
modeless Userform that stays visible somewhere on the desktop even when the
Excel application is minimized on the Taskbar"

Think you are misunderstanding a few things. "ThunderDFrame" is simply the
window classname of a Userform (in Office 2000+). It's not a 'style', it
never changes. It's used simply (with the window caption) to pass to the API
to find the form's window handle. Having got that there are all sorts of
things you can do with a form.

Regards,
Peter T

 

[Robert Crandal]

Yes, that is exactly what I was trying to explain to everyone.
I should have worded it as follows:

"Peter T has recently enlightened me about a modeless Userform
that stays visible on the desktop even when the Excel app is minimized"!
8)

I was then curious if I could revert the userform back to the original
"style", but apparently that is not possible. My reasoning for doing this
is because I like some of the qualitities of both the original basic
userform style and the "ThunderDFrame" form. Namely, I want
to show a basic userform style when Excel is active, and then I
wanted to use the "ThunderDFrame" class window when Excel is
minimized.

 

[Bob Phillips]

So Peter explains something to you, and you say that that was what you were
trying to say, and then repeat the previous thing you said.

As Peter T said, ThunderDFrame is not a style, but you still call it so.
That is why I asked you waht you thought the code that you gave is doing.

 

[Peter T]

Yes, that is exactly what I was trying to explain to everyone.
I should have worded it as follows:

"Peter T has recently enlightened me about a modeless Userform
that stays visible on the desktop even when the Excel app is minimized"!
8)


I was then curious if I could revert the userform back to the original
"style", but apparently that is not possible. My reasoning for doing this
is because I like some of the qualitities of both the original basic
userform style and the "ThunderDFrame" form. Namely, I want
to show a basic userform style when Excel is active, and then I
wanted to use the "ThunderDFrame" class window when Excel is
minimized.

Terminology is still wrong here, the previous code has nothing to do with
changing "style" or "basic" vs "ThunderDFrame", it only changes the form's
window parent.

OK, If you want to toggle the form's parent between Excel (default) and the
desktop, put a button on a form and try this -

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 mhWndFrm As Long
Private mhWndApp As Long
Private mbDeskTop As Boolean

Private Sub CommandButton1_Click()
Dim h As Long

mbDeskTop = Not mbDeskTop

If mbDeskTop Then
h = 0&
Else
h = mhWndApp
End If

SetWindowLongA mhWndFrm, GWL_HWNDPARENT, h

Me.Caption = IIf(mbDeskTop, "Desktop", "Excel")

End Sub

Private Sub UserForm_Initialize()
Dim hWnd As Long

mhWndFrm = FindWindow("ThunderDFrame", Me.Caption)

If Val(Application.Version) < 10 Then
mhWndApp = FindWindow("XLMAIN", Application.Caption)
Else
mhWndApp = Application.hWnd
End If
Me.Caption = "Excel"

End Sub

again show the form as modeless from a routine in a normal module

UserForm1.Show vbModeless

Anticipating your next question - can the code be automatically triggered to
run as appropriate when Excel is minimized/restored - difficult. Min/Restore
Excel does not trigger any events.

Regards,
Peter T

 

[Robert Crandal]

Terminology is still wrong here, the previous code has nothing to do with
changing "style" or "basic" vs "ThunderDFrame", it only changes the form's
window parent.

Yes, I know. I was in hurry to quickly go to bed, so I just worded
it very carelessly (or indifferently). My apologies to both you and
Bob Phillips! 8)

OK, If you want to toggle the form's parent between Excel (default) and
the desktop, put a button on a form and try this :

[code snipped for brevity]

Thank you, that code is exactly what I was looking for. You, also
anticipated
my next question very well too.

Well, since Excel has no way to detect application minimize or restore,
might
there be another work around??? For example, I really only want to
display the "basic" userform whenever someone is actually editing a
sheet (ie. Worksheet_Change()). If no editing is taking place, then it
seems
like a good idea to change the form to "ThunderDFrame" at this time.
I just figured that detecting app minimize or restore would be the best
times
to toggle the different userforms, but now I need to look for other
options...

 

[Peter T]

Well, since Excel has no way to detect application minimize or restore,
might
there be another work around??? For example, I really only want to
display the "basic" userform whenever someone is actually editing a
sheet (ie. Worksheet_Change()).

Now I'm confused, why on earth would you want the form to be displayed while
Excel is minimized (ie its parent changed to the desktop) if the form is
only required visible while editing a cell, or maybe I misunderstand.

I'm obviously missing something because you cannot interact with the form
while in a cell is in edit mode (or is it only for information purposes).

If no editing is taking place, then it seems
like a good idea to change the form to "ThunderDFrame" at this time.

Please refer to my previous explainations as to why the concept "change the
form to ThunderDFrame" is totally misconstrued.

Regards,
Peter T

 

[Robert Crandal]

Sure, I'll try to explain.....

I was looking for a popup that: 1) Stays visible to the user
as they work on their excel spreadsheet, and 2) I also wanted
the userframe to still remain visible even if Excel is minimized
or if they switch to a different application.

Keep in mind, I want the two above conditions to happen
almost automatically, because I dont want my users to spend
time looking for the popup userform. (BTW, the
"ThunderDFrame" class popup is pushed to the background
as soon as editing occurs on the spreadsheet)

Since Excel does not provide a Userform that has BOTH
of the above behaviors, I was going to try to invent a
work-around that involves using your code that toggles
the Userform "from the form's parent between Excel (default)
and the desktop".

So, if an edit occurs, I figured that I would add code to
Worksheet_Change() to show an "Excel default" Userframe,
because the default Userframe remains in the foreground
when editing happens on the spreadsheet. Once the edit is complete,
I figure that would be a good time to switch to the
"Desktop" userframe???

Is that a little bit more clear?? I do apologize if my wording was
not too clear earlier. Plus, I really am grateful for the code examples
that you have provided so far. You have put me so much closer to
a solution that might work for me!

 

[Peter T]

Sure, I'll try to explain.....

I was looking for a popup that: 1) Stays visible to the user
as they work on their excel spreadsheet, and 2) I also wanted
the userframe to still remain visible even if Excel is minimized
or if they switch to a different application.

Keep in mind, I want the two above conditions to happen
almost automatically, because I dont want my users to spend
time looking for the popup userform. (BTW, the
"ThunderDFrame" class popup is pushed to the background
as soon as editing occurs on the spreadsheet)

Let's clear this up, again. No need to call it a "ThunderDFrame class
popup". It's a Userfrom, it's always a Userform. The classname of a
Userform's window is "ThunderDFrame", it's always "ThunderDFrame", forget
about ThunderDFrame! The only thing that's ever changed in the previous
code examples is if the Userform-window is "attached" to Excel's main window
(default) or to nothing (or rather the desktop) and in effect free floating.

I think that's the 4th time, I'm running out of ideas !

Since Excel does not provide a Userform that has BOTH
of the above behaviors, I was going to try to invent a
work-around that involves using your code that toggles
the Userform "from the form's parent between Excel (default)
and the desktop".

So, if an edit occurs, I figured that I would add code to
Worksheet_Change() to show an "Excel default" Userframe,
because the default Userframe remains in the foreground
when editing happens on the spreadsheet. Once the edit is complete,
I figure that would be a good time to switch to the
"Desktop" userframe???

Is that a little bit more clear??

I think so!

OK, you want the form always displayed even if Excel is minimized. However
if the form is not attached to Excel, when user enters edit mode, or even
makes a new selection, Excel's window is brought to the front thereby hiding
the form (normal behaviour). Ideally it'd be handy if Excel exposed
Min/restore events, but it doesn't (actually there is a complex way but I
won't go into it here). So your idea to solve the dilema is to reset the
form's parent to Excel "before" going into edit mode, then set the form back
to the desktop again when user exits edit mode.

First problem, there is no direct way to trap in advance when user goes into
edit mode. And there are (at least?) three ways of doing that: key F2, just
start typing, or double click. Without going into very complicated
sub-classing of Windows events (risky in VBA) I can only think of trapping
the double-click.

Second problem, can only trap user exiting edit mode if user changes a cell
(pressing Enter without changing is enough). However if user presses Esc
there's no event.

With the above limitations in mind have a go with the following; code in
Thisworkbook module, a normal module, and a userform (no controls required)

Run ShowForm (ideally from Alt-F8 or a button)
Double click a cell to go into edit mode
Change a cell or just press Enter to trigger the change event
Minimizing Excel should leave the form still displayed

The code is quickly put together and no doubt many ways of improving it.
Overall though this is about as close as I think possible to having your
cake and eating it, in a clunky kind of way....


''' Thisworkbook module

Private mbExit As Boolean

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
FormParent True, Sh.Name, True
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Call FormParent(bDesk:=False, sInfo:=Target.Address, bToFront:=True)

Application.SendKeys "{F2}"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
FormParent True, Target.Value
mbExit = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
FormParent True, ""
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If mbExit Then
mbExit = False
Else
FormParent True, Target.Address, True
End If
End Sub

''''''''''''' end Thisworkbook module

'' UserForm1 module

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Or gbFrmLoaded Then
CloseForm
End If
End Sub
''''''''''''' end userform


''''' in a normal module
Option Explicit
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 Declare Function SetForegroundWindow Lib "user32.dll" ( _
ByVal hwnd As Long) As Long

Private Declare Function GetForegroundWindow Lib "user32.dll" () As Long

Private Const GWL_HWNDPARENT As Long = -8

Private mhWndFrm As Long
Private mhWndApp As Long
Private mbDeskTop As Boolean

Public mFrm As UserForm1
Public gbFrmLoaded As Boolean

Sub ShowForm()

Set mFrm = UserForm1
gbFrmLoaded = True
mFrm.Caption = "UniqueCaption"
mhWndFrm = FindWindow("ThunderDFrame", mFrm.Caption)
mFrm.Caption = "Hello"

If Val(Application.Version) < 10 Then
mhWndApp = FindWindow("XLMAIN", Application.Caption)
Else
mhWndApp = Application.hwnd
End If

FormParent True, "New Form"
mFrm.Show vbModeless

End Sub

Sub CloseForm()
gbFrmLoaded = False
On Error Resume Next
Unload mFrm
On Error GoTo 0
Set mFrm = Nothing
mbDeskTop = False
mhWndFrm = 0

End Sub
Public Sub FormParent(bDesk As Boolean, sInfo As String, _
Optional bToFront As Boolean)
Dim h As Long, s As String

If gbFrmLoaded Then
On Error Resume Next
s = mFrm.Caption
If Err.Number Then
ShowForm
mbDeskTop = False
End If
On Error GoTo 0
Else
Exit Sub
End If

If mbDeskTop <> bDesk Then

mbDeskTop = bDesk

If mbDeskTop Then
h = 0&
Else
h = mhWndApp
End If

SetWindowLongA mhWndFrm, GWL_HWNDPARENT, h

End If

mFrm.Caption = IIf(mbDeskTop, "Desktop", "Excel") & " : " & sInfo

If bToFront Then
SetForegroundWindow mhWndFrm
End If

End Sub

'''' end normal module


Regards,
Peter T