Keeping a userform on top of another when using taskbar button.

[Scotster]

I have a VBA program that uses multiple userforms. I've made it fairly complex, with regards to its appearance, by hiding the excel application on load, creating a max/min button and creating a taskbar icon for the program.

Now... the issue...

When I click on one of the options, thus opening another userform, it shows great. If I click away from the program and then click on the icon on the taskbar it brings up the initial userform with the newly opened userform behind it.

Is there any way to make it so that the newly opened userform stays on top when the taskbar icon is clicked? The only way I can get it back is to alt-tab to another window, and then alt-tab back to the userform. This brings the uppermost one to the top.

All forms are shown modally so that only the one that's uppermost can be used, I just wish it would stay uppermost lol.

[GTO]

Greetings Scott,

Firstly, I see that this is your first post/thread, so let me be the first to say "Welcome to VBAX!"

Although I have not been able to 'stay up' with the site as well as I'd like to for the past year - I can say without reservation - that you have joined a great site. Simply put, there are some mighty nice folks here, who will go out of their way to help you.

Now as to your issue, I suppose I am curious why the 'first' (parent?) form cannot simply be hidden upon selecting an option? I am probably missing simple stuff though, so I would suggest (albeit it some work on your end), creating an example workbook that replicates your project's current handling of the forms (I am assuming sensitive data; if none, of course your wb would be fine). If not too inconvenient, in .xls format.

In short - it would be wayyyyy easier for any 'answerer' to see what we are facing, and increase chances of a solution.

Again, welcome to VBA Express :-)

Mark

[Scotster]

Greetings Scott,

Firstly, I see that this is your first post/thread, so let me be the first to say "Welcome to VBAX!"

Although I have not been able to 'stay up' with the site as well as I'd like to for the past year - I can say without reservation - that you have joined a great site. Simply put, there are some mighty nice folks here, who will go out of their way to help you.

Now as to your issue, I suppose I am curious why the 'first' (parent?) form cannot simply be hidden upon selecting an option? I am probably missing simple stuff though, so I would suggest (albeit it some work on your end), creating an example workbook that replicates your project's current handling of the forms (I am assuming sensitive data; if none, of course your wb would be fine). If not too inconvenient, in .xls format.

In short - it would be wayyyyy easier for any 'answerer' to see what we are facing, and increase chances of a solution.

Again, welcome to VBA Express :-)

Mark

Hi Mark, thanks for the quick response and friendly welcome.

I've made a little example in work, sent it home but the work killed the macro so will attach it tomorrow.

Basically the program opens fullscreen and has a listbox on the left and options on the right. The list contains a quick view of information, one of the other forms that pops up displays the detailed information on a double click. There are forward and backward arrows to go to the next item or previous item, highlighted on the listbox behind the now front form.

With the initial page being the main base of the program I would rather have it stay visible the entire time, with search boxes, detail forms, address forms etc all coming up in front of it. Every other form is closed before every other form comes up, the only one that stays the entire time is the main one.

As it stands it works great and the only person that uses the program is me, but it's always bugged me that if I click on another window (email, internet, explorer etc) and then click the taskbar button to bring it back, it's the main window that's shown rather than the uppermost form. If I don't hide the application and don't bother creating a taskbar button for the form, it works as I want it to. But I would rather hide the application as it feels more professional for me :D

I'm thinking that it may not be possible, or it may require a lot of coding, as the taskbar button is created on the main form. The only way I can think of doing it myself is to either unload the taskbar button when a parent form is opened, and then re-create the taskbar button on that form.... or simply have more than one taskbar button, one designated for each window.

Anyway, thanks for the help and for the very warm welcome :)

[mikerickson]

Normally, I would recommend a multipage control rather than a pile of userforms. But that doesn't sound like it would work for you.

Have you considered putting the subsidiary controls (the second userform, that you want on top) into a Frame control on the first userform. Then you could set the .Visible property of that Frame rather than invoking a second userform.

[Scotster]

Normally, I would recommend a multipage control rather than a pile of userforms. But that doesn't sound like it would work for you.

Have you considered putting the subsidiary controls (the second userform, that you want on top) into a Frame control on the first userform. Then you could set the .Visible property of that Frame rather than invoking a second userform.

That sounds like a possibility but my only issue is I fear that would take a LOT of work.

I've taken all the data and gubbins out of the program I'm using and attached it. This is to show the general layout of what I have and to show how the icon/taskbar button all operates.

To be honest I can live with it, and I have done for a year or 2 now, but it would be nice if I could get it to work the way I want it. It's not necessary, I've basically come to the end of my fiddling and I'm just ironing out little niggles lol.

[GTO]

The vba properties appears to be passeworded. I can read the code, but not see what libraries are referenced, and I received several error messages about object(s) not available. Not sure if I'll be of any help, but for whoever might be able to, could you re-attach the example with password(s) removed?

Mark

[Scotster]

Oops, sorry about that.

The errors are most likely down to the progress bars and the date and time picker, I always have trouble with those controls when switching systems.

I've taken out the password and removed the offending controls, hopefully that should help with the errors.

Cheers

[Aflatoon]

Your issue is that you used AppTaskList on the main form only. If you move the API code to a normal module and make it public, you can then call it from any form that you want and make sure to use AppTaskList Me in the activate event of each form.

[Scotster]

Your issue is that you used AppTaskList on the main form only. If you move the API code to a normal module and make it public, you can then call it from any form that you want and make sure to use AppTaskList Me in the activate event of each form.

Ooo that sounds good. I'll go and have a play.

BTW I'm a complete self taught beginner so I'm not exactly sure how to do it, I'll give it a go though :D

[Scotster]

Ooo that sounds good. I'll go and have a play.

BTW I'm a complete self taught beginner so I'm not exactly sure how to do it, I'll give it a go though :D


Easy as pie, got that going no bother. Now, is there a way that I can unload the apptasklist for the main window? Just so that I can unload the main one as I load the other, and then re-load the main when the other is closed.

Cheers

[Aflatoon]

Something like this - in a new module:

Option Explicit

'API functions
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" _
(ByVal hWnd As Long, _
ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Declare Function SetWindowPos Lib "user32" _
(ByVal hWnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long) As Long
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function GetActiveWindow Lib "user32.dll" _
() As Long
Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Private Declare Function DrawMenuBar Lib "user32" _
(ByVal hWnd As Long) As Long

'Constants
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private Const GWL_EXSTYLE = (-20)
Private Const HWND_TOP = 0
Private Const SWP_NOACTIVATE = &H10
Private Const SWP_HIDEWINDOW = &H80
Private Const SWP_SHOWWINDOW = &H40
Private Const WS_EX_APPWINDOW = &H40000
Private Const GWL_STYLE = (-16)
Private Const WS_MINIMIZEBOX = &H20000
Private Const SWP_FRAMECHANGED = &H20
Private Const WM_SETICON = &H80
Private Const ICON_SMALL = 0&
Private Const ICON_BIG = 1&

Sub AddIcon(frm As Object)
'Add an icon on the titlebar
Dim hWnd As Long
Dim lngRet As Long
Dim hIcon As Long
hIcon = Sheet7.Image2.Picture.Handle
hWnd = FindWindow(vbNullString, frm.Caption)
lngRet = SendMessage(hWnd, WM_SETICON, ICON_SMALL, ByVal hIcon)
lngRet = SendMessage(hWnd, WM_SETICON, ICON_BIG, ByVal hIcon)
lngRet = DrawMenuBar(hWnd)
End Sub

Sub AddMinimiseButton()
'//Add a Minimize button to Userform
Dim hWnd As Long
hWnd = GetActiveWindow
Call SetWindowLong(hWnd, GWL_STYLE, _
GetWindowLong(hWnd, GWL_STYLE) Or _
WS_MINIMIZEBOX)
Call SetWindowPos(hWnd, 0, 0, 0, 0, 0, _
SWP_FRAMECHANGED Or _
SWP_NOMOVE Or _
SWP_NOSIZE)
End Sub

Sub AppTasklist(myForm)
'Add this userform into the Task bar
Dim WStyle As Long
Dim Result As Long
Dim hWnd As Long

hWnd = FindWindow(vbNullString, myForm.Caption)
WStyle = GetWindowLong(hWnd, GWL_EXSTYLE)
WStyle = WStyle Or WS_EX_APPWINDOW
Result = SetWindowPos(hWnd, HWND_TOP, 0, 0, 0, 0, _
SWP_NOMOVE Or _
SWP_NOSIZE Or _
SWP_NOACTIVATE Or _
SWP_HIDEWINDOW)
Result = SetWindowLong(hWnd, GWL_EXSTYLE, WStyle)
Result = SetWindowPos(hWnd, HWND_TOP, 0, 0, 0, 0, _
SWP_NOMOVE Or _
SWP_NOSIZE Or _
SWP_NOACTIVATE Or _
SWP_SHOWWINDOW)
End Sub

Sub AppTaskDelist(myForm)
'remove this userform from the Task bar
Dim WStyle As Long
Dim Result As Long
Dim hWnd As Long

hWnd = FindWindow(vbNullString, myForm.Caption)
WStyle = GetWindowLong(hWnd, GWL_EXSTYLE)
WStyle = WStyle And Not WS_EX_APPWINDOW
Result = SetWindowPos(hWnd, 0, 0, 0, 0, 0, _
SWP_NOMOVE Or _
SWP_NOSIZE Or _
SWP_NOACTIVATE Or _
SWP_HIDEWINDOW)
Result = SetWindowLong(hWnd, GWL_EXSTYLE, WStyle)
Result = SetWindowPos(hWnd, 0, 0, 0, 0, 0, _
SWP_NOMOVE Or _
SWP_NOSIZE Or _
SWP_NOACTIVATE Or _
SWP_SHOWWINDOW)
End Sub


then in the deactivate event of each form (assuming you used Apptasklist me on it!) use
AppTaskDelist Me

[Scotster]

Something like this - in a new module:

Option Explicit

'API functions
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" _
(ByVal hWnd As Long, _
ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Declare Function SetWindowPos Lib "user32" _
(ByVal hWnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long) As Long
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function GetActiveWindow Lib "user32.dll" _
() As Long
Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Private Declare Function DrawMenuBar Lib "user32" _
(ByVal hWnd As Long) As Long

'Constants
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private Const GWL_EXSTYLE = (-20)
Private Const HWND_TOP = 0
Private Const SWP_NOACTIVATE = &H10
Private Const SWP_HIDEWINDOW = &H80
Private Const SWP_SHOWWINDOW = &H40
Private Const WS_EX_APPWINDOW = &H40000
Private Const GWL_STYLE = (-16)
Private Const WS_MINIMIZEBOX = &H20000
Private Const SWP_FRAMECHANGED = &H20
Private Const WM_SETICON = &H80
Private Const ICON_SMALL = 0&
Private Const ICON_BIG = 1&

Sub AddIcon(frm As Object)
'Add an icon on the titlebar
Dim hWnd As Long
Dim lngRet As Long
Dim hIcon As Long
hIcon = Sheet7.Image2.Picture.Handle
hWnd = FindWindow(vbNullString, frm.Caption)
lngRet = SendMessage(hWnd, WM_SETICON, ICON_SMALL, ByVal hIcon)
lngRet = SendMessage(hWnd, WM_SETICON, ICON_BIG, ByVal hIcon)
lngRet = DrawMenuBar(hWnd)
End Sub

Sub AddMinimiseButton()
'//Add a Minimize button to Userform
Dim hWnd As Long
hWnd = GetActiveWindow
Call SetWindowLong(hWnd, GWL_STYLE, _
GetWindowLong(hWnd, GWL_STYLE) Or _
WS_MINIMIZEBOX)
Call SetWindowPos(hWnd, 0, 0, 0, 0, 0, _
SWP_FRAMECHANGED Or _
SWP_NOMOVE Or _
SWP_NOSIZE)
End Sub

Sub AppTasklist(myForm)
'Add this userform into the Task bar
Dim WStyle As Long
Dim Result As Long
Dim hWnd As Long

hWnd = FindWindow(vbNullString, myForm.Caption)
WStyle = GetWindowLong(hWnd, GWL_EXSTYLE)
WStyle = WStyle Or WS_EX_APPWINDOW
Result = SetWindowPos(hWnd, HWND_TOP, 0, 0, 0, 0, _
SWP_NOMOVE Or _
SWP_NOSIZE Or _
SWP_NOACTIVATE Or _
SWP_HIDEWINDOW)
Result = SetWindowLong(hWnd, GWL_EXSTYLE, WStyle)
Result = SetWindowPos(hWnd, HWND_TOP, 0, 0, 0, 0, _
SWP_NOMOVE Or _
SWP_NOSIZE Or _
SWP_NOACTIVATE Or _
SWP_SHOWWINDOW)
End Sub

Sub AppTaskDelist(myForm)
'remove this userform from the Task bar
Dim WStyle As Long
Dim Result As Long
Dim hWnd As Long

hWnd = FindWindow(vbNullString, myForm.Caption)
WStyle = GetWindowLong(hWnd, GWL_EXSTYLE)
WStyle = WStyle And Not WS_EX_APPWINDOW
Result = SetWindowPos(hWnd, 0, 0, 0, 0, 0, _
SWP_NOMOVE Or _
SWP_NOSIZE Or _
SWP_NOACTIVATE Or _
SWP_HIDEWINDOW)
Result = SetWindowLong(hWnd, GWL_EXSTYLE, WStyle)
Result = SetWindowPos(hWnd, 0, 0, 0, 0, 0, _
SWP_NOMOVE Or _
SWP_NOSIZE Or _
SWP_NOACTIVATE Or _
SWP_SHOWWINDOW)
End Sub


then in the deactivate event of each form (assuming you used Apptasklist me on it!) use
AppTaskDelist Me

Spot on, works exactly as I want it to!

Thanks very much for your help :)

[Scotster]

Got around to using this properly today and found a strange problem with it....

If I open up the program then click on "Quick View" it brings up the form as expected. If I click "Back to main" it hides the form as expected. However, if I click on the DTpicker to change the date and then click "Back to main" the form hides behind the Main userform. If I then bring it to the front and click "Back to main" again I get "Run-time error '402': Must close or hide topmost modal form first".

If I take out the "AppTaskList Me" from the Activate sub for the userform it works as it should. It's almost as if clicking on the DTPicker tries to re-activate the userform thus trying to add the form to the taskbar again.


I tried moving the "AppTaskList" call to the Initialize sub and changed the "Me" to "QuickView" but this gave runtime error 400 "Form already displayed; can't show modally"

Feel like I'm so near yet so far. Damn DTPicker!!

[Scotster]

I really hate the way VB flags errors. I think I've now found the issue and it was nothing to do with the DTPicker or anything to do with anything relating to it lol.

Turns out it didn't like me opening a "loading" form, which automatically closed, and then closing the QuickView form. I swapped the loadingform.show and quickview.hide around and it seems to have cured the issue.

I'm not sure why it's an issue but the fact that it's sorted is good enough for now :D