Centre Userform over Activeworkbook

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
149
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
294
Office Version
  1. 2007
Platform
  1. Windows
Put this code in the userform module.

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
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.
VBA Code:
Sub Example()

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

End Sub
 

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
149
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
VBA Code:
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
VBA Code:
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.
VBA Code:
Sub Example()

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

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

Attachments

  • Object Required error.jpg
    Object Required error.jpg
    104.9 KB · Views: 7

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
149

ADVERTISEMENT

Put this code in the userform module.

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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 ...
Rich (BB code):
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

Board Regular
Joined
Jan 23, 2014
Messages
149

ADVERTISEMENT

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 ...
Rich (BB code):
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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 ...

VBA Code:
Call LaunchUSF(ThisWorkbook, QCopySelectedRange)

and a modified macro ...
VBA Code:
Public Sub LaunchUSF(ByVal argWb As Workbook, ByVal UserFormName As Object)
    With UserFormName
        Set .CallerWorkbook = argWb
        .Show
    End With
End Sub
 
Last edited:
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jan 23, 2014
Messages
149
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 ...

VBA Code:
Call LaunchUSF(ThisWorkbook, QCopySelectedRange)

and a modified macro ...
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,910
Members
414,346
Latest member
mmoose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top