VBA Userform Position

[LisaGreen]

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

[HansV]

Welcome to Eileen's Lounge!

Which application are you using?

[LisaGreen]

I'm in excel... but I want it to be general for vba.

Lisa

[HansV]

Could you post the code behind the button? When I change the Height and Width of a userform, the Left and Top remain unchanged.

[LisaGreen]

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

TIA
Lisa

[HansV]

Thanks. When I click a button that runs your code, the top left corner of the userform doesn't budge:

x1278.png

x1279.png

x1280.png

What happens when you try it?

[LisaGreen]

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

[HansV]

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.

[HansV]

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

[LisaGreen]

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

[HansV]

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.

[LisaGreen]

Hiya...

Attempt two....

Posted code...
Opening Form

Pic 1.jpg

Button pressed once

Pic 2.jpg

Button pressed 2nd time

pic 3.jpg

.... 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

[HansV]

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:

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

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...

[LisaGreen]

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.

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

And thanks again for taking time for this.

Hugs... Lisa

[HansV]

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:

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

Ands in the macro that starts the userform, I set focus back to the VBE after the form has been closed:

Code: Select all

Sub StartForm()
    UserForm1.Show
    Application.VBE.MainWindow.SetFocus
End Sub

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:

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

No idea why, though.

[Jan Karel Pieterse]

Perhaps one uses pixels and the other uses points?

[HansV]

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...

[LisaGreen]

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.

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

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

[HansV]

Dinnertime here - I'll get back to you later.

[HansV]

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

Code: Select all

    Me.Top = 10 - 1.5 * Application.VBE.MainWindow.Top - 45
    Me.Left = 10 - 0.75 * Application.VBE.MainWindow.Left - 6

similar to the On Click code of the command button, I get 10;10 from UserForm_Layout.