Positioning of userforms

[Johannes]

Hi, I'm currently developing a report where a point & figure chart is drawn on a worksheet (not a 'chartsheet'). A userform allows the user to track the chartingprocess. The problem is that the userform tends to place itself on top of the active cell. As I am using the Hide and Show properties of the userform when the code is looping, the form will relocate itself in the middle of the screen the next time it appears, so it doesn't really help to drag the form to another position of the screen either. I know that it is possible to state the position of the form in the postion porperties of the userform, the drawback with that is that I cannot know in advance where the active cell will be on the screen at any time.

Is there a way to 'tell' the userform to remember the last position it was dragged to? Alternatively to find the position of the active cell on the screen and then change the position properties of the userform to a different location?

This forum is really useful, so I am already looking forward to any feedback you guys (and girls) may be able to give me ...

Johannes

[mdmackillop]

Hi Johannes,
I don't know of a setting that will do this, but you could manipulate the form position, depending upon the activecell position
MD

Private Sub UserForm_Initialize()
    Me.Top = ActiveCell.Top + 150
End Sub

[smozgur]

Hi Johannes,

We can store the userform location before closing the form in the registry key. And load those values when we open the form. So it remembers where it was closed and opened there.

Option Explicit

Private Const AppName As String = "MyApplication"
Private Const StartX As String = "StartPositionX"
Private Const StartY As String = "StartPositionY"

Private Sub UserForm_Activate()
Dim formX
Dim formY
    'If there is no registry value stored yet, then leave them blank
    'so userform will be opened at center of the screen
    formX = GetSetting(AppName, Me.Name, StartX, "")
    formY = GetSetting(AppName, Me.Name, StartY, "")
    If IsNumeric(formX) And IsNumeric(formY) Then
        Me.Move formX, formY
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'Closing the form, store location information
    SaveSetting AppName, Me.Name, StartX, Me.Left
    SaveSetting AppName, Me.Name, StartY, Me.Top
End Sub

Since I used a form named as Userform1, the registry keys will be saved at this path:

HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApplication\UserForm1

You can set "MyApplication" however you like.

Is this method safe ? Yes, it is a method mostly we use for storing small data since it is very easy to retrieve and save by using GetSetting and SaveSetting function.

Attached is the sample workbook for this. I hope it helps.

Suat

[Johannes]

Hi Johannes,
I don't know of a setting that will do this, but you could manipulate the form position, depending upon the activecell position
MD

Private Sub UserForm_Initialize()
Me.Top = ActiveCell.Top + 150
End Sub

Thanks. This works fine. Only problem is that the userform seems to appear in a random location (for the user), but it is a quick fix to the problem and is certainly much better than having the form appear on top of the active cell, when the active cell happens to be in the same location as the user form.

[Johannes]

Thank you Suat! Your solution is obviously the best and seems to be working very well, although I have not had a lot of time yet to incorporate it in my own code and test it out. Just out of curiosity, what other things do you typically store in the registry ?
Johannes

[Johannes]

Just a feedback on the final solution to my problem:

Although I did learn a lot from the advise I got, in the end I was not able to make any of them work well enough to solve my particular problem. Storing the userform location in registry is a great alternative, but I could not make it work with the userform.hide command. As far as I could find out, the method requires that the userform.unload method is used.

To manipulate the form location according to the active cell poisition was a very quick and easy method, but the problem turned out to be that as the active cell could move out of the visible section of the sheet, the userform would also disappear.

So I finally found a procedure in one of J.Walchenbach's books and it sort of works very well and looks very sophisticted to the user, although it is not the most efficient method (which would be to drag the form to a new position and stor the location in the registry..if it only would work with the userform.hide method..) With J. Walchenbach's method I have inserted 3 scrollbar controls in my userform together with the code below. This allows the user to scroll the sheet horisontally, vertically and also to zoom in and out, while the userform is still displayed.

Private Sub UserForm_Initialize()
LabelZoom.Caption = ActiveWindow.Zoom
'zoom
With ScrollBarZoom
.Min = 10
.Max = 400
.SmallChange = 1
.LargeChange = 10
.Value = 100
End With
'Horisontal Scrolling
With ScrollBarColumns
.Min = 1
.Max = 256
.Value = ActiveWindow.ScrollColumn
.LargeChange = 25
.SmallChange = 1
End With
'vertical scrolling
With ScrollBarRows
.Min = 1
.Max = ActiveSheet.Rows.Count
.Value = ActiveWindow.ScrollRow
.LargeChange = 25
.SmallChange = 1
End With
End Sub
 
Private Sub ScrollBarZoom_Change()
With ActiveWindow
.Zoom = ScrollBarZoom.Value
LabelZoom = .Zoom & "%"
End With
End Sub

Private Sub ScrollBarColumns_Change()
ActiveWindow.ScrollColumn = ScrollBarColumns.Value
End Sub
 
Private Sub ScrollBarRows_Change()
ActiveWindow.ScrollRow = ScrollBarRows.Value
End Sub