VBFlexGrid Control (Replacement of the MSFlexGrid control)

[SamOscarBrown]

Was looking through this thread, but couldn't find an answer to my question, which is, Is there an auto-column-width function built in that would automatically set the columns' width to the longest string in those columns. I have used the MSFlexgrids for quite a while, and your vbFlexGrid for quite a few months now. I could not find such an enhancement in either. I have always set the column width 'by hand' when setting up a grid...

just wondering

Sammi

[Krool]

Was looking through this thread, but couldn't find an answer to my question, which is, Is there an auto-column-width function built in that would automatically set the columns' width to the longest string in those columns. I have used the MSFlexgrids for quite a while, and your vbFlexGrid for quite a few months now. I could not find such an enhancement in either. I have always set the column width 'by hand' when setting up a grid...

just wondering

Sammi

.AutoSize method.

[Krool]

When working with sets of data, there is for example the Recordset.GetRows feature to quickly move all data retrieved from a database into an array. For exporting to Excel there is the Range.Resize function to quickly move the data from an array into a worksheet.

I guess the VBFlexGrid control offers such features as well, but haven't figured it out yet. If it indeed does, can you give me some pointers on how to do that?

Update released.

Included the CopyFromRecordset function which copies the contents of an ADO or DAO Recordset onto the flex grid.
It works like the Excel function which returns the number of fetched records and copies from the current position to EOF.
The number of maximum rows and columns can be specified.
If omitted it is maximum remaining rows and columns that the grid can receive relative to the destination starting point.
The grid will not grow and it just ignores the overflow of rows and columns. (like Excel)
The destination row/col can be specified where the pasting onto the flex grid starts.
If omitted it is the first non-fixed row and first non-fixed column.

VBFlexGrid
Public Function CopyFromRecordset(Data As Object, [ByVal Rows As Long = -1], [ByVal Cols As Long = -1], [ByVal Row As Long = -1], [ByVal Col As Long = -1]) As Long

Excel
Function CopyFromRecordset(Data As Unknown, [MaxRows], [MaxColumns]) As Long

[Krool]

Update released.

Edge case bugfix in the CopyFromRecordset function and minor tweaks. So, it's now hardened on DAO and ADO recordsets.

[vb6nostalgia]

Hello, first of all thank you for the good work with the VBFlexgrid control. Lately I have changed from a grid control to vbflexgrid, I am trying to use the "Standard EXE Version" however when I am debugging VB6 it closes without giving any error. This is happening to me after switching from using the OCX version to the "EXE" version.
I also have a question about how to make the grid work, let's say I have 5 columns, in three of them the user must enter a sku and the name of the item appears in column 2 and in column 3 they must enter a price, and then jump to the next line in column 1, with BeforeEdit I can prevent the user from entering information in that column, but how can I make sure that the columns that are read-only do not accept the focus?

[Calcu]

Hi!

Everything seems to be fine :-), thanks a lot.

My question is:

Could you add the possibility of having a button inside the cell ?, i can do it with a picture, etc..., but just to be like the combo and the checkbox, that you implemented it into the grid and is working so fine :-)

[Krool]

Hi!

Everything seems to be fine :-), thanks a lot.

My question is:

Could you add the possibility of having a button inside the cell ?, i can do it with a picture, etc..., but just to be like the combo and the checkbox, that you implemented it into the grid and is working so fine :-)

There is already combo button (or combo cue button) available.
You can set a picture inside that button.

[Calcu]

but only a button, no a combo with button

[Krool]

but only a button, no a combo with button

Just use a combo cue with button.
Evaluate BeforeEdit event. When Reason <> ComboCue then cancel. Otherwise cancel as well but execute your button click handler routine.

[Calcu]

But doing this way, i can't change the 3 dots inside the button with a text, for example

[Krool]

But doing this way, i can't change the 3 dots inside the button with a text, for example

You can set a button picture. See ColComboButtonPicture or ComboButtonPicture.
Worst case set ComboButtonDrawMode to owner draw.

[vb6nostalgia]

Hi!
I have a question.
Is there a way to define a column that cannot receive focus and also ignore those columns when the user moves with the keyboard from one column to another.
Something similar to how a "fixed column" works but not at the beginning, but between columns.

It turns out that I have tried to have certain columns disabled within the grid,
but so far I have not been able to.
Will there be a way to do it?

[Calcu]

Do you need to not receive focus or it's enough to block the column to not edit it ?

To block it, in the cellbeforeEdit event you can use:

if col = 3 then
    cancel = true
    grid.col=4   ' you can send the focus to any col .
end if

For example.

[vb6nostalgia]

Thanks!
what I need is to not receive focus.

example: On press enter or right arrow key move to next col enabled
Attachment 189242

[Krool]

Thanks!
what I need is to not receive focus.

example: On press enter or right arrow key move to next col enabled
Attachment 189242

Try the BeforeRowColChange event. That's for the caret.
There are also BeforeSelChange event.

[vb6nostalgia]

Try the BeforeRowColChange event. That's for the caret.
There are also BeforeSelChange event.

thank you! I had only tried Before RowColChange, with your indication I understood better how to implement that operation.

that did the job.

Private Sub table1_BeforeRowColChange(ByVal NewRow As Long, ByVal NewCol As Long, Cancel As Boolean)
   If NewCol = 2 Then
      Cancel = True
      table1.Col = 3
   End If
End Sub

Private Sub table1_BeforeSelChange(ByVal NewRowSel As Long, ByVal NewColSel As Long, Cancel As Boolean)
   If NewColSel = 2 Then
      Cancel = True
   End If
End Sub

[softv]

Update released.



Ok, I could now replicate this issue.... checking on the last column brought this up.
It's now fixed. Thanks.

Attachment 189035

Thanks a TON, krool. How great of you to remember even such a small request of mine and give a fix. Always in gratitude.

Have not been able to find focused time to get into this thread for the past few weeks. Hence, the delay in acknowledging.

Have a Divineful Day!

Kind Regards.

[softv]

Update released.

Included the CopyFromRecordset function which copies the contents of an ADO or DAO Recordset onto the flex grid.
It works like the Excel function which returns the number of fetched records and copies from the current position to EOF.
The number of maximum rows and columns can be specified.
If omitted it is maximum remaining rows and columns that the grid can receive relative to the destination starting point.
The grid will not grow and it just ignores the overflow of rows and columns. (like Excel)
The destination row/col can be specified where the pasting onto the flex grid starts.
If omitted it is the first non-fixed row and first non-fixed column.

VBFlexGrid
Public Function CopyFromRecordset(Data As Object, [ByVal Rows As Long = -1], [ByVal Cols As Long = -1], [ByVal Row As Long = -1], [ByVal Col As Long = -1]) As Long

Excel
Function CopyFromRecordset(Data As Unknown, [MaxRows], [MaxColumns]) As Long

Great, krool. Some weeks back, when I needed this requirement, I stumbled upon our great Olaf's solution (cFlexSQLiteDS) here - https://www.vbforums.com/showthread....=1#post5514140 - it was very much for (y)our fabulous VBFlexGrid only. Olaf's readymade solution worked like a charm, as always. Its great now to see this functionality inbuilt in VBFlexGrid itself. I am yet to get time to download the latest VBFlexGrid and see what methodology you have adopted, to accomplish this 'CopyFromRecordSet' functionaltiy. The features you have specified for the CopyFromRecordSet function look wonderful. Thanks a TON. Looking forward to avail them soon, some time in the coming days.

Always remaining in gratitude to the wisdom of you, Olaf, LaVolpe, et al.

Kind Regards.

[Calcu]

Hi all, a new question (is not a problem with the grid itself), it's only to know the best way to do it.

I have a grid with .... 200 lines

I want to "auto select" 50 of them, via clipboard. I mean, i have a list with this 50 items to search at the grid, find and select them.

Actually i'm pasting the list on another grid and with a loop searching for the items on the first grid.

Is there any way to just drag a file (a txt with the items, one on each line) for example and select this? what event must i use fot this ?

Thanks !!!

For any who searchs something similar:

Event: BeforeClipboardAction

If Action = FlexClipboardActionPaste Then
Tcop = Clipboard.GetText  

'do what you need 

Cancel = True
end If

it's perfect.

thanks!

[Calcu]

@Krool do you think it's possible to make groups in vbflexgrid ?

Like this in excel :

(edit: trying to upload a gif in movement)

https://imgur.com/BGursmZ

[Krool]

Update released.

Fixed that no invalidation occurs when setting Rows/Cols to 0. (regression 13-Nov-2023)

Included the ClearRowInfo/ClearColInfo method which clears the info structure for the specified row/column.
An index of -1 will clear the info structure for all rows/columns.

Below code example will reset everything. Thus in some special situations it would not be necessary anymore to set rows/cols to 0 and back to the original value.

VBFlexGrid1.Clear FlexClearEverywhere, FlexClearEverything
VBFlexGrid1.ClearRowInfo -1
VBFlexGrid1.ClearColInfo -1

[Krool]

Update released.

Included the ColCheckBoxesHitTestInvisible/ColCheckBoxesHitTestInvisibleFixed run-time property.

It determines whether check boxes are visible but not hit-testable. (cannot interact with mouse cursor)

This can be useful for representation only check boxes (e.g. FlexTextAsCheckBox) where hot-light is not needed and CellClick (etc.) events should fire.
However, this does not avoid changing the checked state by space key.
To avoid that use the FlexDisabled* checked states or set Cancel = True on the CellBeforeCheck event.

EDIT: Included Optional ByRef DisabledOnly As Boolean parameter in these ColCheckBoxesHitTestInvisible/ColCheckBoxesHitTestInvisibleFixed property.
When set to True and DisabledOnly is also True the effect is that only disabled check boxes are not hit-testable.
When set to False and DisabledOnly is True the effect is inversed and only enabled check boxes are not hit-testable.

The simple mode is of course when set to True (ignore DisabledOnly) all check boxes are not hit-testable.
When set to False all check boxes are hit-testable.

[jdelano]

Appreciate your continued work on this grid, Thank you.

[Krool]

OCX version 1.7 released.

[TheLeePiper]

Loving the control. Great work. One request: can you add (or is it possible to code a workaround) to add a cell border? We are hoping to use the control as an update to a very old grid control (Protoview DataTable) that was very capable as a data entry device (but has a look-and-feel rushing headlong into 1999). This is what we have and would like to do with FG. Thanks!

[PietroV]

Hello Krool,

really thanks for this great control, I hope I can get rid of the MSHflexgrid.

Today I was experimenting for the first time with your OCX 1.7 and stumbled upon a problem.
I have a simple test app where I connect a MSHflexgrid to a recordset populated with data from a MySql DB which I connect through with MySql ODBC driver v5.1.

I have one table that contains integer, datetime and varchar fields only, just a few dozens of records that works well with MSHFlegrid.
The command:

MSHFlexGrid1.DataSource = rsCloud

works without issues and populate the grid.

If I repeat the same command with your grid with exactly the same table:

VBFlexGrid1.DataSource = rsCloud

I got the following error:
Attachment 189713

Do you know what can be the problem ?
Thanks in advance

[MountainMan]

I have a utility/user guide for Krool's vbCCR and vbFlexGrid controls. In addition, it provides a mechanism that you can develop with the .OCX versions (faster & more stable in the IDE) but compiles the controls you actually use into your EXE (smaller and is totally self-contained; don't need to worry about SxS or including/registering the OCX files on your clients' PC's). It is here in VBForums.

[PietroV]

Hello Krool,

really thanks for this great control, I hope I can get rid of the MSHflexgrid.

Today I was experimenting for the first time with your OCX 1.7 and stumbled upon a problem.
I have a simple test app where I connect a MSHflexgrid to a recordset populated with data from a MySql DB which I connect through with MySql ODBC driver v5.1.

I have one table that contains integer, datetime and varchar fields only, just a few dozens of records that works well with MSHFlegrid.
The command:

MSHFlexGrid1.DataSource = rsCloud

works without issues and populate the grid.

If I repeat the same command with your grid with exactly the same table:

VBFlexGrid1.DataSource = rsCloud

I got the following error:
Attachment 189713

Do you know what can be the problem ?
Thanks in advance

Krool,
I have debugged going through the source code of the OCX and noted that for some reason the problem is caused by a TinyInt field:
although the ADODB recordset can handle the field correctly the ADODB Recordset.Getrows() function used to fill the ArrRows array fails to convert it correctly and create the problem.
Converting the field to a smallint solved it.
Thanks again for the great control.
Pietro

[PietroV]

Hello MountainMan,
thanks for the suggestion!
I will study it and maybe, why not, I will use it.
Really many thanks

Pietro

[softv]

Updated released.

Included the CopyFromRecordset function which copies the contents of an ADO or DAO Recordset onto the flex grid.
... .. .
..
.

I heartily wish you a Very Happy New Year, Krool.

Your controls are one of the most invaluable and most powerful ones, which all know. Thanks a TON, forever.

And, the speed at which you are updating the FlexGrid control is mindboggling.

I will write back here once I get time to check out all the recent enhancements you have effected.

Have a Divineful Year!

God Bless you. God Bless all.

Kind Regards.

[Krool]

Update relased.

Included the AllowReaderMode property which enables the support of the reader-mode API for auto-scrolling and panning.

The OCX VBFLXGRD17 was also updated. The internal type lib version is now 1.1.

Object={2DA70529-3366-414A-B408-46083BCD481B}#1.1#0; VBFLXGRD17.OCX

[Krool]

ReaderMode shows now a half blended cursor image indicating the anchor position. (like in WordPad)
It's working as of Windows XP.

[Nouyana]

28-Sep-2023
- Included the BeforeClipboardAction/AfterClipboardAction event.
Enum 'FlexClipboardActionConstants' included to distinguish between Copy, Cut, Paste and Delete.

Hi, Krool!

The BeforeClipboardAction event has empty Text parameter when the Delete action is performed. In my opinion it should contain "clean" values of deleted cells without headers (ClipCopyMode should be ignored). It would be useful for the Undo operation.

Best regards,
Nouyana.

Private Sub VBFlexGrid1_BeforeClipboardAction( _
                    ByVal Action As FlexClipboardActionConstants, _
                          Text As String, _
                          Cancel As Boolean)
   Debug.Print "========================"
   Select Case Action
   Case FlexClipboardActionDelete
      Debug.Print "Before Delete. Text:"
      Debug.Print Text                            ' Text is empty
   End Select
End Sub

Edited:

In my opinion, if the Cut action is about to be performed, the BeforeClipboardAction event should be called twice: one for the Copy and another one for the Delete method. The Text parameter for the Copy method may contain headers (in accordance with ClipCopyMode), but not for the Delete method.

[Calcu]

Hi allí, it's possible to move columns dragging and dropping them ?

I will try to do it this afternoon, but.. I little help will be great xD.

Thanks !

[Krool]

Hi, Krool!

The BeforeClipboardAction event has empty Text parameter when the Delete action is performed. In my opinion it should contain "clean" values of deleted cells without headers (ClipCopyMode should be ignored). It would be useful for the Undo operation.

Best regards,
Nouyana.

Private Sub VBFlexGrid1_BeforeClipboardAction( _
                    ByVal Action As FlexClipboardActionConstants, _
                          Text As String, _
                          Cancel As Boolean)
   Debug.Print "========================"
   Select Case Action
   Case FlexClipboardActionDelete
      Debug.Print "Before Delete. Text:"
      Debug.Print Text                            ' Text is empty
   End Select
End Sub

Edited:

In my opinion, if the Cut action is about to be performed, the BeforeClipboardAction event should be called twice: one for the Copy and another one for the Delete method. The Text parameter for the Copy method may contain headers (in accordance with ClipCopyMode), but not for the Delete method.

Thanks for your valued input.
I thought too about the Cut action. What in your opinion should be the Text for the Cut BeforeClipboardAction?
So the Cut methold would fire first BeforeCut then BeforeCopy/AfterCopy, then BeforeDelete/AfterDelete and then finally AfterCut.

To be on the same page. The Text for the BeforeDelete event should be the content text which is about to be deleted?

[Nouyana]

So the Cut methold would fire first BeforeCut then BeforeCopy/AfterCopy, then BeforeDelete/AfterDelete and then finally AfterCut.

You can't do it just like that if you worry about backward compatibility. Let's say that somebody already use it this way:

Private Sub VBFlexGrid1_BeforeClipboardAction( _
                ByVal Action As FlexClipboardActionConstants, _
                      Text As String, Cancel As Boolean)
   Select Case Action
   Case FlexClipboardActionCopy, FlexClipboardActionCut
      ' Any conversion which could be performed twice:
      Text = Replace$(Text, "A", "AA")
      Text = "Header" & vbCr & Text & vbCr & "Footer"
   End Select
End Sub

If you will fire the BeforeCut and then BeforeCopy, it will cause a double conversion.

What in your opinion should be the Text for the Cut BeforeClipboardAction?

I see only two scenarios.

1. You may just remove the BeforeCut event (the FlexClipboardActionCut constant) and replace it with BeforeCopy and BeforeDelete. Code like the above will work fine, but maybe another code will work wrong.

2. You may add a property which will indicate a behavour: BeforeCut (default) or BeforeCopy+BeforeDelete.

Anyway, the Text in the BeforeCopy or BeforeCut is Ok now.

The Text for the BeforeDelete event should be the content text which is about to be deleted?

Yes. The text should be such that we can simply save it in memory and, if necessary, return it back (UnDo / ReDo) with the Paste method.

[Krool]

You can't do it just like that if you worry about backward compatibility. Let's say that somebody already use it this way:

Private Sub VBFlexGrid1_BeforeClipboardAction( _
                ByVal Action As FlexClipboardActionConstants, _
                      Text As String, Cancel As Boolean)
   Select Case Action
   Case FlexClipboardActionCopy, FlexClipboardActionCut
      ' Any conversion which could be performed twice:
      Text = Replace$(Text, "A", "AA")
      Text = "Header" & vbCr & Text & vbCr & "Footer"
   End Select
End Sub

If you will fire the BeforeCut and then BeforeCopy, it will cause a double conversion.



I see only two scenarios.

1. You may just remove the BeforeCut event (the FlexClipboardActionCut constant) and replace it with BeforeCopy and BeforeDelete. Code like the above will work fine, but maybe another code will work wrong.

2. You may add a property which will indicate a behavour: BeforeCut (default) or BeforeCopy+BeforeDelete.

Anyway, the Text in the BeforeCopy or BeforeCut is Ok now.



Yes. The text should be such that we can simply save it in memory and, if necessary, return it back (UnDo / ReDo) with the Paste method.

I see. I also found out that the .Delete method does not respect the ClipMode property (= ExcludeHidden).

I keep the BeforeCut event like it is. Not overcomplicating things ...
How about keeping the BeforeDelete event with empty Text and do below helper ? (this would solve to bother about BeforeCut)

Private Sub VBFlexGrid1_BeforeClipboardAction(ByVal Action As FlexClipboardActionConstants, Text As String, Cancel As Boolean)
Select Case Action
    Case FlexClipboardActionDelete
        Dim OldClipCopyMode As FlexClipCopyModeConstants
        OldClipCopyMode = VBFlexGrid1.ClipCopyMode
        VBFlexGrid1.ClipCopyMode = FlexClipCopyModeNormal
        Text = VBFlexGrid1.Clip ' Text is already a free provided variable.
        VBFlexGrid1.ClipCopyMode = OldClipCopyMode
        ' Do the rest.
End Select
End Sub

[Krool]

Hi allí, it's possible to move columns dragging and dropping them ?

I will try to do it this afternoon, but.. I little help will be great xD.

Thanks !

The demo project uses a move rows dragging and dropping example. I extended the demo to make the same for the columns.
Just hit that button in the demo project to enable the functionality.

[Nouyana]

I see. I also found out that the .Delete method does not respect the ClipMode property (= ExcludeHidden).

If it will respect the ClipMode property, then the UnDo/ReDo functionality will be more complicated. In this case it will be better to implement undo and redo methods instead. Sounds great for me

How about keeping the BeforeDelete event with empty Text ...?

Why? You can at least make it the same as the others. It will be more obvious.

...and do below helper ? (this would solve to bother about BeforeCut)

Yes, it should work.

[Krool]

Update relased.

FlexClearText for the .Clear method can now be used even when a custom data source is set.

Added FlexClearClip enum (clip range) which the .Delete method now uses instead of FlexClearSelection.

Added FlexClipPasteModeExtended/FlexClipPasteModeExtendedAutoSelection enum.
If ClipPasteMode is >= Extended then FlexClearClip deletes also the extended selection. (opposite action)

The OCX VBFLXGRD17 was also updated. The internal type lib version is now 1.2.

Object={2DA70529-3366-414A-B408-46083BCD481B}#1.2#0; VBFLXGRD17.OCX

EDIT: There is no behavior break to existing code except that .Delete respects now the ClipMode (= ExcludeHidden)
But that I do consider as a bug fix.