none
ActiveWindow.VisibleRange.Left changes by tab RRS feed

  • Question

  • Thanks for taking the time to read my question.

    I'm opening a user form and am positioning it. On the first tab of my workbook it works fine but on the third tab it does not. The difference seems to be this value:

    ActiveWindow.VisibleRange.Left

    On the first tab it returns 513, on the third tab it returns 0 and I have no idea why. I suspect on the third tab it should still return 513.

    Any help would be great.

    Thanks,
    Brad

    Update:
    I have determined that the result of this calculation must be less than -120. Not sure if that helps.

    • Edited by mbrad Thursday, January 26, 2017 4:32 PM New info
    Thursday, January 26, 2017 2:18 PM

Answers

  •  actually shows on my right monitory (it would be off screen if I only had one monitor I think).

    Your are on the wrong track, it is much harder to get the screen coordinates of a cell.

    The Excel application can have multiple windows (till XL2010 which is MDI and 2013 and above is SDI) and Panes (freezed or splitted windows) and a zoom value <> 100 and finally a Fullscreen mode.

    Furthermore the formula bar can be shown or not, resp. can have different sizes.
    You can display headings or not... and and and.

    And if the visible cell in the top left is A1 the coordinates of that cell are always 0,0 in all cases above.

    Sub Test()
      With ActiveWindow.VisibleRange
        MsgBox "Left " & .Left & " Top " & .Top
      End With
    End Sub

    Play around with that sub through some scenarios.

    The only way I know to get the screen coordinates of a cell is to use a lot of Windows API code.
    If you have the point (which is in pixel) you have to convert in points.

    But before you can do that you have to examine the monitors and get the areas where they are, then you have to check if the Userform (or a part of it) is not outside the screen (or on the other monitor)...

    You need a lot code... Consider whether the effort is really worth it.

    Andreas.

    • Marked as answer by mbrad Friday, January 27, 2017 4:32 PM
    Friday, January 27, 2017 4:29 PM

All replies

  • Hi mbrad,

    you had mentioned that,"On the first tab it returns 513, on the third tab it returns 0 and I have no idea why. I suspect on the third tab it should still return 513."

    the data on the sheet are at different position so value will be changed according to that.

    see the testing result below.

    now I just scroll the sheet to right side where the value is available on the same sheet. see the result below.

    so you can see that I get different result on the same sheet.

    here I want to ask you , what you want to do actually?

    so that we can try to suggest you something useful that can solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 27, 2017 4:23 AM
    Moderator
  • ActiveWindow.VisibleRange.Left

    On the first tab it returns 513, on the third tab it returns 0 and I have no idea why. I suspect on the third tab it should still return 513.

    The VisibleRange are the visible cells on screen, means if you can see cell A1 on the top left you get 0 as result.

    But if you can see cell B1 you get a positive value (50 or 60... depends on the with of column A).

    You get not the screen coordinates! For that you have to subtract the position of the top left cell.

    (And add the window position of XLDESK and a little for real pixel coordinates).

    EDIT:

    It is hard to find the screen coordinates from the top left, an easier way is to find the hor./ver. scrollbars inside the Excel window. One pixel left and up is the last visible cell on screen.

    Andreas.


    • Edited by Andreas Killer Friday, January 27, 2017 11:12 AM additional informations
    Friday, January 27, 2017 8:18 AM
  • Thanks to both of you for your reply.

    What I am doing is using Excel kind of like MS Project, so I have the screen split on Sheet1. I have columns A and B hidden. Data on this sheet goes WAY over to the left

    On Sheet3 I do not have any columns hidden, and have data up to column E.

    This the code for UserForm1 when I open it:

    Private Sub UserForm_Initialize()
    Dim x As Integer
    
    'Debug.Print Sheets(ActiveSheet.Name).Cells(globRefRow, globRefCol - 2).Left
    'Debug.Print Sheets(ActiveSheet.Name).Cells(globRefRow, globRefCol).Width
    'Debug.Print ActiveWindow.VisibleRange.Left
    'Debug.Print ActiveWindow.Width
    
    'Debug.Print ActiveWindow.ActiveCell.Left + ActiveWindow.ActiveCell.Width - ActiveWindow.VisibleRange.Left
    
    If ActiveWindow.VisibleRange.Left = 0 Then
        Me.Left = -121
        Me.Top = ActiveWindow.ActiveCell.Top - ActiveWindow.VisibleRange.Top
    Else
        Me.Left = ActiveWindow.ActiveCell.Left + ActiveWindow.ActiveCell.Width - ActiveWindow.VisibleRange.Left
        Me.Top = ActiveWindow.ActiveCell.Top - ActiveWindow.VisibleRange.Top
    End If
     
    'Me.Left = Sheets(ActiveSheet.Name).Cells(globRefRow, globRefCol - 2).Left + Sheets(ActiveSheet.Name).Cells(globRefRow, globRefCol).Width - ActiveWindow.VisibleRange.Left
    'Me.Top = Sheets(ActiveSheet.Name).Cells(globRefRow, globRefCol - 2).Top - ActiveWindow.VisibleRange.Top
    
    x = 2
    
    Do Until Sheets("Project Settings").Cells(x, 1) = ""
        With Me.ListBox1
           .AddItem Sheets("Project Settings").Cells(x, 1)
        End With
        
        With Me.ComboBox1
           .AddItem Sheets("Project Settings").Cells(x, 1)
        End With
        x = x + 1
    Loop
    
    
    Dim ii As Integer
    
    If ActiveCell.Value = "" Then
        Me.ListBox1.ListIndex = -1
        Me.ComboBox1.ListIndex = -1
    Else
        tResources = Split(ActiveCell.Value, ", ", , vbTextCompare)
        For i = LBound(tResources) To UBound(tResources)
            If i = 0 Then
                ComboBox1.Value = tResources(i)
            Else
                For ii = 0 To ListBox1.ListCount - 1
                    If ListBox1.List(ii) = tResources(i) Then
                        ListBox1.Selected(ii) = True
                        Exit For
                    End If
                Next ii
            End If
        Next
    End If
    
    End Sub

    I have just added the IF statement for now to try and get around my issue and then make Me.Left = -121 which seems to center UserForm1 on the screen which is good enough. If I make the value even more negative, UserForm1 does not move any further left...

    UserForm1 is a listing of project resources and it opens when I click on a cell in Column E in Sheet1, and on column C in Sheet3. It works on Sheet1, but on Sheet3 the form appears all the way to the right and actually shows on my right monitory (it would be off screen if I only had one monitor I think).

    I hope this explains things more.

    Thanks,
    Brad

    Friday, January 27, 2017 3:25 PM
  •  actually shows on my right monitory (it would be off screen if I only had one monitor I think).

    Your are on the wrong track, it is much harder to get the screen coordinates of a cell.

    The Excel application can have multiple windows (till XL2010 which is MDI and 2013 and above is SDI) and Panes (freezed or splitted windows) and a zoom value <> 100 and finally a Fullscreen mode.

    Furthermore the formula bar can be shown or not, resp. can have different sizes.
    You can display headings or not... and and and.

    And if the visible cell in the top left is A1 the coordinates of that cell are always 0,0 in all cases above.

    Sub Test()
      With ActiveWindow.VisibleRange
        MsgBox "Left " & .Left & " Top " & .Top
      End With
    End Sub

    Play around with that sub through some scenarios.

    The only way I know to get the screen coordinates of a cell is to use a lot of Windows API code.
    If you have the point (which is in pixel) you have to convert in points.

    But before you can do that you have to examine the monitors and get the areas where they are, then you have to check if the Userform (or a part of it) is not outside the screen (or on the other monitor)...

    You need a lot code... Consider whether the effort is really worth it.

    Andreas.

    • Marked as answer by mbrad Friday, January 27, 2017 4:32 PM
    Friday, January 27, 2017 4:29 PM
  • Thanks for your reply Andreas,

    Sounds like I'm leaving it as is :-)

    have a great weekend.

    Brad

    Friday, January 27, 2017 4:32 PM