FlexGrid Sorting/Rearranging Columns

[eyeRmonkey]

I think I finally figured out how to use the FlexGrid control. I have been told that you can’t sort by column in a FlexGrid, but there is a .Sort property. I was wondering how to use that property. I saw the constants in the Object Browser, but how do I apply the property to a certain column? Or can it only be applied to the left most column? In the Object Broswer it says is sorts selected rows, does that mean I can’t sort the entire grid by column?

Which brings me to my next question: how do I rearrange columns? Like if someone drags a column heading to the other side of the grid I want it to drop there and all the text in that column to move with it. Would I have to manually write all the code including rewriting the whole text matrix?

Also, how do I get the text in the upper cell of a FlexGrid to be left aligned?

[BruceG]

To answer your question on sorting by a certain column, you can use this general routine (I would place this in a module) :

'-----------------------------------------------------------------------------
Public Sub SortGrid(pobjGrid As MSFlexGrid, ByVal plngSortCol As Long)
'-----------------------------------------------------------------------------
    
    With pobjGrid
    
        ' suppress changes in display until done
        .Redraw = False
    
        ' Sort using the clicked column.
        .Col = plngSortCol
        .ColSel = plngSortCol
        .Row = 0
        .RowSel = 0
    
        ' If this is a new sort column, sort ascending.
        ' Otherwise switch which sort order we use.
        If mlngSortCol <> plngSortCol Then
            mintSortOrder = flexSortGenericAscending
        ElseIf mintSortOrder = flexSortGenericAscending Then
            mintSortOrder = flexSortGenericDescending
        Else
            mintSortOrder = flexSortGenericAscending
        End If
        .Sort = mintSortOrder
    
        ' Restore the previous sort column's name.
        If mlngSortCol = 0 Then
            If Left$(.TextMatrix(0, 0), 2) = "< " _
            Or Left$(.TextMatrix(0, 0), 2) = "> " _
            Then
                .TextMatrix(0, 0) = Mid$(.TextMatrix(0, 0), 3)
            End If
        Else
            .TextMatrix(0, mlngSortCol) = Mid$(.TextMatrix(0, mlngSortCol), 3)
        End If
    
        ' Display the new sort column's name.
        mlngSortCol = plngSortCol
        If mintSortOrder = flexSortGenericAscending Then
            .TextMatrix(0, mlngSortCol) = "< " & _
                .TextMatrix(0, mlngSortCol)
        Else
            .TextMatrix(0, mlngSortCol) = "> " & _
                .TextMatrix(0, mlngSortCol)
        End If
    
       ' refresh the display
        .Redraw = True
    
    End With
    
End Sub

USAGE:

'-----------------------------------------------------------------------------
Private Sub MSFlexGrid1_MouseUp(Button As Integer, _
                                Shift As Integer, _
                                X As Single, _
                                Y As Single)
'-----------------------------------------------------------------------------
    
    ' If this is not row 0, do nothing.
    If MSFlexGrid1.MouseRow <> 0 Then Exit Sub
 
    MSFlexGrid1.Highlight = flexHighlightNever
    
    ' Sort by the clicked column.
    SortGrid MSFlexGrid1, MSFlexGrid1.MouseCol
 
End Sub

NOTES:
When setting up the grid, it is assumed that you have set FixedRows to 1 (thus reserving the first row, row 0, for the column headings). In my example, I am not using any fixed columns, but if you are, than you should test for that in your MouseUp routine, i.e.:
If MSFlexGrid1.MouseCol = 0 Then Exit Sub
Also, as a rule of thumb, if the column contains numeric data, concatenate one blank space at the beginning of the data, i.e.:
" " & TheNumericData
Additionally, for dates, format them with the year first:
" " & Format$(TheDateField, "yyyy-mm-dd")

-----------------------------------------------------------------

Can't help you with rearranging columns

----------------------------------------------------------------
To set the alignment of text in the current cell, you can use the CellAlignment property.

I hope this has helped.

[brucevde]

You can sort by column, in fact you can sort by multiple columns.

When the Sort property is called, data is sorted by the currentl column(s). The order is always left to right and you can only sort by contiguous columns.

'code to sort by columns 1 and 2
 With FlexGrid
     .Col = 1
     .ColSel = 2
     .Sort = flexSortStringAscending
End With

To align columns or individual cells

With FlexGrid
     .ColAlignment(1) = flexAlignLeftCenter 'align entire column
     .Col = 1
     .Row = 7
     .CellAlignment = flexAlignRightCenter 'cell alignment overrides column alignment
End With

To Move a column, change its ColPosition

Private lngMoveColumn as Long 
 
Private Sub grdDetails_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If grdDetails.MouseRow = 0 And grdDetails.MouseCol >= grdDetails.FixedCols Then
        lngMoveColumn = grdDetails.MouseCol
    Else
        lngMoveColumn = -1
    End If
End Sub
 
Private Sub grdDetails_MouseUp(Button As Integer, Shift As Integer, x As Single, y As Single)
    If grdDetails.MouseRow = 0 And lngMoveColumn <> -1 Then
        grdDetails.ColPosition(lngMoveColumn) = grdDetails.MouseCol
    End If
End Sub

[eyeRmonkey]

Is there a way to allow multiple selections on a FlexGrid using CTRL? Right now all I can do is hold SHIFT and select ranges. Is there away to allow multiple (individual) selections using CTRL like in Windows Explorer and other environments?

[eyeRmonkey]

I'm starting to think doing a multiple select using CTRL isn't possible after looking at all the properties of the FlexGrid.

Does anyone know of a way to do this?

[brucevde]

The FlexGrid does not support the "extended selection" functionality. But it is easy enough to simulate. This code is for the Hierarchical flex grid but it should work on just the FlexGrid.

Basically, it uses the back ground colors to simulate selecting multiple non-contiguous rows.

Private Sub Form_Load()
   
    MSHFlexGrid1.HighLight = flexHighlightNever
 
End Sub
 
Private Sub MSHFlexGrid1_Click()
    Dim lngColor As Long
    
    With MSHFlexGrid1
        .Redraw = False
 
        lngColor = .BackColor
    
        If .CellBackColor = lngColor Then 
            lngColor = .BackColorSel
        End If
    
        .FillStyle = flexFillRepeat
        .Col = 1
        .ColSel = .Cols - 1
        .CellBackColor = lngColor
        .FillStyle = flexFillSingle
        
        .Redraw = True
    
    End With
    
End Sub

[dglienna]

Only in BigSelection's. There is two options, one for using Control and the other for select each.

[eyeRmonkey]

The FlexGrid does not support the "extended selection" functionality. But it is easy enough to simulate. This code is for the Hierarchical flex grid but it should work on just the FlexGrid.

Basically, it uses the back ground colors to simulate selecting multiple non-contiguous rows.

Private Sub Form_Load()
   
    MSHFlexGrid1.HighLight = flexHighlightNever
 
End Sub
 
Private Sub MSHFlexGrid1_Click()
    Dim lngColor As Long
    
    With MSHFlexGrid1
        .Redraw = False
 
        lngColor = .BackColor
    
        If .CellBackColor = lngColor Then 
            lngColor = .BackColorSel
        End If
    
        .FillStyle = flexFillRepeat
        .Col = 1
        .ColSel = .Cols - 1
        .CellBackColor = lngColor
        .FillStyle = flexFillSingle
        
        .Redraw = True
    
    End With
    
End Sub

If I understand this right then I have 2 problems with it.

1) I don't have a way to check which rows are selected (I have selection mode set to row) without checking the background color of every row in the table.
2) I want it to only select multiple rows only if CTRL is pressed and I want it to select ranges if SHIFT is pressed. It is easy enough to add the CTRL part useing a module level variable. But I'm not 100% sure how to select ranges using this method.

[davincinewfie]

tryign to get on form load for it to move column 4 to column 1 automaticlly

constant errors on everythign I tried so far

wondering if you guys could help me out??

[si_the_geek]

Welcome to VBForums

Based on post #3 above, this should do it:

MSFlexGrid1.ColPosition(3) = 1

[davincinewfie]

dang your good and quick!!

worked like a charm, I was wayyyy over complicating things, now if to figgure out how to sort dates when they are like yyyy/mm/dd

hate to be constantly asking for help but skill arent what they used to be for vb, too many years of linux programming, back on the redmond side for a change :-S

[brucevde]

When the format is yyyy/mm/dd a simple string sort will work.

Private Sub Form_Load()
    With Me.MSFlexGrid1
        .Rows = 5
        .TextMatrix(1, 1) = "2007/06/23"
        .TextMatrix(2, 1) = "2007/01/05"
        .TextMatrix(3, 1) = "2007/06/03"
        .TextMatrix(4, 1) = "2007/01/01"
        .Sort = flexSortStringAscending
    End With
End Sub

[davincinewfie]

man yee guys are good sooooooOOOooOo many thanks

thank you all for your time in helping me, if I could only be as helpful to others as you are to me

many many thanks