How can I make Userform's name as a variable to manipulate it?

[CubaRJ]

Hi, I am trying to improve the form open events to be placed in the middle of the active screen, but I noticed I have to repeated it thru all Initialize event of each form.

This is the code I am using to call a function to calculate the window sizing:

Private Sub UserForm_Initialize()
Dim sngLeft As Single, sngTop As Single
    Call ReturnPosition_CenterScreen(Me.Height, Me.Width, sngLeft, sngTop): Me.Left = sngLeft: Me.Top = sngTop
End Sub

Notice the "Me" is the part I am willing to make a global variable carrying the name of the userform so I won´t have this piece pasted all over the Initialize event and have just a generic call from them.

I found thise code tha makes the Userform a variable, but it does not work as strFormName does not pass to the function to calculate the screen...

Public Sub OpenForm(strFormName As String)
'Code to open subforms using a variable so we can use the positioning code only once instead of writing in every form open event.
    
    VBA.UserForms.Add(strFormName).Show

End Sub

Sub FormForecast()
    
    Call OpenForm("frmForecastCrewChanges")
 
End Sub

Can anyone helpe me?

 

[Micron]

Each form has its own Initialized event. Me is just a short form of referring to the form object. I see no point in trying to make a variable for Me (not that I think you can anyway) and using other procedures to deal with it. Why not just use the initialize event to pass the form properties to your positioning procedure? Or put the positioning function call in the OpenForm event? Maybe I'm not following completely.

 

[CubaRJ]

Each form has its own Initialized event. Me is just a short form of referring to the form object. I see no point in trying to make a variable for Me (not that I think you can anyway) and using other procedures to deal with it. Why not just use the initialize event to pass the form properties to your positioning procedure? Or put the positioning function call in the OpenForm event? Maybe I'm not following completely.

Hi, the only reason is because I would like not to repeat the same piece of code in all forms I have.

I have found this piece of code that makes the form name a variable, but it does not work for this specific purpose.

Sub frmAdminSettingsShow()
    Call OpenForm("frmAdminSetting")
End Sub
 
Public Sub OpenForm(strFormName As String)
    VBA.UserForms.Add(strFormName).Show
End Sub

But if this somehting not doable, I will drop it...

 

[Micron]

I've done similar in Access. I can't test because I don't have your component parts and don't know what you're doing to open forms, but maybe

Sub OpenForm(strFormName As String)
Dim frm As Object
Dim sngLeft As Single, sngTop As Single


Set frm = CallByName(UserForms, "Add", VbMethod, Name)
frm.Show
sngTop = frm.Left
sngLeft = frm.Top
ReturnPosition_CenterScreen frm.Height, frm.Width, sngLeft, sngTop

End Sub

or just put your positioning code in the same sub? Not sure why the sng variables - could just use frm.Left and frm.Top?

 

[CubaRJ]

Hi!

Thanks for the help! This is the complete code:

Option Explicit


#If VBA7 Then
    Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal Index As Long) As Long
    Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
    Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDC As Long) As Long
    Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal Index As Long) As Long
    Declare PtrSafe Function GetWindowRect Lib "user32" (ByVal hWnd As Long, ByRef lpRect As udtRECT) As Long
#Else
    Declare Function GetSystemMetrics Lib "user32" (ByVal Index As Long) As Long
    Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
    Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDC As Long) As Long
    Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal Index As Long) As Long
    Declare Function GetWindowRect Lib "user32" (ByVal hWnd As Long, ByRef lpRect As udtRECT) As Long
#End If

Type udtRECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Public Sub ReturnPosition_CenterScreen(ByVal sngHeight As Single, _
                                       ByVal sngWidth As Single, _
                                       ByRef sngLeft As Single, _
                                       ByRef sngTop As Single)
Dim sngAppWidth As Single
Dim sngAppHeight As Single
Dim hWnd As Long
Dim lreturn As Long
Dim lpRect As udtRECT

    hWnd = Application.hWnd   'Used in Excel and Word
    'hWnd = Application.hWndAccessApp  'Used in Access
    
    lreturn = GetWindowRect(hWnd, lpRect)
    sngAppWidth = ConvertPixelsToPoints(lpRect.Right - lpRect.Left, "X")
    sngAppHeight = ConvertPixelsToPoints(lpRect.Bottom - lpRect.Top, "Y")
    sngLeft = ConvertPixelsToPoints(lpRect.Left, "X") + ((sngAppWidth - sngWidth) / 2)
    sngTop = ConvertPixelsToPoints(lpRect.Top, "Y") + ((sngAppHeight - sngHeight) / 2)
End Sub

Public Function ConvertPixelsToPoints(ByVal sngPixels As Single, _
                                      ByVal sXorY As String) As Single
Dim hDC As Long

   hDC = GetDC(0)
   If sXorY = "X" Then
      ConvertPixelsToPoints = sngPixels * (72 / GetDeviceCaps(hDC, 88))
   End If
   If sXorY = "Y" Then
      ConvertPixelsToPoints = sngPixels * (72 / GetDeviceCaps(hDC, 90))
   End If
   Call ReleaseDC(0, hDC)
End Function
Sub StartCode()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
End Sub

 

[Micron]

OK, I think that this might be the most I want to play with it. Not sure if you considered .Move method for a form or if that won't fit your approach. Here's the parts that I edited. I opened my userform from the immediate window with userform.show and watched what happened. To see if there was any effect I added various numbers to sngTop, such as sngTop + 10
First procedure would be called by what I don't know. Sheet buttons or something? Still not sure how you planned to pass the form name.

Sub OpenForm(strFormName As String)
Dim frm As Object
Dim sngLeft As Single, sngTop As Single

For Each frm In VBA.UserForms
     If StrComp(frm.Name, strFormName, vbTextCompare) = 0 Then
          frm.Show
          Exit Sub
     End If
Next
Set frm = VBA.UserForms.Add(strFormName)
frm.Show
ReturnPosition_CenterScreen frm

End Sub

'passing form itself instead of a bunch of variables that you don't want to have to repeat per form
Public Sub ReturnPosition_CenterScreen(frm As Object)
'I don't see why your version had parameters for sngHeight or sngWidth as you don't seem to use them here
Dim sngHeight As Single, sngWidth As Single, sngLeft As Single, sngTop As Single
Dim sngAppWidth As Single, sngAppHeight As Single
Dim hWnd As Long, lreturn As Long
Dim lpRect As udtRECT

hWnd = Application.hWnd   'Used in Excel and Word
'hWnd = Application.hWndAccessApp  'Used in Access
    
lreturn = GetWindowRect(hWnd, lpRect)
sngAppWidth = ConvertPixelsToPoints(lpRect.Right - lpRect.Left, "X")
sngAppHeight = ConvertPixelsToPoints(lpRect.Bottom - lpRect.Top, "Y")
sngLeft = ConvertPixelsToPoints(lpRect.Left, "X") + ((sngAppWidth - sngWidth) / 2)
sngTop = ConvertPixelsToPoints(lpRect.Top, "Y") + ((sngAppHeight - sngHeight) / 2)

'as mentioned, not seeing use of height or width so not included in this With block
With frm
     .Left = sngLeft
     .Top = sngTop + 10
End With

End Sub

 

[DayByDay]

Honestly, I'm a little confused as to what the OP is attempting to accomplish, and as such, this may not be what the OP needs. Regardless, here's my quick solution to assigning UserForms by variable.

Function MySetUserForm(MyUserFormName) As Object

    'This Function Sets a given UserForm control by returned assignment.
    'Created 16 January 2024.
    
    'Set default value of Nothing.
        Set MySetUserForm = Nothing
    
    
    'Check to see if the specified UserForm is currently loaded, and Set UserForm, as applicable.
        For Each MySetUserForm In VBA.UserForms
            If MySetUserForm.Name = MyUserFormName Then Exit For
        Next MySetUserForm
    
    
    'Add UserForm, as Set, as applicable.
        If MySetUserForm Is Nothing Then Set MySetUserForm = VBA.UserForms.Add(MyUserFormName)

End Function

The above Function can then called from any routine, and reassigned through a standard loop, as desired:

    dim MyUserFormName:                     MyUserFormName = "My_UserForm"
    dim MyUserForm as Object:        Set MyUserForm = MySetUserForm(MyUserFormName)

    With MyUserForm
        .Width = 200
       .Top = 10
       .Left = Application.Left + 25
       .Caption = "Look, Mom!  I made a UserForm a variable!"
       .Show False
    End With

 

[Micron]

My guess is that since a year has passed it is either solved or it was dropped.