Automated positioning of UserForm before UserForm.Show

I use an extension monitor, and my userForms sometime appear in odd places on the screens.
The following code places user form 'thisFrm' over the Application window:

    With thisFrm
        .StartUpPosition = 0
        .left = Application.left + (0.5 * Application.Width) - (0.5 * .Width)
        .top = Application.top + (0.5 * Application.height) - (0.5 * .height)
        .Show
    End With

I tried to automate the positions of several forms, and ran into problems below...

The line:     Debug.print "thisFrm.name = "; thisFrm.name         gave an Error. I find thisFrm IS Nothing
When the lines (below) followed by '**' were commented out, no errors occurred, and  MsgBox "WEEE" displayed (so the sub was run).

sub call1()
   Call userForm_Position("thisFrm")
end sub

Sub userForm_Position(formName As String)
    Dim frm As Object
    Dim thisFrm As Object
   
    For Each frm In VBA.UserForms
        If frm.name = formName Then  **
            Set thisFrm = frm  **
            exit sub  **
        End If  **
    Next
    Debug.print "thisFrm.name = "; thisFrm.name  **
    MsgBox "WEEE"
 
    With thisFrm  **
        .StartUpPosition = 0  **
        .left = Application.left + (0.5 * Application.Width) - (0.5 * .Width)  **
        .top = Application.top + (0.5 * Application.height) - (0.5 * .height)  **
        .Show  **
    End With  **

Is the enumeration   'Each frm In VBA.UserForms'   appropriate for forms that are not yet displayed ?
Ought the enumeration come from form objects specified in the VBE?

Floundering here!
Thanks
Kelvin
Kelvin4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

it get to Wee because at

For Each frm In VBA.UserForms

it  probably doesn't find anything and continues after the next

Regards
0
Rgonzo1971Commented:
Hi,

pls try

Sub userForm_Position(formName As String)
     Dim frm As Object
     Dim thisFrm As Object
     
     For Each VBComp In Application.VBE.ActiveVBProject.VBComponents
         If VBComp.Name = formName Then
             Set thisFrm = VBA.UserForms.Add(VBComp.Name)
         End If
     Next
     Debug.Print "thisFrm.name = "; thisFrm.Name
     MsgBox "WEEE"
  
     With thisFrm
         .StartUpPosition = 0
         .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
         .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
         .Show
     End With
End Sub

Open in new window

Regards
0
Rory ArchibaldCommented:
The Userforms collection only contains loaded forms so you need to check if it was found and load it if not. Here's one way without needing trusted access to the VBProject:

Sub userForm_Position(formName As String)
    Dim frm As Object
    Dim thisFrm As Object
    
    For Each frm In VBA.UserForms
        If LCase$(frm.Name) = LCase$(formName) Then
            Set thisFrm = frm
            Exit For
        End If
    Next
    If thisFrm Is Nothing Then
        On Error Resume Next
        Set thisFrm = UserForms.Add(formName)
        On Error GoTo 0
        If thisFrm Is Nothing Then
            MsgBox "No userform called " & formName & " exists"
            Exit Sub
        End If
    End If
    Debug.Print "thisFrm.name = "; thisFrm.Name
    MsgBox "WEEE"
 
    With thisFrm
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin4Author Commented:
Rgonzo & Rory
Your comments address my problem.
I'll work on your solutions right now.

Many thanks!
Kelvin
0
Kelvin4Author Commented:
Many thanks for immediate responses which made several points. I have both solutions up and running.
Best regards
Kelvin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.