Userform - startup position

Please Login or Register  to view this content.

Hello Lukae,

I have some code that will make a UserForm function like a window. Would that work for you ?

Hello Lukael,

I have had a difficult time staying connected to the forum. Here is the code I promised. Yes, adding this will remember where the UserForm was positioned last while it is open. The default start position is the center of the screen.

Copy the code below and paste it into a new VBA module in your workbook. Change you UserForm to non-modal by changing the ShowModal property to False.

Call the macro from the UserForm's Activate event.

Please Login or Register  to view this content.

Macro Code To Convert UserForm to Window

Please Login or Register  to view this content.

Hello Lukael,

Can you post a copy of the workbook for me to review?

Hello Lukael,


The macro "ConvertToWindow" was not saved in the workbook. I have added the module and tested the workbook. Everything works as it should.

Originally Posted by Lukael

Hello,

I have Userform to display entered data in cells, and needs to be navigated through sheet while entering data. I want Userform to stay at It's last position when I hit "Refresh" button.

Tried all positions in Startup Property but none works for me, so far best option is to position It to center.

my "Refresh" button code :

Please Login or Register  to view this content.

Any ideas ?

Thanks in advance !

This...

(Explanation: 1. Because you don't know where the user will drag the window too, it is impossible to hard code the positions in the Initialize event. 2. Because you are refreshing your form by Unloading then reloading, all form variables are scrapped so Initialize can't rely on the use of module level variables for the last form positon. However if you have project/global level variables in a standard module then these are not cleared by the form unloading and therefore they can be read by Initialize event when you 'refresh' your form. Easy. )

Originally Posted by Lukael

Anyway, mc84excel, thanks for your code, and If this is your masterpiece I must say - You're a genious

Glad to help

If you wish to thank me, you could consider taking a few seconds to rep my post. (Click on the star in the bottom left hand corner of the post).

If your problem is now solved, please use the Thread Tools (top right hand corner of the page) to mark the thread as SOLVED.

Originally Posted by Lukael

mc84excel's code worked ! Not sure why It didn't on my job PC as I have same Excel 2003 version. Maybe some add-ins missing ?

My code doesn't require any add-ins. Did you run the downloaded workbook on the job PC or was it after you copied the code into your original workbook?

Originally Posted by Lukael

I'm still figuring out how It actually works, despite your explanation everything is still not quite clear to me

I'm happy to explain it in more detail if you want.

My code doesn't require any add-ins. Did you run the downloaded workbook on the job PC or was it after you copied the code into your original workbook?

No, in both cases I downloaded file from this forum and tried. On job It didn't work, but on my home PC worked so I pasted code in my original workbook. I will try again on work to see If there is any changes.

I'm happy to explain it in more detail if you want.

Yes, I would very like that, I save all my files and try to comment codes so that I learn from It

If you wish to thank me, you could consider taking a few seconds to rep my post. (Click on the star in the bottom left hand corner of the post).

DONE !

Originally Posted by Lukael

I tried again on both PC's with same file. At home It works, at job not working.

Hi Lukael,

Sorry to hear it is not working on both machines.

Originally Posted by Lukael

What's wrong, Excel version is same, the only difference I see is that I have Win 7 at home, and XP on work. Is that the case maybe ?

The code relies on two global level variables. ( lngUFpositionTop & lngUFpositionLeft). It works by reading/updating the variable values on the form Initialize event.
Variables are not dependent on the version of Windows OS or Office so I am puzzled as to why it is not working for you at your job.

Originally Posted by Lukael

At home It works, at job not working.

When you say not working - do you mean that the form doesn't keep the same position after Refresh? Or will the form not even open?

Originally Posted by Lukael

Yes, I would very like that, I save all my files and try to comment codes so that I learn from It

Quick notes below:
You wanted a user form to keep its position that the end user last dragged it to before hitting Refresh.
Your refresh code works by Unloading the form and then loading the form again. Doing this causes the Initialize event to fire.
When a form initializes - the forms position is set using the Top & Left properties (if these are not set, VBA uses a default position)
However you can also run-time set the forms position in the UserForm_Initialize event (by setting Me.Top and Me.Left).

So far so good.

So all we need to do is find a way to write the Top & Left positions after the user drags the form and then read these values back at UserForm_Initialize

A quick google search revealed that the UserForm_Layout event would cover the user dragging the form. This is the point where you would want to call some code to write the forms current Top & Left positions.

The question then is how to store these values? There are multiple ways we could do this. But the most simplest way would be to use variables.

For the purpose of this exercise, they can not be stored in the form (the values of form level variables are lost when a form unloads) so I added two global level variables to a standard module instead.

When the user drags the form, the UserForm_Layout event fires and updates the two global variables.

When you open/refresh the form ( UserForm_Initialize ), it checks to see if these global variables are set. If they are then set the forms Top & Left to these values. If they are not set (first time form opened for this session), then write the opening values to the variables.

Why must you close and reopen form? It is better to have routine to reset controls.

With your example workbook, code will be like:

Please Login or Register  to view this content.

This is part you need:

Please Login or Register  to view this content.

This code will check type of each control on form and reset to default value dependent upon control type.

Izandol,

I fully tested your code, but I'm afraid It wasn't working quite as It should. Code actually duplicates records, which is not what I wanted. When I pressed refresh button, It adds another whole lines of names into Listbox, which was not desired, because I have scrollbar on listbox.

So I had to first clear listbox, now It works !

Got any clues on how to set comobobox listindex to remain in same value - index after refresh ?

You may store value before refresh and reset afterwards. Or do not clear combobox.