Centre Userform over Activeworkbook

[pingme89]

I have multiple workbooks open at any particular time.
I would like to be able to open a userform that is centred over the workbook that is actually opening the userform.
I also have multiple screens so I can't use StartUpPosition as Windows Default, CenterOwner, or CenterScreen option.

There is an option for setting it manually but , the position of the Workbook may vary over time and there are multiple workbooks open at all times.

In essence, if Workbook A opens a Userform, I would like that Userfrom to be centered over WorkbookA and force the userform to be on top.

Can someone help me figure out how to do this?

Your help is much appreciated.

 

[EXCEL MAX]

Put this code in the userform module.

Option Explicit

Private Declare Function GetSystemMetrics32 Lib "User32" _
    Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
    
Dim varScreenHeight As Long, varScreenWidth As Long
Dim varUserFormHeight As Long, varUserFormWidth As Long


Private Sub UserForm_Initialize()
    
    varScreenWidth = GetSystemMetrics32(0) ' width in points
    varScreenHeight = GetSystemMetrics32(1) ' height in points
    varUserFormHeight = Me.Height
    varUserFormWidth = Me.Width

    Me.Left = varScreenWidth - varUserFormWidth / 4
    Me.Top = varScreenWidth - varUserFormHeight / 4
    
End Sub

 

[GWteB]

I would like to be able to open a userform that is centred over the workbook that is actually opening the userform.

The userform will have to be informed from which workbook it is called in order to be able to obtain the right window properties.
Therefore, a custom property is to be added to the userform, called CallerWorkbook.
Prior to displaying the userform, the calling workbook has to be assigned to this property.
This means that the macro that displays the userform must also be informed which workbook the caller is.
Code assumes that the workbook with your userform is opened in Excel.

See if this sets you on the right track.

This goes in the module of the Userform

Option Explicit

Private oWbCaller As Workbook


Public Property Set CallerWorkbook(ByVal argWb As Workbook)
    Set oWbCaller = argWb
End Property

Public Property Get CallerWorkbook() As Workbook
    Set CallerWorkbook = oWbCaller
End Property


Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
End Sub

Private Sub UserForm_Activate()
    With oWbCaller.Windows(1)
        Me.Top = .Top + (.Height / 2) - (Me.Height / 2)
        Me.Left = .Left + (.Width / 2) - (Me.Width / 2)
    End With
End Sub

This goes in a standard module of the workbook in which the Userform is located

Sub Example()

    Call LaunchUSF(ThisWorkbook)

End Sub

Public Sub LaunchUSF(ByVal argWb As Workbook)

    Dim oUsf    As Object
    
    Set oUsf = New UserForm1
    With oUsf
        Set .CallerWorkbook = argWb
        .Show
    End With
End Sub

This code to be used in any other workbook.

Sub Example()

    Application.Run "'WorkbookWithUSF.xlsm'!LaunchUSF", ThisWorkbook

End Sub

 

[pingme89]

The userform will have to be informed from which workbook it is called in order to be able to obtain the right window properties.
Therefore, a custom property is to be added to the userform, called CallerWorkbook.
Prior to displaying the userform, the calling workbook has to be assigned to this property.
This means that the macro that displays the userform must also be informed which workbook the caller is.
Code assumes that the workbook with your userform is opened in Excel.

See if this sets you on the right track.

This goes in the module of the Userform

Option Explicit

Private oWbCaller As Workbook


Public Property Set CallerWorkbook(ByVal argWb As Workbook)
    Set oWbCaller = argWb
End Property

Public Property Get CallerWorkbook() As Workbook
    Set CallerWorkbook = oWbCaller
End Property


Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
End Sub

Private Sub UserForm_Activate()
    With oWbCaller.Windows(1)
        Me.Top = .Top + (.Height / 2) - (Me.Height / 2)
        Me.Left = .Left + (.Width / 2) - (Me.Width / 2)
    End With
End Sub

This goes in a standard module of the workbook in which the Userform is located

Sub Example()

    Call LaunchUSF(ThisWorkbook)

End Sub

Public Sub LaunchUSF(ByVal argWb As Workbook)

    Dim oUsf    As Object
   
    Set oUsf = New UserForm1
    With oUsf
        Set .CallerWorkbook = argWb
        .Show
    End With
End Sub

This code to be used in any other workbook.

Sub Example()

    Application.Run "'WorkbookWithUSF.xlsm'!LaunchUSF", ThisWorkbook

End Sub

This is the error I get with I open the Userform.

 

[pingme89]

Put this code in the userform module.

Option Explicit

Private Declare Function GetSystemMetrics32 Lib "User32" _
    Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
   
Dim varScreenHeight As Long, varScreenWidth As Long
Dim varUserFormHeight As Long, varUserFormWidth As Long


Private Sub UserForm_Initialize()
   
    varScreenWidth = GetSystemMetrics32(0) ' width in points
    varScreenHeight = GetSystemMetrics32(1) ' height in points
    varUserFormHeight = Me.Height
    varUserFormWidth = Me.Width

    Me.Left = varScreenWidth - varUserFormWidth / 4
    Me.Top = varScreenWidth - varUserFormHeight / 4
   
End Sub

I tried this code. The userform doesn't appear and Excel is hung. I had to go into VBA to stop the VBA code.

 

[GWteB]

This is the error I get with I open the Userform.

Did you copy ALL of the code?
The error you are getting is because the CallerWorkbook property is not set.
Your userform should be launched like ...

Sub Example()
    Call LaunchUSF(ThisWorkbook)
End Sub

Public Sub LaunchUSF(ByVal argWb As Workbook)

    Dim oUsf    As Object
    
    Set oUsf = New UserForm1
    With oUsf
        Set .CallerWorkbook = argWb
        .Show
    End With
End Su

 

[pingme89]

Did you copy ALL of the code?
The error you are getting is because the CallerWorkbook property is not set.
Your userform should be launched like ...

Sub Example()
    Call LaunchUSF(ThisWorkbook)
End Sub

Public Sub LaunchUSF(ByVal argWb As Workbook)

    Dim oUsf    As Object
   
    Set oUsf = New UserForm1
    With oUsf
        Set .CallerWorkbook = argWb
        .Show
    End With
End Su

This works when I change Set oUsf = New UserForm1 -> Set oUsf = New QCopySelectedRange

where QCopySelectedRange is the name of my userform.
Is there a way where I can reuse this code where I can put the name of the Userform in a variable?

I tried to modify it to work but I couldn't get it to work.

Public Sub LaunchUSF(ByVal argWb As Workbook, ByVal UserFormName As Object)

Dim oUsf As Object

Set oUsf = New UserFormName
With oUsf
Set .CallerWorkbook = argWb
.Show
End With
End Sub

This code gave me another error.

 

[GWteB]

This works when I change Set oUsf = New UserForm1 -> Set oUsf = New QCopySelectedRange

Assumed this was obvious. I should have mentioned that you had to put the actual name of the userform in the place of "Userform1".

Is there a way where I can reuse this code where I can put the name of the Userform in a variable?

No, not in this manner.
Nevertheless, I would advise against this, because then you have to create an extra macro to call up this modified macro.

This (my modified) code gave me another error.

You do not let us know under which conditions and on which line you encounter an error message.
Be sure passing the userform as second argument to your modified procedure and qualified as Object (rather then a string with quotation marks), like ...

Call LaunchUSF(ThisWorkbook, QCopySelectedRange)

and a modified macro ...

Public Sub LaunchUSF(ByVal argWb As Workbook, ByVal UserFormName As Object)
    With UserFormName
        Set .CallerWorkbook = argWb
        .Show
    End With
End Sub

 

[GWteB]

I forget one more important aspect!
Your wish was to make the appearance of the userform dependent on OTHER workbooks. Those other workbooks do not have direct access to the userform object.
This means, that even though you know its name, you cannot simply use it in the code of other workbooks. This will produce (depending on the method used) either compile errors or run-time errors.
In this regard, it makes little or no sense to change the LAUNCH procedure to accept a userform object as an argument.

 

[pingme89]

Assumed this was obvious. I should have mentioned that you had to put the actual name of the userform in the place of "Userform1".


No, not in this manner.
Nevertheless, I would advise against this, because then you have to create an extra macro to call up this modified macro.


You do not let us know under which conditions and on which line you encounter an error message.
Be sure passing the userform as second argument to your modified procedure and qualified as Object (rather then a string with quotation marks), like ...

Call LaunchUSF(ThisWorkbook, QCopySelectedRange)

and a modified macro ...

Public Sub LaunchUSF(ByVal argWb As Workbook, ByVal UserFormName As Object)
    With UserFormName
        Set .CallerWorkbook = argWb
        .Show
    End With
End Sub

Thank you for all your help. I will go with your suggestions.