VBFlexGrid Control (Replacement of the MSFlexGrid control)

[ChenLin]

I do not know if this is a bug: Change BackColor when the BackColorAlt also changed, need to re-set BackColorAlt.

[Krool]

I do not know if this is a bug: Change BackColor when the BackColorAlt also changed, need to re-set BackColorAlt.

That is not a bug, it's a feature.
Since in the original MSFlexGrid is no such BackColorAlt property it must be changed synchronous whenever the BackColor changes to maintain compatibility.

[Karl77]

Awesome work!
And great to have a better grid in future.

I use MSFlexgrid quite often, and that is really cumbersome (mousewheel etc.).
Also I don't like the dependency to MSFLXGRD.OCX.

---

"- FormatString property not implemented"

Before I change my code in dozens of places:
Is FormatString not implemented YET or will it not happen?

---

Do you plan to implement something like AutoSizeColWidth?
Today I have to put the col content into a label, AutoSize=true, read the width, and then set the col width.
That is the opposite of fast and cumbersome as well.

[Krool]

Here also some minor "DPI Aware" updates. Mainly in the internal drawing routines.
There is only a very minor further tweak possible, namely the DrawFocusRect API.
Currently is still done by assuming as 1px thick.
So here could be done a minor further improvement by taking SPI_GETFOCUSBORDERWIDTH/SPI_GETFOCUSBORDERHEIGHT with SystemParametersInfo into account.
Has somebody expierence with this? I mean is this really related to high DPI? Or is this just a system setting that can be set to whatever regardless of DPI?
The documentation is not clear about this..

"- FormatString property not implemented"

Before I change my code in dozens of places:
Is FormatString not implemented YET or will it not happen?

I wanted to avoid to implement such a cumbersome property. (in my opinion)
But I can imagine that for many migration purposes it would be indeed easier to have it.
So yes, it is not YET implemented.

Do you plan to implement something like AutoSizeColWidth?

AutoSize features are needed, so yes planning to do so.

[Krool]

Update released.

Included the ClipSeparators property.
The two distinct characters will be used as column (first character) and row (second character) separators in clip strings. (.Clip property and .AddItem method)
If the property is set to empty, the defaults vbTab and vbCr are used.

While vbTab and vbCr (defaults) are useful when interacting between Excel spreadsheet and the flex grid it becomes a obstacles when dealing with a Recordset.
For instance when you use the 'GetString' method in a Recordset you can define a custom Column and Row delimiter. Now you can apply the same delimiters in the ClipSeperators property.
Reason: The delimiter vbTab and vbCr can also be contained within some text fields in the Recordset. So in most cases it is better to define other delimiters to circumvent that issue.

[Krool]

Update released.

Bugfix concerning the FixedAlignment property.
When not preset (internal value of -1) it returns now the ColAlignment property. (like in MSFlexGrid)
Also the CellAlignment (when not preset) returns then the ColAlignment or the FixedAlignment (if fixed cell and preset) property.
Unlike in the MSFlexGrid the FixedAlignment can be turned back to not preset by setting a value of -1.

"- FormatString property not implemented"

Before I change my code in dozens of places:
Is FormatString not implemented YET or will it not happen?

FormatString property is now implemented. I would appreciate if you could test it extensively.

[chosk]

Hi Krool,

I have tried to look for but not able how to go about doing this.

I have 1 fixed column on the left.
a) When I click on any cell in the fixed column, I want to set the SelectionMode to 1-ByRow and highlight and select that row. This will allow an option to Insert or Add a row.
b) Click on any other cell, I change to SelectionMode to 0-Free and select that cell for editing.

I have problem to capture the click on the left fixed column cell. Is this possible?

Edit:
If I can capture the click on the left fixed column cell, I can do the rest.

[chosk]

I found it. MouseCol and MouseRow.

[Tech99]

Downloaded and briefly fiddled with this - looks very promising. Do you plan to support in-cell editing?

[chosk]

Have not use FlexGrid before, not even MS. This is first time so a lot of features to get familiar with.

No need to change SelectionMode. This will do it.

Private Sub FlexGrid_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    With FlexGrid
        If .MouseCol = 0 Then
            .Row = .MouseRow
            .Col = .MouseCol
            .RowSel = .Row
            .ColSel = .Cols - 1
        End If
    End With
    
    If Button = 2 Then
        'ToDo
        'Popup menu to InsertRow and AddRow
    End If
End Sub

[Krool]

@ Chosk, you can achieve what you want with .AllowBigSelection = True without any additional code. Of course for showing the Popup you need to handle like you did.

Do you plan to support in-cell editing?

Yes, first point was to achieve an replacement. But of course more features will be added by the time.

[chosk]

@ Chosk, you can achieve what you want with .AllowBigSelection = True without any additional code. Of course for showing the Popup you need to handle like you did.

Thanks, Krool.

Will check it out.

[chosk]

Hi Krool,

Re: .AllowBigSelection = True.

I read up on this. Actually nothing much to read - very simple.
https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx

So I play around with this property.

When True:
I can only select Column but cannot select Row.
Can also select entire grid by clicking on the Top Left cell where both Headers meet, like in Excel.

When False:
The cell immediately below the Column Header is selected (normal). Spariodically, the cell below the Column Header get highlighted and scroll up one row when a random Column Header is clicked. Nothing happen when I click on Row Header.

[Krool]

Hi Krool,

Re: .AllowBigSelection = True.

I read up on this. Actually nothing much to read - very simple.
https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx

So I play around with this property.

When True:
I can only select Column but cannot select Row.
Can also select entire grid by clicking on the Top Left cell where both Headers meet, like in Excel.

When False:
The cell immediately below the Column Header is selected (normal). Spariodically, the cell below the Column Header get highlighted and scroll up one row when a random Column Header is clicked. Nothing happen when I click on Row Header.

I can select the row when .AllowBigSelection = True.
Did you try in the VBFlexGridDemo if you can do select the row?

[chosk]

I forgot about the demo. Yes, I can select row when .AllowBigSelection = True. I will see what I have done differently.

[chosk]

I found out what is causing this ".AllowBigSelection = True" not working for row. When AllowUserResizing is 0-None or 1-Column then it won't work.

[Krool]

I found out what is causing this ".AllowBigSelection = True" not working for row. When AllowUserResizing is 0-None or 1-Column then it won't work.

Update released. There was indeed an minor bug in an internal function (GetHitTestInfo).
Now it should work as expected. Thanks for reporting.

[chosk]

Thanks, Krool.

[chosk]

Hi Krool,

When .AllowBigSelection = False:
Spariodically, the cell below the Column Header get highlighted and scroll up one row when a Column Header is clicked.

[Krool]

Hi Krool,

When .AllowBigSelection = False:
Spariodically, the cell below the Column Header get highlighted and scroll up one row when a Column Header is clicked.

That behavior is expected and equals to MSFlexGrid. The "spariodically" is because you slightly move the mouse after your click, that's why it is scrolling up while your moving. (which is also expected and no bug)

[chosk]

Removed

[chosk]

Removed

[chosk]

Hi Krool,

Sorry for the trouble. I removed my earlier 2 posts because after giving some thoughts, maybe the ListView is more appropriate for my use. I want to be able to select only rows and not columns and only use the scrollbar to scroll the rows. The auto-scrolling of the rows in only one direction - upwards - may pose a problem for me.

Now I just go find a way to simulate ListView column one (ListItem) fixed and not editable. I hope can be done.

[Krool]

Hi Krool,

Sorry for the trouble. I removed my earlier 2 posts because after giving some thoughts, maybe the ListView is more appropriate for my use. I want to be able to select only rows and not columns and only use the scrollbar to scroll the rows. The auto-scrolling of the rows in only one direction - upwards - may pose a problem for me.

Now I just go find a way to simulate ListView column one (ListItem) fixed and not editable. I hope can be done.

Try following before you switch to ListView:

SelectionMode = 1 - ByRow
AllowBigSelection = False

and add following Code:

Private Sub VBFlexGrid1_BeforeRowColChange(ByVal NewRow As Long, ByVal NewCol As Long, Cancel As Boolean)
If NewRow < VBFlexGrid1.TopRow Then
    VBFlexGrid1.Row = VBFlexGrid1.TopRow
    Cancel = True
End If
End Sub

Private Sub VBFlexGrid1_BeforeSelChange(ByVal NewRowSel As Long, ByVal NewColSel As Long, Cancel As Boolean)
If NewRowSel < VBFlexGrid1.TopRow Then
    VBFlexGrid1.RowSel = VBFlexGrid1.TopRow
    Cancel = True
End If
End Sub

[chosk]

Hi Krool,

Thanks for the suggestions. I have been playing around with it today and I think if I can do just 1 thing and the VBFlexGrid will be perfect for the job.

To explain...
I need :
AllowBigSelection = True
SelectionMode = 0-Free

Reasons:
I need to be able to select single row or multiple rows.
I need to be able to go to any cell and edit (I will implement the edit myself until you are ready).

I have this in MouseMove to disable selecting column:

Private Sub FlexGrid_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    With FlexGrid
        If (.MouseRow = 0) And (.MouseCol <> 0) Then
            .AllowBigSelection = False
        Else
            .AllowBigSelection = True
        End If
    End With
End Sub

With your codes suggestion, I am getting there but not quite. If I were to left-click and hold on the column header, the row will still scroll up but very slowly and the cells in that column that scroll will be selected. I want to prevent this.

So actually in short, what I want to achieve is to just ignore the left mouse click (and also click and hold) on the column header, just like with the present behavior of right mouse click. The selected cell remain where it is and not moved.

If maybe you can point me to where in your code I can 'rem' out the appropriate part then I don't have to trouble you. The default behavior of your VBFlexGrid will then remains as it is.

Thanks.

[Krool]

If maybe you can point me to where in your code I can 'rem' out the appropriate part then I don't have to trouble you. The default behavior of your VBFlexGrid will then remains as it is.

I am thinking of including a 'Flags' property with enum constants which will control various behaviors.
If 0 -None it will equal to MSFlexGrid and by the time maybe more flags will get included.
Do you have an idea how to call your behavior change?
Maybe 'FlexFlagDisableNonBigSelectionFixedRowMouseMoveScroll' ?

Edit: And of course it would be designed that in future multiple flags can be set together. (Like in MsgBox)

[chosk]

'FlexFlagDisableNonBigSelectionFixedCellClickScroll'. This is so funny.

How about "AllowBigSelectionMode"
0 - FlexFlagAllowBigSelectionFixedCellClickScrollBoth (Default)
1 - FlexFlagAllowBigSelectionFixedCellClickScrollHorizontalOnly
2 - FlexFlagAllowBigSelectionFixedCellClickScrollVerticalOnly

No lah.

Copying the idea from SelectionMode:
0 - FlexBigSelectionModeBoth (Default)
1 - FlexBigSelectionModeByRow
2 - FlexBigSelectionModeByColumn

"AllowBigSelectionMode" because then it will be just below "AllowBigSelection" in the Properties List:
AllowBigSelection
AllowBigSelectionMode

And maybe also something like this group together with the Scroll properties:

ScrollByHeader
0 - True (Default)
1 - False

[Krool]

Ok, I postpone the Flags idea.
Then let's just introduce a new property called 'AllowDragScroll'.
It can then be set to following:
FlexAllowDragScrollEverywhere = 0 (default)
FlexAllowDragScrollNever = 1
FlexAllowDragScrollFixed = 2
FlexAllowDragScrollScrollable = 3

In your case you would then set to 'FlexAllowDragScrollScrollable' since you want to block the Fixed cells.
I think a differantiation between Rows and Column is not necessary because who would want to be drag scrolling in the scrollable area only by rows and not for columns?

And about the 'AllowBigSelectionMode'. Nice idea that would allow more detailed configuration for Both, ByRow or ByColumn of big selection.

[chosk]

In your case you would then set to 'FlexAllowDragScrollScrollable' since you want to block the Fixed cells.
I think a differantiation between Rows and Column is not necessary because who would want to be drag scrolling in the scrollable area only by rows and not for columns?

Yes.
Thanks.

[Krool]

Chosk, I would like to discard the new AllowDrag.. property.

Actually there is only a minor change necessary.
Currently the "DragSelection" is running immediately. (just like in MSFlexGrid)
But it would be better behavior to go into "DragSelection" mode when dragging the mouse out of the first captured cell. That behavior change would not make any difference in the scrollable cells, but especially for the fixed headers.

We could make this minor change and break behavior to MSFlexGrid for the better or we could make some other property (or Flag 'FlexFlagNoImmediateDragSelection' ? ) to control if "DragSelection" will start immediately or when first move out of captured cell. You get it?

[chosk]

Hi Krool,

I manged to get the "customized" behavior I looking for so you need not do anything and can leave the behavior per default to MSFlexGrid.

This is what I did in WindowProcControl:

        Case WM_LBUTTONDOWN
            '======================
            'Disable LButtonDown on Fixed Row
            With HTI
                Pos = GetMessagePos()
                .PT.X = Get_X_lParam(Pos)
                .PT.Y = Get_Y_lParam(Pos)
                ScreenToClient hWnd, .PT
                Call GetHitTestInfo(HTI)
                If .MouseRow = 0 And .MouseCol <> 0 Then
                    VBFlexGridRowSel = VBFlexGridRow
                    VBFlexGridColSel = VBFlexGridCol
                    Exit Function
                End If
            End With
            '=====================
            SetCapture hWnd
            Call ProcessLButtonDown(GetShiftStateFromParam(wParam), Get_X_lParam(lParam), Get_Y_lParam(lParam))

Thanks for the help.

[Krool]

Hi Krool,

I manged to get the "customized" behavior I looking for so you need not do anything and can leave the behavior per default to MSFlexGrid.

This is what I did in WindowProcControl:

        Case WM_LBUTTONDOWN
            '======================
            'Disable LButtonDown on Fixed Row
            With HTI
                Pos = GetMessagePos()
                .PT.X = Get_X_lParam(Pos)
                .PT.Y = Get_Y_lParam(Pos)
                ScreenToClient hWnd, .PT
                Call GetHitTestInfo(HTI)
                If .MouseRow = 0 And .MouseCol <> 0 Then
                    VBFlexGridRowSel = VBFlexGridRow
                    VBFlexGridColSel = VBFlexGridCol
                    Exit Function
                End If
            End With
            '=====================
            SetCapture hWnd
            Call ProcessLButtonDown(GetShiftStateFromParam(wParam), Get_X_lParam(lParam), Get_Y_lParam(lParam))

Thanks for the help.

Small suggestion to you to change little bit: (to be clean)

        Case WM_LBUTTONDOWN
            '======================
            'Disable LButtonDown on Fixed Row
            With HTI
                Pos = GetMessagePos()
                .PT.X = Get_X_lParam(Pos)
                .PT.Y = Get_Y_lParam(Pos)
                ScreenToClient hWnd, .PT
                Call GetHitTestInfo(HTI)
                If .HitRow = 0 And .HitCol <> 0 And .HitResult = FlexHitResultCell Then
                    Me.RowSel = Me.Row
                    Me.ColSel = Me.Col
                    Exit Function
                End If
            End With
            '=====================
            SetCapture hWnd
            Call ProcessLButtonDown(GetShiftStateFromParam(wParam), Get_X_lParam(lParam), Get_Y_lParam(lParam))

Edit:
I could include an 'BeforeMouseDown' event with Cancel parameter? (Similar to like in vsFlexGrid)

[chosk]

I could include an 'BeforeMouseDown' event with Cancel parameter? (Similar to like in vsFlexGrid)

And I can do this to ignore the left mouse button. Great! Then I don't have to remember to insert my code every new update.

Thank you.

Private Sub FlexGrid_BeforeMouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single, Cancel As Boolean)
    With FlexGrid
        If (Button = 1) And (.MouseRow = 0) And (.MouseCol <> 0) Then Cancel = True
    End With
End Sub

[Krool]

Included the BeforeMouseDown event.

@ chosk, I would suggest to you to process the BeforeMouseDown as following:

Private Sub VBFlexGrid1_BeforeMouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single, Cancel As Boolean)
With VBFlexGrid1
If Button = vbLeftButton And .AllowBigSelection = False Then
    .HitTest X, Y
    If .HitResult = FlexHitResultCell Then ' We still allow divider dragging, if applicable
        If .HitRow = 0 And .HitCol > 0 Then Cancel = True
    End If
End If
End With
End Sub

[chosk]

Hi Krool,

Thank you. Working great.

[Krool]

Bugfix in the generic sorting. It is now differentiating between text and numbers. Just like the MSFlexGrid control does.

[MountainMan]

Krool,

When do you plan to release the OCX version? I'd like to try this out in VBA but I have to have the OCX version. I thought about making my own but it seemed like this was relatively short-term for you so I have held off doing that, especially after seeing the hassle that Hosam had trying to make a new OCX out of your CCR controls...

[Krool]

Krool,

When do you plan to release the OCX version? I'd like to try this out in VBA but I have to have the OCX version. I thought about making my own but it seemed like this was relatively short-term for you so I have held off doing that, especially after seeing the hassle that Hosam had trying to make a new OCX out of your CCR controls...

Yes, of course.
I thought to wait until "FlexDataSource" (VirtualMode) property is included.
But I could release VBFLXGRD10.OCX now and include such new features in a next OCX version.

[MountainMan]

If it is going to be 6 months then an OCX release now with a later update would work well. If it is a matter of a few days or weeks then I'll be patient and wait for FlexDataSource (VirtualMode) to be included. thanks for all your work.

[Krool]

If it is going to be 6 months then an OCX release now with a later update would work well. If it is a matter of a few days or weeks then I'll be patient and wait for FlexDataSource (VirtualMode) to be included. thanks for all your work.

I will release an OCX version shortly. There are some nasty pitfalls for VBA compatibility. I did now a few internal improvements to solve some issues. However, there still issue left and I need some time to solve it. So be patient.