userform always on top 2

[bronc]

I made a Userform - from Word as it happens. If I click on the Word app it won't come to the top ie the userform is always on top. If I start Excel , i can click on it to bring it to the top but not the Word. I saw a Windows API caled SetWindowPos but also read that the userform is not a child of the Word window but a child of Thunder something or other. I simply want to click on Word and the userform to go to the back.

 

[DPlank]

Look into the ShowModal method - that should let you do what you need.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[bronc]

well thanks Dave. I forgot to mention that I open the form using UserForm1.Show vbModeless. I think that ShowModal is just a design time way of doing the same thing so its not going to help with the order of the windows. I have just found a link to Chip Pearson's site with some stuff about changing the Parent window. I'll take a look at that and see if that helps. (That website is literally fabulous.)

 

[fumei]

Displaying a userform (if the userform itself is Modal) with vbModeless, will not put the userform to the back when you shift focus to Word. It will remain on top. Focus will be on Word application though.

I do not know what version of Excel you are using, but in 2002 (XP) it is exactly the same. Excel takes focus, but the userform is NOT sent to the back. It remains on top.

" think that ShowModal is just a design time way of doing the same thing "

No, making the userform ShowModal = False is not just design time. It affects it at run-time, and is equivalent to vbModeless.

faq219-2884

Gerry
My paintings and sculpture

 

[bronc]

I mentioned that i was running this from Word and consequently Word can't be brought to the front, modal form or otherwise.
(I have not seen a way of writing to the ShowModal property at run-time and when MS says it is read-only i am unusually inclined to agree with them.)
Thanks but I think i'l continue looking at these API's.

 

[fumei]

Huh?

I ran it from Word, and it does exactly what I stated it does. It makes Word have focus(that is, you can perform action on the document), but the userform is still on top.

I do not understand the comment re: writing to ShowModal at run-time. Why would you want to? You can either set the property (in design mode) as True, in which case it is modal. OR, you can set it as False, in which case it is not. OR, you can have it set as True (modal) but display as modaless via Show vbModaless (or Show 0).

Userform1.Show 0 will display a modal userform as modaless. At run-time.

In either case, the userform will STILL be on top. Focus will move to the application (in either Word or Excel), but the userform will still be on top.

I do not know where you are reading that it is read-only. You are correct that you can not write to the property ShowModal itself at run-time, but you most certainly can make a userform modal or modaless at run-time when you actually call it via Show. The modal value is an optional Boolean parameter. From Help:

Syntax

[object.]Show modal

Modeless forms do not appear in the task bar and are not in the window tab order.

My bolding.

Are you saying you can get the userform to no longer be visible?

faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

Huh?

I ran it from Word, and it does exactly what I stated it does. It makes Word have focus(that is, you can perform action on the document), but the userform is still on top.

I do not understand the comment re: writing to ShowModal at run-time. Why would you want to? You can either set the property (in design mode) as True, in which case it is modal. OR, you can set it as False, in which case it is not. OR, you can have it set as True (modal) but display as modaless via Show vbModaless (or Show 0).

Userform1.Show 0 will display a modal userform as modaless. At run-time.

In either case, the userform will STILL be on top. Focus will move to the application (in either Word or Excel), but the userform will still be on top.

I do not know where you are reading that it is read-only. You are correct that you can not write to the property ShowModal itself at run-time, but you most certainly can make a userform modal or modaless at run-time when you actually call it via Show. The modal value is an optional Boolean parameter. From Help:

Syntax

[object.]Show modal

Modeless forms do not appear in the task bar and are not in the window tab order.

My bolding.

Are you saying you can get the userform to no longer be visible? Without using .Hide?

faq219-2884

Gerry
My paintings and sculpture

 

[DPlank]

I got the impression the OP wanted to make the userform invisible without using .Hide, or at least shift it out of the way, hence his enquiry about being able to use Word while the userform is present.

Seems to me the sensible thing to do would be drag the form out of the way while editing Word. As Gerry's noted Word will get focus and you can work on it - it's just a case of dragging the form out of the way to see what you are doing.

I'm sure there's a way to do this too programmatically, but can't think of it off the top of my head, short of resizing the userform before switching to word and then resizing back again after..?

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[fumei]

Actually, I do that sometimes - resize the userform to a wee bar, AND move it out of the way. Then use Userform_Click to resize and place it back into position, when I want it.

I know of no way to make the application on top - that is, the userform is NOT visible - without using .Hide.

faq219-2884

Gerry
My paintings and sculpture

 

[bronc]

OK this is as far as I've got. I'm working from Excel now since its easier than Word. (I'll eventually modify it for Word). I want the userform (which has a Caption "Data") to go behind the Excel w/book (named "winWxpt.xls"). I've managed to get a handle to the userform as shown by the commented line below. It just doesn't work for the userform handle (ie using the uncommented line instead of the other hndl, but it does manage to send Excel behind another app like word when i use the code exactly as is below. Since I understand that a userform is a child of the Excel widow, i guess i will need to work on changing the ownership of the userform. Any API experts out there?


Dim hndl As Long, lReturnValue As Long
'hndl = FindWindow(lpClassName:="ThunderDFrame", lpWindowName:="Data")
hndl = FindWindow(lpClassName:="XLMAIN", lpWindowName:="Microsoft Excel - winExpt.xls")
lReturnValue = SetWindowPos(hndl, HWND_BOTTOM, 0, 0, 0, 0, SWP_NOSIZE Or SWP_NOMOVE)

 

[DPlank]

What's wrong with the suggestion you've received? Resizing the userform and moving it off out of the way while you interact with the host application.

You can do this easily and then bring it back after you're done.

I'm pretty certain that you cannot change the hierarchy of the userform and its parent windows/applications, so this path is not applicable.

Perhaps I'm missing something here. Why exactly do you need the userform to be completely hidden from the user while they work on the application, and why is it not suitable to simply resize it and shift it out of the way?

And if this is absolutely necessary, you could use the .Hide method, and assign an additional macro to rerun the .Show command once the work on the application is completed.

Happy to help, but you'll have to explain better what you are doing and why

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[fumei]

DPlank, good response.

bronc, if you insist on trying to use API to do something, fine, that is quite up to you. For interest sake, and general politeness, it would be nice if you told us why you are trying to do something. Or at the very least, tell us exactly what it is you ARE trying to do.

faq219-2884

Gerry
My paintings and sculpture

 

[bronc]

OK here we go.

My girlfriend makes property leases. She gets sent one word document - a "data sheet" and tediously puts the data into another word document – the "agreement". This takes her about 20 minutes a time.

From word I wrote some code to produce a userform - an intermediate step so that she can first check the data and I can deal with issues such as the date in the data sheet sometimes appearing as 12th June rather than June 12 and automatically calculating the renew option dates etc etc. This userform has stacks of (dynamically produced) text boxes and is therefore very large, taking up most of the screen.

My girlfriend hasn't heard of a userform and doesn't really want to know. To her and a lot of other people it looks like another window and so expects "normal" window behaviour ie going behind other windows when they are clicked upon. I don't wish to resize it, move it, close it, minimize it, restore it, hide it , show it - modally or not - at design time or otherwise - or any other workaround – I just want it to go behind.

My girlfriend was so overcome - by clicking this button on the userform it produced this lease literally at the touch of a button. She was so happy in fact that she implicitly forgave me for the several women that I have run off with over the years. (Well you did ask).

As it happens from the same form, at the touch of another button she can now automate Excel and using the same data on the userform, update her Excel journal and then automatically produce invoices at another button click. (That was also another pleasant surprise for me - that the invoice produced by Excel is easier, very glamorous and more versatile than Word's.)

You're right, I have a responsibility for politeness and respect for people who go out of their way to spend their valuable time sharing their expertise and trying to help other people on this forum. Thanks.

 

[fumei]

That was hilarious. Thanks. Although I am sure it would be better if she explicitly forgave you, rather than "she implicitly forgave "

So...just curious. You did, in fact, make it go behind? If so...how do you get it back????

faq219-2884

Gerry
My paintings and sculpture

 

[bronc]

Well she actually said that i was the best thing since canned beer, so being Australian, I took that to mean absolute exoneration.

No after 3 days i still can't do it. Of course if i get it to behave like a "normal" window i will be able to bring it back to the front by clicking on it. (after moving others away a bit of course).

I just had another, here we go again, brainwave. I was messing about with Spy++ which i just discovered comes with VStudio and noticed something . My userform has a POPUP style. Maybe I could change that but i read somewhere that changing styles using APIs is hit and miss.

I 'm going to solve this if it kills me cause as you have noticed, the reward may be unforeseeable.

 

[DPlank]

You could always import your efforts into VStudio and build it as an application, making use of its links into VBA and Word/Excel.

Code shouldn't change all that much, and you should find it easier to add minimise/maximise buttons etc...

I think.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[fumei]

Of course if i get it to behave like a "normal" window "

BUT...it is not a normal window. It is not a normal window. It is not a normal window.

Again, userforms in VBA are NOT in the window tab order.

You could as DPlank suggests make it a "real" Windows application, but that is whole other thing.

faq219-2884

Gerry
My paintings and sculpture

 

[strongm]

>I simply want to click on Word and the userform to go to the back.

Yep, you can do this

>Of course if i get it to behave like a "normal" window i will be able to bring it back to the front by clicking on it

Yep, you can do this to

Your API stuff earlier on was so close ...

I don't have time just now to put an example together right now, but I should be able to post something this evening.

 

[strongm]

Here's the code you need to post into your Word UserForm:

[blue]Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) 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 Const GWL_HWNDPARENT = (-8)

Private Sub UserForm_Initialize()
    SetWindowLong FindWindow("ThunderDFrame", Me.Caption), GWL_HWNDPARENT, ByVal 0&
End Sub[/blue]

Please note that DPlank is correct when he says the form needs to be launched as vbModeless for this to work properly.

 

[bronc]

i can't believe it. So simple! I click another window and the u/form goes to the back.

Thanks EVERYONE for getting me there in the end.