Persistent Userform Positioning
Posted by Derek Mang on 30th October 2008
When using Userforms within Office, you have 4 options for its display position. (the StartUpPosition property)
0 - Manual, where you'll need to provide some values for the left and top positions,
1 - Centre Owner, where the userform will be centred within the host application screen,
2 - Centre Screen, and
3 - Windows Default, in the top left.
With all of these options, the userform presents in the same position, and hopefully your end-user is OK with it. And yes, the userform can be moved around while in use to a more convenient location if desired.
Rather than have your end-user move the userform whenever its displayed, you can quite simply position it wherever it had been previously using the SaveSetting and GetSetting methods. These methods respectively save and retrieve information to / from the registry. Timing is essential. The information should retrieved in the Initialize event, prior to actual userform display and saved in the QueryClose event, while userform properties are still available to you.
The StartUpPosition property must be set to manual (value 0 ) for this to work as expected. To make this as generic as possible, yet specific to your project, the activevbproject name and the userform name are used in constructing the setting information.
Private Sub UserForm_Initialize()
VBENAME = Application.VBE.ActiveVBProject.Name
Me.StartUpPosition = 0 'manual
Me.Top = CLng(GetSetting(VBENAME, Me.Name, "Top", CStr(Me.Top)))
Me.Left = CLng(GetSetting(VBENAME, Me.Name, "Left", CStr(Me.Left)))
Me.Width = CLng(GetSetting(VBENAME, Me.Name, "Width", CStr(Me.Width)))
Me.Height = CLng(GetSetting(VBENAME, Me.Name, "Height", CStr(Me.Height)))
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
SaveSetting VBENAME, Me.Name, "Top", CStr(Me.Top)
SaveSetting VBENAME, Me.Name, "Left", CStr(Me.Left)
SaveSetting VBENAME, Me.Name, "Width", CStr(Me.Width)
SaveSetting VBENAME, Me.Name, "Height", CStr(Me.Height)
End Sub
Sub DeleteSettings()
DeleteSetting VBENAME
End Sub
The DeleteSettings sub will remove all settings for your project.
If you want to get a little fancier, why not include Andy Pope's resizer code in your user form, found here - http://www.andypope.info/vba/resizeform.htm
Posted in Office (All) | No Comments »