XL VBA How do I prevent a UserForm's .Top and .Left properties being ignored on the first .Show?

Question

I have an Excel 2007 VBA project with several userforms, one of which is a date picker containing a Calendar Control 12.0 object. The date picker is activated whenever the user clicks one of two textbox controls on one of the other forms. I want to control the startup position of the date picker such that, when shown, it appears in line with whichever textbox control has been clicked.

To do this, the date picker userform (frmDatePicker) exposes three public vars: xOffset, yOffset and frmParent. The intent is that, before frmDatePicker is shown, its .Top and .Left properties will be set by reference to these three vars, which are themselves initialised by the calling sub. (The userform's .StartUpPosition property is also set to zero (i.e. "Manual") to allow its initial position to be controlled in this way.)

This works perfectly... except for the first time the date picker is shown. The first time the .Show method is invoked, frmDatePicker does not align with the clicked textbox. From the second time onwards this problem disappears.

frmDatePicker's code is as follows:

Option Explicit

Public InitialDate As Variant
Public xOffset As Double
Public yOffset As Double
Public frmParent As Object

Private DateSelected As Boolean

Function Execute() As Boolean

    If Not frmParent Is Nothing Then
        Me.StartUpPosition = 0
        Me.Left = frmParent.Left + xOffset
        Me.Top = frmParent.Top + yOffset
    End If

    If Not IsDate(InitialDate) Then InitialDate = DateSerial(Year(Now), Month(Now), 1)

    calPickDate.Value = InitialDate
    calPickDate.ValueIsNull = True

    btnCancel.SetFocus

    DateSelected = False

    Me.Show

    Execute = DateSelected

End Function



Private Sub btnCancel_Click()

    DateSelected = False
    Me.Hide

End Sub



Private Sub calPickDate_Click()

    DateSelected = True
    Me.Hide

End Sub



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = vbFormControlMenu Then
        Cancel = True
        DateSelected = False
        Me.Hide
    End If

End Sub

And the calling sub is as follows:

Private Sub GetDate(Target As MSForms.TextBox)

    With frmDatePicker

        .Caption = IIf(Target Is txtDstart, "Start date", "End date")

        Set .frmParent = frmAnalysis

        .xOffset = Target.Left
        .yOffset = Target.Top
        .InitialDate = Target.Value

        If .Execute() Then Target.Value = Format(.calPickDate, "dd/mm/yyyy")

    End With

End Sub

What I have tried so far:

• Tested with a fresh userform in a blank workbook to make sure this behaviour is not because of some cruft or corruption in my project (it isn't)

• Setting the .Top and .Left properties in the UserForm_Initialise and UserForm_Activate events of frmDatePicker - individually as well as in both (no difference)

• Preloading frmDatePicker before calling my .Execute function and setting the positional properties as above (no effect)

I really don't want to fall back on "quickly showing then hiding the form" because... well, it's just a horrible solution!

Thanks for reading! Any ideas?

Answer 1

Odd, As you say the first time round setting .Left/Top seems to reset/be ignored:

Not sure why. As a fix this seems to work:

If Not frmParent Is Nothing Then
    Me.StartUpPosition = 0
    Me.Move frmParent.Left + xOffset, frmParent.Top + yOffset
End If

Answer 2

I've posted this answer for completeness only, to provide more info on what is actually happening in this instance. Alex K's fix is better.

I did some more testing and found that it is only the FIRST write to either .Left or .Top which is reset/ignored.

In my example above, it turns out that .Left was being reset/ignored as observed, but the value written to .Top was in fact "sticking". In my project the required vertical displacement from the default centered position was very small so I didn't spot this.

My solution was simply to set the first property twice, i.e.

If Not frmParent Is Nothing Then
    Me.StartUpPosition = 0
    Me.Left = frmParent.Left + xOffset
    Me.Left = frmParent.Left + xOffset
    Me.Top = frmParent.Top + yOffset
End If

It doesn't matter whether the first one is .Left or .Top, and presumably the first line could just set a dummy value since it will be ignored anyway. As long as you then set it a second time the userform displays in the desired position first time round.