Search code examples
excelvbabuttonspacing

VBA Even spacing of Command button on UserForm

I have several Userforms (of various sizes depending on user input - how many features they selected). My problem is, that I want to align the "Next" command button with even spacing from the bottom right corner (and the "Back" button with even spacing from the bottom left corner). In below image, I want the four red lines to be all the same distance.

I thought it might be as easy as using UserForm1's height and width and subtracting for "Next" and adding for "Back", but that doesn't work.

Private Sub UserForm_Initialize()
    'Adjust Commandbutton
    With CommandNext
            .Caption = " Next " & WorksheetFunction.Unichar(129094)
            .Width = 40
            .Height = 21
            .Left = UserForm1.Width - CommandNext.Width - 32
            .Top = UserForm1.Height - CommandNext.Height - 32
        End With
    With CommandBack
       .Caption = WorksheetFunction.Unichar(129092) & " Back "
       .Width = 40
       .Height = 21
        .Left = 32
        .Top = CommandNext.Top
        End With
End Sub

This is wrong:

wrong distances

How do I alter code to work on any size Userform?

updated I was expecting to get both buttons with same distance from the bottom corners (please note it is not perfect since it was produced decreasing/increasing numbers):

"Back" and "Next" evenly spaced

Private Sub UserForm_Initialize()
    'Adjust Commandbutton
    With CommandNext
            .Caption = " Next " & WorksheetFunction.Unichar(129094)
            .Width = 40
            .Height = 21
            .Left = UserForm1.Width - .Width - 14 'manually altered
            .Top = UserForm1.Height - .Height - 32
        End With
    With CommandBack
       .Caption = WorksheetFunction.Unichar(129092) & " Back "
       .Width = 40
       .Height = 21
        .Left = 5 'manually altered
        .Top = CommandNext.Top
        End With
End Sub
Solution

  • UserForm1.Width and UserForm1.Height define the entire size of the userform including borders and the title bar. You have to use the .InsideWidth and .InsideHeight properties to get the internal dimensions of the userform:

    .Left = UserForm1.InsideWidth - .Width
.Top = UserForm1.InsideHeight - .Height

    InsideHeight, InsideWidth properties example