Search code examples
excelvbauserform

Why Does It Seem That This Excel VBA UserForm is Placing My Location-Dynamic Buttons in the Wrong Place?

enter image description here

Solution Found (by taller_ExcelHome): Excel differentiates between the total height of the UserForm (including the TitleBar) and the usable area. Any formulas using the .Height to pull the height of the UserForm to scale controls, etc. will be incorrect. This should rather use .InsideHeight to grab the height of the usable area.

As I had mentioned in the title, I have a UserForm that scales based on the user's monitor. As a result, the location of controls on the UserForm must be dynamic. In the screenshot above, you can see the entire UserForm window. It cuts off just below the bottom of the "Submit", "Skip" and "Cancel" buttons.

When I run this, I am checking the actual numeric values for the .Height and .Top of the UserForm and the buttons. The values are as follows:

  • UserForm.Height: 381.6
  • Button.Top: 326.7
  • Button.Height: 26.0

As the .Top value signifies the numeric distance from the top of the UserForm container shouldn't the buttons bottom edge be Button.Top + Button.Height? And if so, wouldn't that make the bottom edge 352.7, meaning there should be 28.9 units (381.6 - 352.7) or that the bottom 7.6% (1 - (352.7 / 381.6)) of the UserForm container should still remain beyond the bottom edge of the buttons?

Edit: Below is the function I'm using to define the size and location of the UserForm. Below it is the code snippet showing how those values are in turn used to size and place the buttons. I'm interested to hear if this has any impact on the outcome, as I would have thought it shouldn't as I can definitively check the true values of the UserForm & Button size / location.

UserForm Dimensioning Function

Public Function fn_UserFormDimension(ByVal myForm As UserForm) As Variant

Dim ufWidth As Double
Dim ufHeight As Double
Dim ufTop As Double
Dim ufLeft As Double
Dim resultArray() As Variant

Dim widthScale As Double
Dim heightScale As Double
Dim heightTopBuffer As Double
Dim heightBottomBuffer As Double
Dim widthLeftBuffer As Double
Dim i As Integer

heightTopBuffer = 0.3
heightBottomBuffer = 0.1
widthScale = 0.4
widthLeftBuffer = 0.03

heightScale = 1 - (heightTopBuffer + heightBottomBuffer)
i = 4

ufWidth = Application.Width * widthScale
ufHeight = Application.Height * heightScale

ufLeft = Application.Width * ((1 - widthScale) - widthLeftBuffer)
ufTop = Application.Height * heightTopBuffer
ReDim resultArray(1 To i)

resultArray(1) = ufWidth
resultArray(2) = ufHeight
resultArray(3) = ufLeft
resultArray(4) = ufTop

fn_UserFormDimension = resultArray

End Function

Code snippet from the UserForm's initialization macro where the button locations & size are dynamically set:

ButtonHeight = Application.WorksheetFunction.RoundDown(Me.Height * 0.07, 1)
ButtonTop = Application.WorksheetFunction.RoundDown(0.865 * Me.Height, 1)
Solution

  • The height of the UserForm caption (title bar) is around 30 units. You can confirm this as follows:

    • Create a new UserForm.
    • Add a command button to the UserForm and set the button's height to be approximately the same as the UserForm's height.
    • Add the following click event code:
    Private Sub CommandButton1_Click()
     Debug.Print Me.CommandButton1.Height & "," & Me.Height 
     Debug.Print Me.Height - Me.CommandButton1.Height
End Sub
    • Show the userform and click the button.

    The resulting difference in heights should be very close to the value you initially observed. This difference remains consistent and does not change with variations in display resolution on my PC.

    Please verify it on your env..