Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
Sorry, I am not getting what hyou think this does. What is the point as far
as you are concerned? --- HTH Bob Phillips "Robert Crandal" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
" 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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
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. "Peter T" <peter_t@discussions wrote in message ... " 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
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. --- HTH Bob Phillips "Robert Crandal" wrote in message ... 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. "Peter T" <peter_t@discussions wrote in message ... " 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
"Robert Crandal" wrote in message news:9QxTm.61260$%
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
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... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
"Robert Crandal" wrote in message
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
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" <peter_t@discussions wrote in message ... "Robert Crandal" wrote in message 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fun with Userforms!!!!
"Robert Crandal" wrote in message
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForms | Excel Programming | |||
Userforms | Excel Programming | |||
Esc out of userforms | Excel Programming | |||
Userforms..... | Excel Programming | |||
userforms again... | Excel Programming |