Consulting

Results 1 to 6 of 6

Thread: Positioning of userforms

  1. #1
    VBAX Regular
    Joined
    Aug 2004
    Posts
    22
    Location

    Positioning of userforms

    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

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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

  3. #3
    BoardCoder VBAX Regular
    Joined
    May 2004
    Location
    Istanbul, Turkiye
    Posts
    73
    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

  4. #4
    VBAX Regular
    Joined
    Aug 2004
    Posts
    22
    Location
    Quote Originally Posted by 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

    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.

  5. #5
    VBAX Regular
    Joined
    Aug 2004
    Posts
    22
    Location
    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

  6. #6
    VBAX Regular
    Joined
    Aug 2004
    Posts
    22
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •