Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Glasgow, Scotland
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking a userform to an Excel Cell (Excel 2000 SR1a)

    I want to link a userform to the active cell in a worksheet so that when I click on any cell the userform appears directly above the cell on the left side. I have created a sample userform, userform1 and set the startup position to manual and set the target to top left however when I click on any cell the userform appears at least five or six cells above the active cell, how can I get the userform to appear directly above the cell I click in?

    All help much appreciated.

    Regards

    John

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Linking a userform to an Excel Cell (Excel 2000 SR1a)

    Hi John,
    My guess would be that the problem lies in the fact that the Top property for a range is measured from the top of Row 1, whereas I believe the Top property for a userform is measured from the top of the screen. The difference you're getting is probably therefore the height of the Application title bar plus any menu bars and the formula bar. I don't know of any easy way around that - possibly a WinAPI call to get the cursor position would do it.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Linking a userform to an Excel Cell (Excel 2000 SR1a)

    Following on from my last post, this should get you closer:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With UserForm1
    .Top = Target.Top + (Application.Height - Application.UsableHeight) - .Height
    .Left = Target.Left + (Application.Width - Application.UsableWidth)
    .Show
    End With
    End Sub
    You should probably check the WindowState first as this may make a difference.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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