• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic userform position based on window position

Manish Sharma

Manish Sharma

Member
Dear friends,

In an Excel tool, I have used two userforms. When I minimize the excel window, the userform also gets minimized that is absolutely fine with me. But the problem arises when I click on the Restore down/maximize application button (which is in between the close and minimize buttons). After clicking on this button the excel window gets resized (i.e its size becomes small) but the userforms still remains outside the excel window frame. It doesn’t retain itself inside the window.

So friends, is there any way by which we can force the userform to always remain inside the excel window.
Also this problem is cross posted at http://www.excelforum.com/showthread.php?t=1077932&p=4047331#post4047331


Regards,
 

Attachments

  • Userform position dynamic based on widow position.xlsm
    20.1 KB · Views: 11
I would use this in the appropriate Userform activate events

Code: 
With UserForm1
  .Top = Application.Height / 2 - (UserForm1.Height / 2)
  .Left = Application.Width / 2 - (UserForm1.Width / 2)
  .Show
End With
 
Hui said:
I would use this in the appropriate Userform activate events

Code: 
With UserForm1
  .Top = Application.Height / 2 - (UserForm1.Height / 2)
  .Left = Application.Width / 2 - (UserForm1.Width / 2)
  .Show
End With
Click to expand...

Thanks a lot Hui, for your reply. I tried to use your code in UserForm_Initialize event; but still the userform remains outside the excel window. Here i giving link to the screenshot image, maybe this will help you my friend to understand my problem.

Link to the screenshot image
http://postimg.org/image/cnf1vzaxb/full/
 
Have you got Display Fusion or something else installed that controls Dual Screen systems ?

Your also using 2013 which I don't have so I can't test it on that
 
As I mentioned it works fine for me in 2010
I don't have 2013 installed at present to test it on.

In 2013 they changed the Display mode of Excel to the MDI Model

So you may have to access the parent's window position with some thing like

Code: 
With UserForm1
  .Top = Application.[COLOR=#ff0000]Parent.[/COLOR]Height / 2 - (UserForm1.Height / 2)
  .Left = Application.[COLOR=#ff0000]Parent.[/COLOR]Width / 2 - (UserForm1.Width / 2)
  .Show
End With
[code]

Or some other combination

I cannot test this as I don't have 2013 installed
 
Hui said:
View attachment 17965
Click to expand...
Oh that icon is of teamviewer application. This application is used for remotely accessing the system.

Thanks Hui, very much for your help. I tried your suggestions in Excel 2010 also but still i am facing the same problem.

Hui, is it something like i have to use Workbook_WindowResize event in this workbook module?
 
Last edited:
Hi Manish ,

Why are you using the StartUpPosition of 0 ? Should this not be 1 , which is CenterOwner ? Why use manual settings for the Top and Left properties ?

Narayan
 
NARAYANK991 said:
Hi Manish ,

Why are you using the StartUpPosition of 0 ? Should this not be 1 , which is CenterOwner ? Why use manual settings for the Top and Left properties ?

Narayan
Click to expand...

Hi Narayan, i have kept it as zero because i want my userforms to be loaded at that specific place (when loaded in full screen mode). If i will use 1 instead 0 then my userforms will be loaded in the middle position which i don't want in my tool as in the central position there will table containing some data.

And once again i have used the manual settings for top and left because i want them to load at top left hand side of the screen in full screen mode.

If this can be achieved by any other mean, then i would be happy to learn it. If you don't mind then can you please show me in the sample file attached with this post.
 
Hi Manish ,

Can you try this and comment ?
Code: 
Private Sub UserForm_Initialize()
            With UserForm1
                .StartUpPosition = 0
                .Top = Application.Top + 175
                .Left = Application.Left + 40
            End With
End Sub
Narayan
 
NARAYANK991 said:
Hi Manish ,

Can you try this and comment ?
Code: 
Private Sub UserForm_Initialize()
            With UserForm1
                .StartUpPosition = 0
                .Top = Application.Top + 175
                .Left = Application.Left + 40
            End With
End Sub
Narayan
Click to expand...

Still the userform remains outside the excel window. I tried in both 2010 and 2013 version.

Is it something like i have to use Workbook_WindowResize event in this workbook module?
 
NARAYANK991 said:
Hi Manish ,

See this file.

Narayan
Click to expand...
Thanks a lot Narayan,

In your file, I have made a small change. I have added the below code in ThisWorkBook module and its working fine. Thanks to you and Hui for all the guidance :)

Code: 
Private Sub Workbook_WindowResize(ByVal Wn As Window)
  With UserForm1
  .StartUpPosition = 0
  .Top = Application.Top + 175
  .Left = Application.Left + 40
  End With
   
  With UserForm2
  .StartUpPosition = 0
  .Top = Application.Top + 175
  .Left = Application.Left + 40
  End With
End Sub
 
You must log in or register to reply here.
Back
Top