VBFlexGrid Control (Replacement of the MSFlexGrid control)

[MountainMan]

Patience is a virtue and I like to think I'm virtuous so I'll wait... Thanks.

[Krool]

ActiveX Control Version released. It can run in the VBA environment from the beginning.

However, there is the VBA mysterious arrow key shift control focus issue.
Due to the fact that IOleInPlaceActiveObject::TranslateAccelerator is not catched by VBA. (same "issue" with the VBCCR ActiveX)

If somebody has an solution please let me know. But at least all can "run" in VBA without crashes.

[darjeeling]

Hi Krool,
A ScaleMode property will be useful.

[Karl77]

What do you need it for?
Just curious.

[Karl77]

FORMATSTRING ISSUE

The FormatString doesn't work for me.
See attached example.
Grid1.zip

Thank you

[Karl77]

REMARKS/QUESTIONS

A)
Can I get the width of the vertical scrollbar?
(Now I get it using GetSystemMetrics - perhaps there is a property in the Grid I don't know of?)

B)
An AutoSizeColumn feature would be highly appreciated.

C)
AutoSizeColumn could also work on a doubleclick between columns.

D)
I think GridLines/GridLinesFixed is mixed, or I get the meaning wrong.

E)
Something like IntegralHeight would be useful.
Now I do: .Height = ((Int(.Height / .CellHeight)) * .CellHeight)

---

Otherwise, the grid works great, it is also quite fast.
A very good replacement for the old MsFlexGrid.


Karl

[Karl77]

SCROLL PROBLEM / RANGE

See attached project.
Grid1b.zip

F)
Click 'Fill'.
Click below the last row.
See the whole grid scrolling to the left.
Not wanted of course...

.Cell(FlexCellLeft, .Rows - 1, .Cols - 1)

Seems to be the cause.

G)
Click 'Make cell range red'.
A single cell becomes red, but not the range.
.Cell(FlexCellBackColor, 1, 0, 3, 3) = vbRed
I understand row #1, col #0 to row #3 to col #3.
But setting the range has no effect.
My misunderstanding or a bug?

Karl

[Krool]

FORMATSTRING ISSUE

The FormatString doesn't work for me.
See attached example.
Grid1.zip

Thank you

It's not a bug, it's a feature.

Reason is because the FormatString needs a FixedRow.
In the VBFlexGrid it is not possible to have fixed rows and no cells.

So, instead of:

Private Sub Command1_Click()

With VBFlexGrid1
    .Clear
    .Rows = 1
    .Cols = 3
    .FixedCols = 1
    '.Redraw = False
End With

VBFlexGrid1.FormatString = Headline

End Sub

do:

Private Sub Command1_Click()

With VBFlexGrid1
    .Clear
    .Rows = 2
    .Cols = 3
    .FixedCols = 1
    .FixedRows = 1
    '.Redraw = False
End With

VBFlexGrid1.FormatString = Headline

End Sub

I added this point in the incompatibilities. (that it is not possible to have fixed rows and no cells)

F)
Click 'Fill'.
Click below the last row.
See the whole grid scrolling to the left.
Not wanted of course...

.Cell(FlexCellLeft, .Rows - 1, .Cols - 1)

Seems to be the cause.

Stupid behavior issue.
The MSFlexGrid does scrolling to current cell. Similar to the .CellEnsureVisible method in the VBFlexGrid.
So in order to be compatible I also do a .CellEnsureVisible when calling .CellLeft. (or indirectly via .Cell)

G)
Click 'Make cell range red'.
A single cell becomes red, but not the range.
.Cell(FlexCellBackColor, 1, 0, 3, 3) = vbRed
I understand row #1, col #0 to row #3 to col #3.
But setting the range has no effect.
My misunderstanding or a bug?

Not a bug. Just make .FillStyle = FlexFillStyleRepeat and then it works.[/QUOTE]

A)
Can I get the width of the vertical scrollbar?
(Now I get it using GetSystemMetrics - perhaps there is a property in the Grid I don't know of?)

Could be included. Have noted .ScrollBarSize like in the SysInfo control.
Or maybe ScrollBarWidth and ScrollBarHeight to have CX and CY values...

B)
An AutoSizeColumn feature would be highly appreciated.

Already noted from your previous posts.

C)
AutoSizeColumn could also work on a doubleclick between columns.

Yes yes, there would be a lot more possible.
First goal was to have a working replacement in place so I can migrate projects.

D)
I think GridLines/GridLinesFixed is mixed, or I get the meaning wrong.

GridLines is for the cells and GridLinesFixed for the fixed cells. I see no problem there. Can you explain more?

E)
Something like IntegralHeight would be useful.
Now I do: .Height = ((Int(.Height / .CellHeight)) * .CellHeight)

Yes your manual way works as the heights of the cells are equal.
But how to manage integral height when all the cell heights are different?

it is also quite fast.

That was my priority from the beginning. To have it efficient and fast. At least not slower than MSFlexGrid.

[Krool]

double post removed

[Karl77]

Stupid behavior issue.
The MSFlexGrid does scrolling to current cell. Similar to the .CellEnsureVisible method in the VBFlexGrid.
So in order to be compatible I also do a .CellEnsureVisible when calling .CellLeft. (or indirectly via .Cell)

Hmm, not so cool.
I thought the .Cell feature advantage is that we don't have to change the selection in order to get or set cell properties.
So I would expect .Cell doesn't do stupid things.
We don't have such a comfy .Cell thing in the original.
Seeing this, can't we avoid stupidity?

Could be included. Have noted .ScrollBarSize like in the SysInfo control.
Or maybe ScrollBarWidth and ScrollBarHeight to have CX and CY values...

I didn't think of the SysInfo control.
That's enough, no need for such a property.

Already noted from your previous posts.

Sorry for the repeat, I should have known you don't forget.

First goal was to have a working replacement in place so I can migrate projects.

Up to now there are only minor obstacles.
And easy to deal with with your assistance.

Yes your manual way works as the heights of the cells are equal.
But how to manage integral height when all the cell heights are different?

Yes, you are right.
So forget it, to do it manually (with the same cell height) is not that much effort and good enough.

[Karl77]

CELL FONT

When I do this:

With VBFlexGrid1
.Cell(FlexCellFontName, 1, 0) = "Marlett"
.Cell(FlexCellText, 1, 0) = "a"
Debug.Print "Font: ", .Cell(FlexCellFontName, 1, 0)
End With

The cell seems to have got the font.

It should show a check mark.
Instead it shows "a".

What do I do wrong?

[Krool]

Hmm, not so cool.
I thought the .Cell feature advantage is that we don't have to change the selection in order to get or set cell properties.
So I would expect .Cell doesn't do stupid things.
We don't have such a comfy .Cell thing in the original.
Seeing this, can't we avoid stupidity?

The (obvious) reason why .CellLeft is scrolling the focused cell into view is that .CellLeft is used to place a TextBox or ProgressBar (or whatever) control in this cell rectangle.
And of course that makes only sense when the cell is in view (visible)
In this context it actually does make sense to keep this logic in .Cell.
Because what makes it a difference when I want to put a TextBox (or whatever) control on the focused cell (.CellLeft) or an arbitrary cell (.Cell)?
What you are looking for is the current extent location of the cell, even if it is far beyond the current client edge.
This you could calculate by a workaround to sum up the Width's of the fixed columns and from the .LeftCol to your actual cell column.
Than you have your .Left you need.

[Karl77]

What you are looking for is the current extent location of the cell, even if it is far beyond the current client edge.
This you could calculate by a workaround to sum up the Width's of the fixed columns and from the .LeftCol to your actual cell column.

What I really want to detect is if I clicked outside a cell.
The dark grey area:


The Width sum works fine for the X direction.
For Y I need the position of the cell.
And when I ask for the Top, the same effect kicks in.
When I sum up the cell heights, I don't have the correct position.

Or is it all too complicated what I want, and there is a better method that I don't see?

[Krool]

What I really want to detect is if I clicked outside a cell.
The dark grey area:


The Width sum works fine for the X direction.
For Y I need the position of the cell.
And when I ask for the Top, the same effect kicks in.
When I sum up the cell heights, I don't have the correct position.

Or is it all too complicated what I want, and there is a better method that I don't see?

What about the .HitTest method and check .HitResult = FlexHitResultNoWhere? (Not available in MSFlexGrid, but here in VBFlexGrid.

[Karl77]

What about the .HitTest method and check .HitResult = FlexHitResultNoWhere? (Not available in MSFlexGrid, but here in VBFlexGrid.

Excellent!

Thank you.

[Karl77]

CELL FONT

When I do this:

With VBFlexGrid1
.Cell(FlexCellFontName, 1, 0) = "Marlett"
.Cell(FlexCellText, 1, 0) = "a"
Debug.Print "Font: ", .Cell(FlexCellFontName, 1, 0)
End With

The cell seems to have got the font.

It should show a check mark.
Instead it shows "a".

What do I do wrong?

I also tried WingDings - no success.
When I set WingDings for the whole VBFlexGrid, it works.
But not for a single cell.
Other fonts, e.g. Times New Roman or Courier New do work ok.

[Krool]

When I do this:

With VBFlexGrid1
.Cell(FlexCellFontName, 1, 0) = "Marlett"
.Cell(FlexCellText, 1, 0) = "a"
Debug.Print "Font: ", .Cell(FlexCellFontName, 1, 0)
End With

The cell seems to have got the font.

It should show a check mark.
Instead it shows "a".

What do I do wrong?

Ok, here comes an additional issue in place. In VBFlexGrid is a CellFontCharset property available, whereas in MSFlexGrid this is not available.

Solution for your problem would be to change not only the FontName but also the FontCharset, like following:

Const SYMBOL_CHARSET As Long = 2
With VBFlexGrid1
.Cell(FlexCellText, 1, 0) = "a"
.Cell(FlexCellFontName, 1, 0) = "Marlett"
.Cell(FlexCellFontCharset, 1, 0) = SYMBOL_CHARSET
Debug.Print "Font: ", .Cell(FlexCellFontName, 1, 0)
End With

Possible bugfix would be that the FontCharset will be overwritten automatically when changing the FontName and not that later the FontCharset breaks up the FontName.

[MountainMan]

Krool,

if the control uses Unicode (actually UCS-2) then of what value is it to have FontCharset?

[Krool]

Update released.

FontCharset issue resolved. Whenever the FontName is changed the FontCharset will be amended as well by default. Thus always working.
However, afterwards the FontCharset can be changed manually, if necessary.

Second fix is actually more important. By update on 23-Nov-2017 (VBA support) it caused an huge performance drop. Reason was that in VBA another extra refresh is necessary. However, this caused the control in VB6 to make the refresh always immediately and resulted finally in 2x draws.
This fix should improve the performance of the control a lot. A big lot.

[Karl77]

FontCharset issue resolved.

Great, works as expected now.

This fix should improve the performance of the control a lot. A big lot.

It was quite fast before the fix, and now even better.

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

You asked in #46.
I couldn't find issues with it, it works the same as in MSFlexgrid (except the 'no cells issue', but no problem at all).

[Krool]

Update released.

The IDE (does not effect compiled exe) will not crash anymore when using VBCCRxx.OCX and VBFLXGRDxx.OCX in paralell. (Std-EXE version not concerned)

[immortalx]

Krool, thank you very much for this absolutely amazing control!
I have a weird issue with mouse wheel scrolling. I thought it wasn't supported at all, until i tried to swipe the wheel really hard!
To best describe the issue, it only works when you make a single fast full 360 rotation of the wheel. Anything less than a full rotation (no matter how fast) has no effect.

[Karl77]

To best describe the issue, it only works when you make a single fast full 360 rotation of the wheel. Anything less than a full rotation (no matter how fast) has no effect.

I use the control because it supports the mouse wheel, and MSFLEXGRID not.
There are other reasons, too.
In short, scrolling works perfect in vertical direction.

Immortal, can you post a small sample project, so that we can see the error?

Karl

[immortalx]

Thanks Karl, that's why I want to use it as well and avoid the hook methods.
I've attached a sample for the ActiveX version. I also tried the included example in the Std-EXE version and it has the same effect.
Also, there's no difference whether it runs in the IDE or compiled.

[chosk]

Running your sample, I have no problem with mousewheel scrolling.

[Karl77]

Thanks Karl, that's why I want to use it as well and avoid the hook methods.
I've attached a sample for the ActiveX version. I also tried the included example in the Std-EXE version and it has the same effect.
Also, there's no difference whether it runs in the IDE or compiled.

Same as chosk says: No problem with your sample.

[immortalx]

chosk & karl, thanks for testing it guys.
That's odd. I installed VB on my work PC and it works perfectly. At home I use a Microsoft wireless mouse that's been rock solid for years and works with every application without a hitch. It also works with other controls that support the mousewheel, and within the IDE itself with the mousewheelfix. I'll try my work mouse at home and will report back.
Again, thank you very much for the help!

[Schmidt]

...At home I use a Microsoft wireless mouse ...

Bought the same thing about 3 years ago - and encountered the same "issues"...
in short - the MS-MouseDrivers are more "fine-grained" and require an accumulation
of the incoming (smaller) Rotation-delta-values.

@Krool (maybe that gives you some ideas)
Below is a little Helper-Function I had to write for the RC5-Form- and -WidgetEngine
(which is doing the accumulation in a Static-Variable at function-level):

Public Function GetMouseWheelLineOffset(ByVal MouseKeys As Long, ByVal Rotation As Long) As Long
  Static Delta As Long, LastRotation As Long
  If Sgn(Rotation) <> Sgn(LastRotation) Then Delta = 0
  Delta = Delta + Rotation
  If Abs(Delta) >= 120 Then
    GetMouseWheelLineOffset = IIf(MouseKeys And &H1000, Delta \ 120, -Delta \ 120)
    Delta = Delta - (Delta \ 120) * 120
  End If
  LastRotation = Rotation
End Function

Olaf

[immortalx]

Schmidt, it seems you're absolutely right. I just tried my work mouse and works as expected. However, I just read a Microsoft document describing that some applications misinterpret partial (30) events as full-line (120) or completely ignore them.

[Krool]

@Krool (maybe that gives you some ideas)
Below is a little Helper-Function I had to write for the RC5-Form- and -WidgetEngine
(which is doing the accumulation in a Static-Variable at function-level):

Public Function GetMouseWheelLineOffset(ByVal MouseKeys As Long, ByVal Rotation As Long) As Long
  Static Delta As Long, LastRotation As Long
  If Sgn(Rotation) <> Sgn(LastRotation) Then Delta = 0
  Delta = Delta + Rotation
  If Abs(Delta) >= 120 Then
    GetMouseWheelLineOffset = IIf(MouseKeys And &H1000, Delta \ 120, -Delta \ 120)
    Delta = Delta - (Delta \ 120) * 120
  End If
  LastRotation = Rotation
End Function

Thanks.
Before I put an update out I want to be sure if it works as I cannot test this. (I do not have a mouse for testing that has a finer-resolution wheel)

Before:

Case WM_MOUSEWHEEL
    If VBFlexGridWheelScrollLines > 0 Then
        Dim WheelDelta As Long, WheelDeltaPerLine As Long
        WheelDelta = HiWord(wParam)
        If Abs(WheelDelta) >= 120 Then
            WheelDeltaPerLine = (WheelDelta / VBFlexGridWheelScrollLines)
            If Sgn(WheelDelta) = -1 Then
                While WheelDelta <= WheelDeltaPerLine
                    SendMessage hWnd, WM_VSCROLL, MakeDWord(SB_LINEDOWN, 0), ByVal 0&
                    WheelDelta = WheelDelta - WheelDeltaPerLine
                Wend
            Else
                While WheelDelta >= WheelDeltaPerLine
                    SendMessage hWnd, WM_VSCROLL, MakeDWord(SB_LINEUP, 0), ByVal 0&
                    WheelDelta = WheelDelta - WheelDeltaPerLine
                Wend
            End If
        End If
        WindowProcControl = 0
        Exit Function
    End If

After: (suggestion)

Case WM_MOUSEWHEEL
    If VBFlexGridWheelScrollLines > 0 Then
        Static WheelDelta As Long, LastWheelDelta As Long
        If Sgn(HiWord(wParam)) <> Sgn(LastWheelDelta) Then WheelDelta = 0
        WheelDelta = WheelDelta + HiWord(wParam)
        If Abs(WheelDelta) >= 120 Then
            Dim WheelDeltaPerLine As Long
            WheelDeltaPerLine = (WheelDelta / VBFlexGridWheelScrollLines)
            If Sgn(WheelDelta) = -1 Then
                While WheelDelta <= WheelDeltaPerLine
                    SendMessage hWnd, WM_VSCROLL, MakeDWord(SB_LINEDOWN, 0), ByVal 0&
                    WheelDelta = WheelDelta - WheelDeltaPerLine
                Wend
            Else
                While WheelDelta >= WheelDeltaPerLine
                    SendMessage hWnd, WM_VSCROLL, MakeDWord(SB_LINEUP, 0), ByVal 0&
                    WheelDelta = WheelDelta - WheelDeltaPerLine
                Wend
            End If
        End If
        LastWheelDelta = HiWord(wParam)
        WindowProcControl = 0
        Exit Function
    End If

Can you please confirm? Thanks again for your help.

[DEXWERX]

Schmidt, it seems you're absolutely right. I just tried my work mouse and works as expected. However, I just read a Microsoft document describing that some applications misinterpret partial (30) events as full-line (120) or completely ignore them.

I believe this is because even the original MSDN examples didn't correctly account for partial scrolls, or bother retrieving the global setting, and the rest of the internet has copied the error for decades.

another thing people never acount for is that if you retrieve a -1 from the global scroll size setting, that means to scroll by 1 page. Once upon a time, on another far away site, i posted a proper example for scrolling a flexgrid.

here's essentially how i handled it back then.

        Case WM_MOUSEWHEEL
            FlexWheelScroll m_Grid, CSng(GET_WHEEL_DELTA_WPARAM(wParam)) / WHEEL_DELTA
            Exit Function

Private Function FlexWheelScroll(Grid As MSHFlexGrid, ByVal Rotation As Single) As Long
    Const SPI_GETWHEELSCROLLLINES As Long = &H68
    Static ScrollLines As Long
    If ScrollLines = 0 Then SystemParametersInfoW SPI_GETWHEELSCROLLLINES, 0, ScrollLines, 0
    Dim StepSize As Long
    If ScrollLines < 0 Then
        StepSize = FlexPageSize(Grid, True)
    Else
        StepSize = ScrollLines
    End If
    If Grid.Rows - Grid.FixedRows = 0 Then Exit Function
    Dim NewRow As Long
    NewRow = Grid.TopRow - Rotation * StepSize
    Dim MaxRow As Long
    MaxRow = Grid.Rows - FlexPageSize(Grid, True)
    If NewRow > MaxRow Then
        Grid.TopRow = MaxRow
    ElseIf NewRow < Grid.FixedRows Then
        Grid.TopRow = Grid.FixedRows
    Else
        Grid.TopRow = NewRow
    End If
End Function

although looking at it now - using a single probably wouldn't really work. You probably have to accumulate rotation as Schmidt has shown.

[immortalx]

@Krool I confirm it works perfectly!

@DEXWERKS that's some very useful info.

[Karl77]

While we are at the scrolling.

We can scroll vertical only, which is ok so far.
Recently I discovered that in Paint.NET, when the mouse is over a horizontal scrollbar, the wheel acts this scrollbar.
Perhaps a nice addition to the VBFlexGrid, and for VBCCR as well.

---
Some years ago I had such a MS mouse, and I thought it is broken.
I bought another one...

[Krool]

Updated released for supporting fine-grained wheel changes. Thanks to Schmidt for pointing this out.

[Krool]

An AutoSizeColumn feature would be highly appreciated.

I want to start working on such a functionality. But I want to put out a question in the room for some feedback.

Similar like in the ListView I want to be able to auto size in two ways. 'ToItems' and 'ToHeader'
So which font should be considered by the auto size method?

'ToItems' it should consider of course the .Font property. But what about the .CellFont ? Ignore or also consider such exceptions.

'ToHeader' it should consider the .FontFixed (if applied) and else the .Font property. But again what about .CellFont exceptions?

Thanks

[Karl77]

I want to start working on such a functionality. But I want to put out a question in the room for some feedback.

Similar like in the ListView I want to be able to auto size in two ways. 'ToItems' and 'ToHeader'
So which font should be considered by the auto size method?

'ToItems' it should consider of course the .Font property. But what about the .CellFont ? Ignore or also consider such exceptions.

'ToHeader' it should consider the .FontFixed (if applied) and else the .Font property. But again what about .CellFont exceptions?

Thanks

Oh sorry, I saw this very late.
The .CellFont should be in the calculation.
(While I use only the same font for all cells.)

Karl

[Krool]

Update released.

Included the TextWidth/TextHeight function that calculates for the current or an arbitrary cell the width and height length.
For multiline text the heights are cumulated and for the width the line with the greatest width is taken. Similar to VB.Form's TextWidth function.

Example:

Takes current cell font. However text must be passed in any case.

MsgBox VBFlexGrid1.TextWidth("abc" & vbLf & "abcdef")

Takes cell matrix 2,2.

MsgBox VBFlexGrid1.TextWidth("abc" & vbLf & "abcdef", 2, 2)

If you want to get text for current cell and text of that cell just do:

MsgBox VBFlexGrid1.TextWidth(VBFlexGrid1.Text)

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.

Now instead using a label control control you can use the new TextWidth function.
However, an handy autosize functionality will follow soon. But this for now enables manual handling of such things and is very general for many scenarios.

[Karl77]

Now instead using a label control control you can use the new TextWidth function.
However, an handy autosize functionality will follow soon. But this for now enables manual handling of such things and is very general for many scenarios.

Thank you, good to know.

For me, it makes no sense to switch over to the new functionality.
The new TextWidth function requires more effort than just using a label, I would have to track the maximum width.
With a label, this is automatic (AutoSize).

So I'll wait for what will follow...

[Krool]

Included the AutoSize method.

.AutoSize(RowOrCol1 As Long, [RowOrCol2 As Long = -1], [Mode As FlexAutoSizeModeConstants], [Scope As FlexAutoSizeScopeConstants], [Equal As Boolean], [ExtraSpace As Long])

If RowOrCol2 is omitted only RowOrCol1 is processed, else the range between RowOrCol1 and RowOrCol2 is processed.
The Mode argument specifies if column widths or row heights are adjusted.
The Scope argument indicates if all cells, only fixed cells or only scrollable cells are evaluated.
Equal argument, if True, the range between RowOrCol1 and RowOrCol2 are set to the same sizes.
ExtraSpace argument allows to specify extra spacing, in twips, which can be useful when picture dimensions need to be taken into consideration.

[immortalx]

Krool I noticed that properties RowIsVisible, ColIsVisible return always true, no matter if the respective Row or Col are hidden or not.
Or maybe I'm doing something wrong?