Make Userform on top hide workbook and use resize workbook event to maximaze and minimaze

[jaryszek]

Hi,

i am trying to :

1. Run usefrom and set it on top
2. when minimaze worbkook - keep form on top and minimaze workbook
3. when workbook maximaze - just show userform and workbook --> on this step is not working for me.

code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

please help,

Jacek

[Greg M]

Asking for help with a VBA project which is password-protected is taking the idea of "challenge" to a whole new level!

Greg M

[jaryszek]

Hi Greg,

no worries, sorry for that.

In attachment please find file without it.

Jacek

[Andy Pope]

Your code does nothing if the userform is visible and the workbook is maximised due to the logic in your test

Please Login or Register  to view this content.

You don't handle the window state of xlNormal, perhaps you should.

Also with application on top and maximized it's difficult to debug if you only have 1 monitor.

[jaryszek]

Hi,

how should i correct it?

I deleted elseif statement but still is not working.

I added:

Please Login or Register  to view this content.

but still i have issue.

I think this is causing the issue:

Please Login or Register  to view this content.

but you can see workbook is minimalized propery but show on top is not working.

Jacek



Jacek

[jaryszek]

Anyone please?

Jacek

[jaryszek]

Please help,

up

[jaryszek]

Guys,

please help,
i can not manage this :/

Jacek

[Andy Pope]

What is not working for you?
Explain what is happening and what you expect to happen.

You appear to be setting the application on top, should it be the userform you are setting on top?

[jaryszek]

Thank you Andy!

When userform is started after workbook is minimazed - i want to keep userform on top of everything and use it all time.
When workbook is maximazed - (user clicks to do this in small Excel icon within task bar in windows) userform do not have to be on top on eveyrthing (it should not be) but userform is also visible.

So when user:
1) click to minimaze workbook --> keep userform still on top /hide workbook (do not want to have additional buttons to do this on userform).
2) click to maximaze workbook --> useform is not on top anymore but still visible.

It is clear or should i try in other words?

Thanks!
Jacek

[Andy Pope]

So it is the userform, and not the excel application, that you want to be setting on top.

You need to change the windows handle you are passing to that of the userform.
https://colinlegg.wordpress.com/2016...userforms-vba/

In your windowstate event you should deal with all 3 states. So you probably what to treat Maximize and Normal in the same way.

[jaryszek]

thanks,

In your windowstate event you should deal with all 3 states. So you probably what to treat Maximize and Normal in the same way.

i do not udenrstand what is normalView?

You need to change the windows handle you are passing to that of the userform.
https://colinlegg.wordpress.com/2016...userforms-vba/

on this article there is possiblity to find userform window.

So you mean here:

Please Login or Register  to view this content.

i should pass useform .

Ok i added:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but still what i have to do put here?:

Please Login or Register  to view this content.

how to get userfoem handle?
Maybe i could use hidden sheet , write useform handle there and just use it directy from cell?

What is best solution here?

Jacek

[Andy Pope]

In the article is a function to get userform handle. Use that instead of the Excel application handle.

Excel window, like all windows, has 3 states.

Maximized, fills the screen
Minimized, closed to start bar
Normal, open but resizable

Please Login or Register  to view this content.

[jaryszek]

ok thanks!

But still not working for me

I changed code:

Please Login or Register  to view this content.

and now i now that this is userform.

But this is not triggering:

Please Login or Register  to view this content.

I mean is hidinh workbook together with userform...

Please help,
Jacek

[Andy Pope]

Try this example
https://jkp-ads.com/Articles/keepuserformontop02.asp

[jaryszek]

thanks Andy but this is entirely new code and class.
I hope that anybody here can help with what i have and explain why this is not working.

Why my code is not working?
What is wrong?
It should work without any issues...

Jacek

[jaryszek]

Like Guy said from article:

The problem can be solved rather simple, by using some code that calls a couple of Windows API functions. All code could go inside the userform's code window. Unfortunately, because I chose to make the form topmost, we must handle the fact that another application might become the foreground window. For example: you are running Excel with the userform showing and then you open Word. In the simple case, the userform will stay on top of Word, which is not what we want.

This is want i do not want to achevie! I want to have userform alwasy on top of another applications.
And when workook minimaze - still have userform on top, when workbook maximazed - turn off userform on top.

Just it.

Jacek

[Andy Pope]

If you check that example, and it works how you need it to, then you can see at how the code differs and understand why the current code is not working.

[jaryszek]

Hi Andy,

it doesnt work like i want.

I wrote above why...

edit: And i do not understand this code entirely. There is a lot of code when to set up useform on top i am using just few lines of code...

Jacek

[jaryszek]

ah i found

Please Login or Register  to view this content.

[jaryszek]

Thank you Andy!

Is working like a charm!

Jacek