CODEWITHSUNDEEP
exceluserformvba
Dentist
Dentist

Reputation: 21

Align Userform to cells in Excel

I'm attempting to position a userform (named UserForm1) in Excel to align with cell references in Excel. If I initialize the form using the first code below, the userform is correct size positioned in the centre of the screen in Excel.

Private Sub UserForm_Initialize()

With Me
  .Width = 200
  .Height = 170
End With

End Sub

However, I want to resize to align the userform so that it covers cells B3:D15. I've tried

Private Sub UserForm_Initialize()

With Me
.Top = Sheet1.Range("B3").Top
.Left = Sheet1.Range("B3").Left
.Width = Sheet1.Range("B3").Offset(0, 4).Left - Sheet1.Range("B3").Left
.Height = Sheet1.Range("B3").Offset(12, 0).Top - Sheet1.Range("B3").Top
End With

End Sub

but the userform appears over the ribbon (Excel zoom is at 100%.)

Is there a way to do this properly taking into account screen resolution/zoom size?

Upvotes: 2

Views: 20239

Answers (1)

romulax14
romulax14

Reputation: 555

First I apologize for bringing back this old thread from the dead, but others answers for this problem on SO either doesn't work perfectly or only on specific situations (fullscreen, etc.).
After looking for answers to this questions on many forums and others SO related questions, I found some simple code on a french forum that works just fine for 32bits version of excel (office 365).

In a module:

Option Explicit
 
Private Declare Function GetDC& Lib _
"user32.dll" (ByVal hwnd&)
Private Declare Function GetDeviceCaps& _
Lib "gdi32" (ByVal hDC&, ByVal nIndex&)
 
Sub UserFormAlign()
    ' 1 inch = 72 points for usually 96 or 120 dpi
    Dim x#, y#, w#, h#
    x = GetDeviceCaps(GetDC(0), 88) / 72
    y = GetDeviceCaps(GetDC(0), 90) / 72
    With UserForm1
        .StartUpPosition = 0
        .Left = (ActiveWindow.PointsToScreenPixelsX(ActiveCell.Left * x) * 1 / x) + ActiveCell.Width
        .Top = (ActiveWindow.PointsToScreenPixelsY(ActiveCell.Top * y) * 1 / y) + ActiveCell.Height
        .Show 'if not already shown
    End With
End Sub

The code above can be easily adapted to be launched from the UserForm by replacing the With UserForm1 with With Me, and also removing the .Show if needed (no point in trying to show itself since there are chances it is already on screen).

Upvotes: 3

Related Questions