VBA Userform Position
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
VBA Userform Position
Hi,
I have a userform opening in the VBE.
There is a button to alter the size of the form to Min/Max/Run where Run is a area of the form.
I'd like the top and left of the form to stay in the same place when the button is pressed but can't make that happen.
Any suggestions or advice appreciated.
TIA
Lisa
I have a userform opening in the VBE.
There is a button to alter the size of the form to Min/Max/Run where Run is a area of the form.
I'd like the top and left of the form to stay in the same place when the button is pressed but can't make that happen.
Any suggestions or advice appreciated.
TIA
Lisa
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
Welcome to Eileen's Lounge!
Which application are you using?
Which application are you using?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: VBA Userform Position
I'm in excel... but I want it to be general for vba.
Lisa
Lisa
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
Could you post the code behind the button? When I change the Height and Width of a userform, the Left and Top remain unchanged.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: VBA Userform Position
Code: Select all
Private Sub cmdMinMax_Click()
' Min > Max > Run > Min.
Dim slUCCaption As String
Dim ilTop As Integer
Dim ilLeft As Integer
slUCCaption = UCase$(Me.cmdMinMax.Caption)
ilTop = Me.Top
ilLeft = Me.Left
If InStr(slUCCaption, "MIN") > 0 Then
' Set Min.
Me.Height = 49.5
Me.Width = 99
Me.cmdMinMax.Caption = "@Run Only"
ElseIf InStr(slUCCaption, "MAX") Then
' Set Max.
Me.Height = 443.2
Me.Width = 875
Me.cmdMinMax.Caption = "@Min Me"
ElseIf InStr(slUCCaption, "RUN") Then
' Set Run.
Me.Height = 443.2
Me.Width = 210
Me.cmdMinMax.Caption = "@Max Me"
End If
Me.StartUpPosition = 0
' *********************************************************************
End Sub
Lisa
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
Thanks. When I click a button that runs your code, the top left corner of the userform doesn't budge:
What happens when you try it?
What happens when you try it?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: VBA Userform Position
Hi Hans....
Thanks for taking some time with this.
Try moving the window and then cllicking the button.
On my set up the window snaps back to 0/0.
What happens for you please?
Lisa
Thanks for taking some time with this.
Try moving the window and then cllicking the button.
On my set up the window snaps back to 0/0.
What happens for you please?
Lisa
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
When I move the window around, then click the button, the top left corner stays where I had placed it.
I'm using Excel 2010 SP1 on Windows 7 Home Premium SP1.
I'm using Excel 2010 SP1 on Windows 7 Home Premium SP1.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
What happens if you change the code to
Code: Select all
Private Sub cmdMinMax_Click()
' Min > Max > Run > Min.
Dim slUCCaption As String
Dim ilTop As Single
Dim ilLeft As Single
slUCCaption = UCase$(Me.cmdMinMax.Caption)
ilTop = Me.Top
ilLeft = Me.Left
If InStr(slUCCaption, "MIN") > 0 Then
' Set Min.
Me.Height = 49.5
Me.Width = 99
Me.cmdMinMax.Caption = "@Run Only"
ElseIf InStr(slUCCaption, "MAX") Then
' Set Max.
Me.Height = 443.2
Me.Width = 875
Me.cmdMinMax.Caption = "@Min Me"
ElseIf InStr(slUCCaption, "RUN") Then
' Set Run.
Me.Height = 443.2
Me.Width = 210
Me.cmdMinMax.Caption = "@Max Me"
End If
Me.StartUpPosition = 0
Me.Top = ilTop
Me.Left = ilLeft
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: VBA Userform Position
Hmmmmm.... I had actually altered the code in the post window so it wasn't the code I was running... Stupid woman!!
I'm now running the code I sent. .. Apologies.
I'm going to try and send some pics... I hope it works.
Opened Form...
er... How do I insert an image please?? I've looked on the forum but can't see any instructions.
Lisa
I'm now running the code I sent. .. Apologies.
I'm going to try and send some pics... I hope it works.
Opened Form...
er... How do I insert an image please?? I've looked on the forum but can't see any instructions.
Lisa
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
You have to click "Post Reply", not "Quick Reply" to be able to post a picture or other attachment. If you have already clicked Quick Reply, click Full Editor to switch. See here for instructions. Any .gif, .png, or .jpg file that you attach will automatically be displayed as a picture in your reply.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: VBA Userform Position
Hiya...
Attempt two....
Posted code...
Opening Form Button pressed once Button pressed 2nd time .... You can see the form moving down a bit.
I have already tried the code you sent ... setting the top and left to the original top and left but I get exactly the same result.
In fact I've just tried it again.
You probably realised that because of the assignment of Top and Left though.
Lisa
Attempt two....
Posted code...
Opening Form Button pressed once Button pressed 2nd time .... You can see the form moving down a bit.
I have already tried the code you sent ... setting the top and left to the original top and left but I get exactly the same result.
In fact I've just tried it again.
You probably realised that because of the assignment of Top and Left though.
Lisa
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
The difference is that I was testing with a userform displayed in Excel. You mentioned that you show the userform in the VBE, but I mistakenly assumed that that was irrelevant.
When I added code to display the userform in the VBE, I saw the behavior that you describe.
I don't have an explanation, but the following kludge works for me:
Disadvantage of such a "brute force" correction is that the value 16.5 may not work on all systems.
I'd be curious to know if someone else can shed some light on this, or suggest a more elegant solution...
When I added code to display the userform in the VBE, I saw the behavior that you describe.
I don't have an explanation, but the following kludge works for me:
Code: Select all
Private Sub cmdMinMax_Click()
' Min > Max > Run > Min.
Dim slUCCaption As String
Dim ilTop As Single
Dim ilLeft As Single
slUCCaption = UCase$(Me.cmdMinMax.Caption)
ilTop = Me.Top
ilLeft = Me.Left
If InStr(slUCCaption, "MIN") > 0 Then
' Set Min.
Me.Height = 49.5
Me.Width = 99
Me.cmdMinMax.Caption = "@Run Only"
ElseIf InStr(slUCCaption, "MAX") Then
' Set Max.
Me.Height = 443.2
Me.Width = 875
Me.cmdMinMax.Caption = "@Min Me"
ElseIf InStr(slUCCaption, "RUN") Then
' Set Run.
Me.Height = 443.2
Me.Width = 210
Me.cmdMinMax.Caption = "@Max Me"
End If
' ***** Adjust top of form *****
Me.Top = ilTop - 16.5
End Sub
I'd be curious to know if someone else can shed some light on this, or suggest a more elegant solution...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: VBA Userform Position
Hi Hans,
After your post I created another form and didn't put it in the VBE and yes... it didn't move at all.
Curiouser and curiouser.
I'd be interested to know how you got 16.5 so I could get a left figure as well. The strange thing is that the behaviour seems intermittent in that yesterday, Left didn't change but today Left is increasing as well!!
I'd also be interested in the code you use to keep the form in the VBE. Here is mine... well... Chip Pearsons :-) with the appropriate declerations of course.
At the moment when exiting the form I drop back to Excel which is not what I want.
And thanks again for taking time for this.
Hugs... Lisa
After your post I created another form and didn't put it in the VBE and yes... it didn't move at all.
Curiouser and curiouser.
I'd be interested to know how you got 16.5 so I could get a left figure as well. The strange thing is that the behaviour seems intermittent in that yesterday, Left didn't change but today Left is increasing as well!!
I'd also be interested in the code you use to keep the form in the VBE. Here is mine... well... Chip Pearsons :-) with the appropriate declerations of course.
At the moment when exiting the form I drop back to Excel which is not what I want.
Code: Select all
Sub subMakeParent()
Dim Res As Long
Dim UserFormHWnd As Long
Dim VBEHwnd As Long
' Chip.
''''''''''''''''''''''''''''''
' Get the HWnd of the UserForm
''''''''''''''''''''''''''''''
UserFormHWnd = FindWindow(C_VBA_USERFORM_WINDOWCLASS, Me.Caption)
If UserFormHWnd > 0 Then
' ''''''''''''''''''''''''
' ' Get the ROOTOWNER HWnd
' ''''''''''''''''''''''''
' ' VBEHWnd = GetAncestor(UserFormHWnd, GA_ROOTOWNER)
' Next line is mine.
VBEHwnd = Application.VBE.MainWindow.hWnd
If VBEHwnd > 0 Then
'''''''''''''''''''''''''''''''''
' Call SetParent to make the form
' a child of the application.
'''''''''''''''''''''''''''''''''
Res = SetParent(UserFormHWnd, VBEHwnd)
If Res = 0 Then
''''''''''''''''''''
' An error occurred.
''''''''''''''''''''
MsgBox "@The call to SetParent failed."
End If
End If
End If
SetWindowPos _
UserFormHWnd, _
HWND_TOPMOST, _
0, 0, 0, 0, _
SWP_NOMOVE + SWP_NOSIZE
' *********************************************************************
End Sub
Hugs... Lisa
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
I used Debug.Print to output the userform's Top and Left properties to the Immediate window before and after moving it.
Left didn't change, Top increased by 16.5.
I used the following code, I think from Rob Bovey:
Ands in the macro that starts the userform, I set focus back to the VBE after the form has been closed:
I now see that the change in position has to do with the position of the VBE window. The further away the top left corner of the VBE window is from the top left corner of the screen, the further the userform moves. But there appears to be some kind of multiplication factor involved - it's as if the VBE and the userform use different units. Experimentally, I found that this works on my PC:
No idea why, though.
Left didn't change, Top increased by 16.5.
I used the following code, I think from Rob Bovey:
Code: Select all
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SetParent Lib "user32" _
(ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) As Long
Private Sub UserForm_Initialize()
Dim lHwForm As Long
Dim lHwVbe As Long
lHwVbe = Application.VBE.MainWindow.Hwnd
lHwForm = FindWindow("ThunderDFrame", vbNullString)
SetParent lHwForm, lHwVbe
Application.VBE.MainWindow.SetFocus
End Sub
Code: Select all
Sub StartForm()
UserForm1.Show
Application.VBE.MainWindow.SetFocus
End Sub
Code: Select all
...
' ***** Adjust position of form *****
Me.Top = ilTop - 1.5 * Application.VBE.MainWindow.Top - 45
Me.Left = ilLeft - 0.75 * Application.VBE.MainWindow.Left - 6
End Sub
Best wishes,
Hans
Hans
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: VBA Userform Position
Perhaps one uses pixels and the other uses points?
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
Might be, but it's strange that it worked for me with a multiplication factor 1.5 for the top and 0.75 for the left coordinate...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: VBA Userform Position
Hi,
I'm using an INI file to try and save and set the form position.
I'm attempting to pick up where the form is moved to in the Layout event.
If I try and pick up Top and Left in the terminate event I get 0 0.
What I find strange is that in testing, I'm setting Top and Left to 10 in the INI file.
When I open the form this is what is printed in the immediate window...
22;6
100;28
Why two lines? Why not 10;10?
The same happens with the same numbers if I hard code Top=10 and Left=10.
I'm now totally out of my depth.
Lisa
I'm using an INI file to try and save and set the form position.
I'm attempting to pick up where the form is moved to in the Layout event.
If I try and pick up Top and Left in the terminate event I get 0 0.
Code: Select all
Private Sub UserForm_Layout()
Dim ilTop As Integer
Dim ilLeft As Integer
Dim slSaveState As String
Dim slPosition As String
ilTop = Me.Top
ilLeft = Me.Left
slPosition = CStr(ilTop) & ";" & CStr(ilLeft)
Debug.Print slPosition
' *********************************************************************
End Sub
When I open the form this is what is printed in the immediate window...
22;6
100;28
Why two lines? Why not 10;10?
The same happens with the same numbers if I hard code Top=10 and Left=10.
I'm now totally out of my depth.
Lisa
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Userform Position
Two lines because apparently the form is drawn twice.
It's basically the same problem as with the On Click code of the command button: the top and left values are offset by values depending on the top left corner of the VBE window. If I use
similar to the On Click code of the command button, I get 10;10 from UserForm_Layout.
It's basically the same problem as with the On Click code of the command button: the top and left values are offset by values depending on the top left corner of the VBE window. If I use
Code: Select all
Me.Top = 10 - 1.5 * Application.VBE.MainWindow.Top - 45
Me.Left = 10 - 0.75 * Application.VBE.MainWindow.Left - 6
Best wishes,
Hans
Hans