[RESOLVED] MSFLEXGRID - how to get column number of 1st displayed column?

[svg3414]

In an MSFLEXGRID with scroll bars, how do i get column/row number of the 1st displayed column/row?

[Siddharth Rout]

One way I can think of is to use .RowIsVisible property for every row starting with the .TopRow

Similarly, you can use .ColIsVisible for columns

[Optional]

All you should need is the TopRow and LeftCol properties.

The TopRow property returns the uppermost visible row, other than the fixed row.

The LeftCol property returns the left-most visible column, other than the fixed column.

A few examples on using the TopRow and LeftCol properties.

To get the column/row number of the 1st displayed column/row:

Private Sub uiGetFirstVisibleRowAndCol_Click()
    Call MsgBox("1st visible Row is: " & MSFlexGrid1.TopRow)
    Call MsgBox("1st visible Column is: " & MSFlexGrid1.LeftCol)
End Sub

To Write a value to the 1st displayed column/row:

Private Sub uiSetValue_Click()
    MSFlexGrid1.TextMatrix(MSFlexGrid1.TopRow, MSFlexGrid1.LeftCol) = "New Value in 1st visible column/Row"
End Sub

To Get the value of the 1st displayed column/row:

Private Sub uiGetValue_Click()
    Call MsgBox(MSFlexGrid1.TextMatrix(MSFlexGrid1.TopRow, MSFlexGrid1.LeftCol))
End Sub

[svg3414]

Thanks Optional.

Similarly, what about the last row/column.

Another question : is it possible to hide the scroll bar (without disabling scrolling)?

[si_the_geek]

There is no property for the last row/column, you need to calculate it somehow.

The two main ways are:

• loop from .TopRow to .Rows-1 checking .RowIsVisible() to see when it becomes False - but be careful of rows that are partly visible, as the way RowIsVisible treats it may not be what you expect.
• write your own code to do the equivalent of .RowIsVisible, based on .RowPos and .RowHeight for each of the rows. This takes more work, but you can determine when a row is partly visible, and treat it as you want.

You can't set a property to hide the scrollbar without disabling scrolling, but what you can do is put the grid inside a picturebox, and set the width of the picturebox to the width of the grid minus the width of the scrollbar.

[Optional]

I'm assuming you mean last visible column/row.

If so you need to use the .ColIsVisible and .RowIsVisible properties of the Grid in combination with .TopRow and .LeftCol as koolsid touched on and si_the_geek mentioned above.

The following sample code will display the number of the last visible col/row in a MSFLEXGRID.

Private Sub uiGetLastVisibleColRows_Click()
    Dim i As Integer
    Dim lastVisible As Integer
    
    lastVisible = 0
    
    For i = MSFlexGrid1.TopRow To (MSFlexGrid1.Rows - 1)
        If ((Not (MSFlexGrid1.RowIsVisible(i))) Or (i = (MSFlexGrid1.Rows - 1))) Then
            lastVisible = (i - MSFlexGrid1.FixedRows)
            Exit For
        End If
    Next i
    
    Call MsgBox("Last visible Row is: " & lastVisible)
    
    lastVisible = 0
    
    For i = MSFlexGrid1.LeftCol To (MSFlexGrid1.Cols - 1)
        If ((Not (MSFlexGrid1.ColIsVisible(i))) Or (i = (MSFlexGrid1.Cols - 1))) Then
            lastVisible = (i - MSFlexGrid1.FixedCols)
            Exit For
        End If
    Next i
    
    Call MsgBox("Last visible Column is: " & lastVisible)
End Sub

The "MSFlexGrid1.Cols - 1" in the for loop is because .ColIsVisible and .RowIsVisible are 0 based.

The additional checks on "Or (i = (MSFlexGrid1.Rows - 1))" and "Or (i = (MSFlexGrid1.Cols - 1))" is used to catch the correct values even if the last visible column/row is the actual last column/row in the grid.

Edit
To add to si_the_geek's comment on partial rows/columns. When I tested the code any partially shown row and column would be considered visible and .RowIsVisible and .ColIsVisible returned True. I tested more by changing the height/width of the grid at runtime dynamically and it seems that even if only a single pixel is visible the row/column is deemed visible.

Watch out for that

Hope this Helps.

[Siddharth Rout]

@svg3414: Seems like you missed Post 2....

[svg3414]

Thanks Optional & Si_the_Geek for the advice.

Actually my problem is this:

I want to make the cells editable as in Spreadsheet applications like Excel etc. For this I am diplaying a textbox at the exact position of the selected cell. This creates problems when scrolling if the selected cell goes out of the display.

I have made some modifications based on your suggestions and now it seems to work ok.

[RhinoBull]

Similarly, what about the last row/column.

Something like this will give you last visible row:

Option Explicit

Private Sub Command1_Click()
Dim lastRowVisible As Long

    lastRowVisible = GetLastRow(MSFlexGrid1)
    MsgBox "Last visible row Index = " & lastRowVisible

End Sub

Private Function GetLastRow(flx As MSFlexGrid) As Long
Dim TotalRowCount As Integer

    'assuming all rows have the same row height
    TotalRowCount = flx.Height / flx.RowHeight(0) - flx.FixedRows
    GetLastRow = (flx.TopRow + TotalRowCount) - 1
    
    'check if row is visible partially
    If flx.Height <= flx.RowPos(flx.Rows - 1) + flx.RowHeight(flx.Rows - 1) Then
        GetLastRow = GetLastRow - 1
    End If
    
    'check if horizontal scrollbar is visible (there could be a better way to determine this)
    If flx.Width <= flx.ColPos(flx.Cols - 1) + flx.ColWidth(flx.Cols - 1) Then
        GetLastRow = GetLastRow - 1
    End If

End Function

Something similar to that will give you last visible column.