VBFlexGrid Control (Replacement of the MSFlexGrid control)

Post #1 in this thread has the built-in code for the VB6 control as shown in the zip file (built-in controls ahe an extension of .ctl. When you put a .ctl file in your code and then compile, the final .exe file will actually have the control included in the exe file and there is no additional file (such as an .ocx) that needs to go along with it. Using the .ctl file in your code makes your final product not have any dependencies but the downside is that each time you compile your program you also have a bit of extra time required to compile the .ctl file into your code. The alternative is to use an.ocx file (it is just the same as the .ctl file but it has been pre-compiled). Your compilations go a bit faster because the control is already compiled but the drawback is that the .ocx file has to accompany your .exe if/when you distribute it.

Look again at Krools first post. Ib abut the 9th line you will see:

ActiveX Control version, together with a Registration-Free (Side-by-side) solution:
Version 1.4

The link for Version 1.4 is the thread that contains the latest ocx file if that' what you want to use.

BTW, in the parent thread about Common Control replacement there is a link in Krool's first post to a utility I wrote that enables you to use the .ocx file for development and then switch to the .ctl file at the end to have a final file that has no .ocx dependency. I hope that will be of benefit to you (it works with the flexgrid control as well as the larger one).

Update released.

Major speed improvement for drawing custom cell background colors.

In the past the custom cell background was drawn via FillRect and by creating a temporary Brush.

Code:

---

Brush = CreateSolidBrush(WinColor(.BackColor))
If Brush <> 0 Then
    FillRect hDC, CellRect, Brush
    DeleteObject Brush
End If

---

However, the new approach is at least 4 times(!) faster.

Code:

---

OldBkColor = SetBkColor(hDC, WinColor(.BackColor))
ExtTextOut hDC, 0, 0, ETO_OPAQUE, CellRect, 0, 0, 0
SetBkColor hDC, OldBkColor

---

any chance this flexgrid have footer bar (for total lines) and request for filter bar below header so user can input like Excel. perhaps...

really appreciated this amazing control

Update released.

Massive performance boost for Get .Clip property. (the larger the range the bigger the performance difference)

The string concatenation was done previously by using small buffer chunks putting together when the buffer chunks reach a certain size.

However, compared to the VB6 poor man's string builder from wqweto this is light years of speed difference.

Just as a hint. A very large grid took me before 16 seconds to get the .Clip string. Now it takes just 1 full second.

EDIT: using now a string array instead of a collection for the poor man's string builder for some extra speed gain.

is it possible add to SelectionMode it should behave like a ListBox (FlexSelectionModeListBox)

thanks

Quote:

---

Originally Posted by Semke

is it possible add to SelectionMode it should behave like a ListBox (FlexSelectionModeListBox)

---

Yes, I thought of something like this also. (kind of multi-selection)
Thanks for bringing this back up.

The vsFlexGrid does have such a feature, right ?

So you don't determine the selection via Row and RowSel but rather via a dedicated IsSelected(Row As Long) property.

How does it behaves there? I mean a ListBox can be set to different selection styles also.
Also is the focus rect "free" or aligned like on "ByRow" ? What do .RowSel report? Is it returning always same as .Row or a "disabled alias" of -1 ?

Maybe I shall make a SelectioMode "ListBox" (similar to "ByRow") and "FreeListBox" (similar to "FreeByRow")
Just thoughts..

I made a demonstration of multiple selections, supporting mouse selection, ctrl+mouse, Shift+mouse selection.

All demo codes are included in sub VBFlexGrid1_MouseDown VBFlexGrid1_MouseUp Sub RowBackColor Sub RowForeColor。

Attachment 177513

Attachment 177515

Quote:

---

Originally Posted by ChenLin

I made a demonstration of multiple selections, supporting mouse selection, ctrl+mouse, Shift+mouse selection.

All demo codes are included in sub VBFlexGrid1_MouseDown VBFlexGrid1_MouseUp Sub RowBackColor Sub RowForeColor。

Attachment 177513

Attachment 177515

---

Please just post code snippets and not entire duplicate of the demo project. In 1 year or so it may be outdated and can cause confusion.

My project has a large grid with input and calculation data, cells with different colors and pictures. When user changes the data of one cell, the content and appearance of other cells changes accordingly. So every time I have to set redraw=false, fill cells with values ​​and color, and then set redraw=true. This works fine with MSHFLXGD even if I completely clear the grid structure. Instantly and without flickering.

However, to support Unicode and DPI-aware, I switched to VBFLXGRD14. And now, at the time of setting redraw=true there is a flicker (like a flash of the backcolor). The grid disappears for milliseconds and appears again. The larger the table, the longer the flash.

What can be done? I don't want to return to MSHFLXGD. Maybe some speccial API call (not LockWindowUpdate)? Or can I hope for VBFLXGRD update without this issue?

Quote:

---

Originally Posted by tnrprog

My project has a large grid with input and calculation data, cells with different colors and pictures. When user changes the data of one cell, the content and appearance of other cells changes accordingly. So every time I have to set redraw=false, fill cells with values ​​and color, and then set redraw=true. This works fine with MSHFLXGD even if I completely clear the grid structure. Instantly and without flickering.

However, to support Unicode and DPI-aware, I switched to VBFLXGRD14. And now, at the time of setting redraw=true there is a flicker (like a flash of the backcolor). The grid disappears for milliseconds and appears again. The larger the table, the longer the flash.

What can be done? I don't want to return to MSHFLXGD. Maybe some speccial API call (not LockWindowUpdate)? Or can I hope for VBFLXGRD update without this issue?

---

Just a question at first: Do you have .DoubleBuffer set to True ?

Quote:

---

Originally Posted by Krool

Just a question at first: Do you have .DoubleBuffer set to True ?

---

I tried several options with .DoubleBuffer=True/False, .VisualStyles=True/False etc. The result is the same

@ tnrprog,
Can you put together a small demo showing your issue? So I have a better picture and don't fix something in the dark.

Quote:

---

Originally Posted by Krool

@ tnrprog,
Can you put together a small demo showing your issue? So I have a better picture and don't fix something in the dark.

---

My attempt to write a demo that demonstrates an issue has failed. No repaint issue occurred in the demo application, and your control shows amazing rendering quality.
Perhaps there is a tricky API conflict in my project. Something updates the window forcibly. I will let you know if I find anything.

So far my problem has been resolved by LockWindowUpdate().

Hi Krool,

I'm new to the FlexGrid control and looking for an example how to use it. I'm assuming at this point that column widths and titles have to be set via code. There is a property page for global settings but nothing to control each column wdith.

I tried to run the .exe example that came in the download but I get the following error when I try to run it in the IDE. Here is the line it stops on.

Private Sub IOleInPlaceActiveObjectVB_TranslateAccelerator(ByRef Handled As Boolean, ByRef RetVal As Long, ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long, ByVal Shift As Long

Attachment 177999

Quote:

---

Originally Posted by ScriptBASIC

Hi Krool,

I'm new to the FlexGrid control and looking for an example how to use it. I'm assuming at this point that column widths and titles have to be set via code. There is a property page for global settings but nothing to control each column wdith.

I tried to run the .exe example that came in the download but I get the following error when I try to run it in the IDE. Here is the line it stops on.

Private Sub IOleInPlaceActiveObjectVB_TranslateAccelerator(ByRef Handled As Boolean, ByRef RetVal As Long, ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long, ByVal Shift As Long

Attachment 177999

---

You have an outdated OLEGuids.tlb.
Just replace on syswow64 (or system32 on 32bit OS) with the new one in the download.

Thanks Krool!

That solved the problem.

Attachment 178006


More good news. Updating OLEGuids.tlb now allows me to use VBCCR16 :thumb:

Quote:

---

Originally Posted by ScriptBASIC

More good news. Updating OLEGuids.tlb now allows me to use VBCCR16 :thumb:

---

Normally the OLEGuids.tlb is not needed for a compiled VBCCR16.

Hello, I am new with using VBFlexGrid. I would like to select multiple cells with highlightin. Is tis possible? Are there any code snippets, how to do this, maybe? Thanks!

Quote:

---

Originally Posted by danoe

Hello, I am new with using VBFlexGrid. I would like to select multiple cells with highlightin. Is tis possible? Are there any code snippets, how to do this, maybe? Thanks!

---

Not yet in-built possible. The plan is to include in future a SelectionMode enum "ListBox" which allows multiple selection (but per full row). (and helper property .RowSelected(Index))
If you want a workaround mimic multi highlighting just search in google for such feature with "MSFlexGrid". You could apply likewise that code to the VBFlexGrid then.

@Krool:

How to get the column index value according to the column heading, and not affected by drag and drop.

Quote:

---

Originally Posted by ChenLin

@Krool:

How to get the column index value according to the column heading, and not affected by drag and drop.

---

When you populate your grid use .ColKey to define the column headings as keys.
After you messed up the grid with drag drop you can use .ColIndex(Key) to get the related column index.

@krool: thanks for your reply. Is it possible to handle the arrow down key to select a cell in the line below the current selected cell? Currently it is working with arrow right, left and up keys.

Quote:

---

Originally Posted by danoe

@krool: thanks for your reply. Is it possible to handle the arrow down key to select a cell in the line below the current selected cell? Currently it is working with arrow right, left and up keys.

---

Don't know what you mean. Please be as precise as possible. For me the arrow down key works as expected.

Quote:

---

Originally Posted by Krool

Don't know what you mean. Please be as precise as possible. For me the arrow down key works as expected.

---

Sorry. I have an Userform with an VBFlexGrid item and a button. If there is an active button in the userform, if I am going the use key down, it sets the focus to the button. If there isn't a button, it works like you described. The selection mode is FlexSelectionModeFree.

https://www.vbforums.com/images/ieimages/2020/08/1.png

Quote:

---

Originally Posted by danoe

Sorry. I have an Userform with an VBFlexGrid item and a button. If there is an active button in the userform, if I am going the use key down, it sets the focus to the button. If there isn't a button, it works like you described. The selection mode is FlexSelectionModeFree.

https://www.vbforums.com/images/ieimages/2020/08/1.png

---

Yes it's a known issue. Contrary to a VB.Form the VBA.UserForm doesn't implement IOleInPlaceActiveObject. That's why it doesn't get the "pre" accelerator keys.

EDIT:
I successfully "workarounded" the VBA issue with a WH_GETMESSAGE hook and a helper message (RWM_PRETRANSLATEMSG).
I need some time to put this clean together and will soon release an update.

Update, the OCX supports now accelerator keys in a VBA environment.

https://www.vbforums.com/images/ieimages/2020/08/1.gif

In order to replace to the new version of the OCX it is strongly recommended to delete certain cache files for MS Office:

Code:

---

C:\Users\<username>\AppData\Local\Temp\VBE\VBFLXGRD14.exd

---

Code:

---

C:\Users\<username>\Application Data\Microsoft\Forms\VBFLXGRD14.exd

---

Those cache files do only exist when previously loaded VBFLXGRD14 into the toolbox on a UserForm.
Then replace the new VBFLXGRD14.OCX in the system directory..

The new conditional compilation const 'ImplementPreTranslateMsg' is set to True in the OCX project and False in the Std-EXE project.
If True (OCX only) the PreTranslateMsg mechanism is used only if the host does not support IOleInPlaceActiveObject.
So the OCX will work unmodified in a VB6 environment like before.
The extension is only used in a VBA environment.

As the OCX can not own or access the message bump in a VBA environment via IOleInPlaceActiveObject, there is no other choice than using a WH_GETMESSAGE hook.
That hook procedure is considered as the "PreTranslateMessage" function. (like c++ mfc)

Also (as side-effect) the WantReturn will work when setting to True. (eating the return key)
This is crucial for the DirectionAfterReturn property to work also. (when it is defined to something other than 0 - None)

Quote:

---

Originally Posted by Krool

When you populate your grid use .ColKey to define the column headings as keys.
After you messed up the grid with drag drop you can use .ColIndex(Key) to get the related column index.

---

Thank you @Krool, I used the following method before. I don't know which of the two methods is better.

Code:

---

Public Function GetColIDVB(Sgrd As VBFlexGrid, ByVal ColCaption As String)
    Dim i As Integer
    On Error GoTo CErr
    For i = 0 To Sgrd.Cols - 1
        If Sgrd.TextMatrix(0, i) = ColCaption Then GetColIDVB = i
    Next i
    Exit Function
CErr:    ' MsgBox Err.Description & i
End Function

---

I have to maintain an old application that uses DAO recordset (using vsFlexGrid DAO version). I want to change with VBFlexGrid to support unicode. Application is too large to move to ADO.
Can be implemented a property DAODataSource?

Quote:

---

Originally Posted by cliv

I have to maintain an old application that uses DAO recordset (using vsFlexGrid DAO version). I want to change with VBFlexGrid to support unicode. Application is too large to move to ADO.
Can be implemented a property DAODataSource?

---

The best is to use the VBFlexGrid in a "virtual" way.
Means using the .FlexDataSource property. Schmidt provided a sample project somewhere showing how to use it for a SQLite Recordset.
It would be kind of easy to swap it with a DAO recordset.
Also performance and memory wise it is better - the data is loaded only once and not shovel over again from the recordset to the grid.

Quote:

---

Originally Posted by Krool

Schmidt provided a sample project somewhere showing how to use it for a SQLite Recordset..

---

found here:
https://www.vbforums.com/showthread....te-Recordsets)

thank you...and by the way ForeColorFixed not working.

Quote:

---

Originally Posted by cliv

and by the way ForeColorFixed not working.

---

Thanks. Fixed!

In the internal DrawFixedCell method was a stupid bug due to a change.

Code:

---

If Not .ForeColor = -1 Then
    OldTextColor = SetTextColor(hDC, WinColor(PropForeColorFixed))
Else
    OldTextColor = SetTextColor(hDC, WinColor(.ForeColor))
End If

---

I must have swapped the content of the two branches (for performance) as it is more likely to have no defined custom cell fore color.
And by the swapping I forgot to remove the "Not" to reverse the logic.

In the likewise internal DrawCell method the "Not" got removed..

However, now ForeColorFixed property works now again as expected.

Dear Krool,


Once again, thanks a million for your work. For ever. Mighty helpful for my freeware and thus the society which avails it freely.


Well, this message of mine is regarding a common issue in both the VBFlexGrid and the RichTextBox of yours. In both cases, I have worked only with the "Std-EXE" version so far. I presume this same issue would exist with the ocx version too.


Two days back, I was displaying all the characters from "Unifont Upper" font in VBFlexGrid. I was displaying each character in a separate cell. Initially, it looked like all characters were getting displayed correctly in their respective cells. But, on closer scrutiny, I found out that for some random ranges of Unicode points, the characters were not getting displayed. Only square boxes were getting printed. For instances:


1)
69216 to 69246
69216 is U+10E60 RUMI DIGIT ONE
69246 is U+10E7E RUMI FRACTION TWO THIRDS


2)
129280 to 129291
(129280 is U+1F900 CIRCLED CROSS FORMED WITH FOUR DOTS)
129291 is U+1F90B DOWNWARD FACING NOTCHED HOOK WITH DOT


Now, for the above ranges, if I copy/paste the characters from VBFlexGrid to MSWord, the characters get displayed correctly in "UniFont Upper". This is to assure you that I did form the surrogate pairs properly for the non-displaying characters too.


So, what really is happening? Is some kind of font-fallback happening for some code points and hence the grid is showing boxes for them?. Can you kindly enlighten me on this?


Actually, I have seen this same issue with the RichTextBox (of VBCCR std-exe version, I mean) too, 2 or 3 years back itself, and it still persists in Windows7. This issue is not present in Windows10 though; mighty happy about it. Well, the issue in Windows 7 is - if I gather all the characters from a particular Unicode font (e.g. Siddhanta) in a string array (say 's'), and finally set the string returned by a JOIN of 's' to RichTextBox1.text, then, for some ranges of code points alone (for e.g. 7376 to 7409), only boxes would get printed (in Windows7). The only workaround I could get to solve this issue at that point of time (and even now, in Windows7) was/is the following.


With RichTextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SelFontCharset = 0
.SelFontName = .Font.Name
.SelStart = 0
End With


But, doing the above will take a long time to finish for fonts like 'Arial Unicode MS' which have more than 38K characters. Sometimes, it would put the application in 'Not responding' mode too.


I thought the VBFlexGrid would solve the above problem in both Windows7 and Windows10 but I see that VBFlexGrid too has the same issue in both OSes.


Actually, I tried a similar approach (setting FontCharset to 0) in VBFlexGrid but it did not work out. I tried copy/pasting the aforesaid range of characters from MS Word to VBFlexGrid. Then also, it did not work. Only square boxes were displayed for these characters.


Actually, in Windows 7, for 'Unifont Upper', the characters at 69635 to 69709 (and many more following characters) also get displayed as two boxes only, in VBFlexGrid. These same characters were getting displayed correctly in VBFlexGrid in Windows10.


Is the above issue of some characters not getting displayed in VBFlexGrid, a limitation of the VBFlexGrid control in Windows7 and Windows10?


Same way, the issue of some characters alone not getting displayed in RichTextBox in Win7, a limitation of the RichTextBox control in Windows7?


Well, I do not know. May be there is some mistake in the fonts themselves (Unifont Upper, Siddhanta, etc.) in their encodings inside them. May be I am doing a mistake somewhere too. So, if you can either kindly point out the mistake I am committing OR confirm that it is a limitation of the controls in different operating systems, I would be grateful to you. If at all you can provide a permanent solution for the above issue, somehow, it would be simply superb, Krool.


Note: I have talked about the issue in RichTextBox control in this thread itself since I felt that the issues in the VBFlexGrid and RichTextBox are similar. I also felt that if I shared the issues I faced in RichTextBox with you, it may throw some light^^ on solving the VBFlexGrid issue too. However, if you feel that the issues are different, then kindly let me know whether I have to post my query regarding the issue in RichTextBox control in the main VBCCR thread.


(^^) General observations
As far as I have seen, in Windows 7 (64-bit), if a character gets displayed in VBFlexGrid (for 'Unifont Upper'), it gets displayed in RichTextBox also. e.g. code point 118847 (BYZANTINE MUSICAL SYMBOL ICHADIN). If a character does not get displayed in VBFlexGrid (for 'Unifont Upper'), it does not get displayed in RichTextBox also. e.g. code point 69216 (RUMI DIGIT ONE).


In Windows10 (64-bit), RichTextBox displays all characters (unlike VBFlexGrid) of 'Unicode Upper' font but has issues in the proper rendering of certain characters (for e.g. displaying the character at codepoint 69632 - BRAHMI SIGN CANDRABINDU). But then, it should be noted that VBFlexGrid itself renders such characters in the same improper way only. And, whenever such a character is displayed in a RichTextBox (in 'Unicode Upper' font), thereafter (i.e. when handling subsequent characters), the RichTextBox could not display all the characters of 'Unifont Upper'. It was able to display only those characters which were displayed by VBFlexGrid. Whatever characters were displayed as boxes in VBFlexGrid were displayed as boxes in RichTextBox too.


Well, just before completing the drafting of this message, I thought I would just check with the latest ".Net Core" C# gui controls. In my sample test (in Windows 10), the RichTextBox and DataGridView of C+ behaved exactly the same way like your RichTextBox and VBFlexGrid in every case (incl. what happens subsequently after the character at 69632 is displayed). So, wonder now, whether after all, the underlying COM (or library interfaces or whatever) of MS itself has problems. Anyway, I am not an expert in C#. Just for a quick test, I created a simple form with just two controls (RichTextBox and DataGridView) and copy/pasted the characters from your VBFlexGrid control onto these two controls.


As far as I can say, MS Word (I have only Word 2007) has no issues in displaying any character, in both Windows 7 and Windows10, correctly. A software like BabelMap or a font manager like MainType also show all the characters of a font like 'Unifont Upper' perfectly, in their own respective Grid displays. Probably these applications are drawing the character glyphs using some other methodology? If (and only if) so, can the same approach be used for your/our VBFlexGrid also? It would be of immense benefit to the society.


Thanks a TON once again, dear Krool, for your monumental work.


Prayers and Kind Regards.

Quote:

---

Originally Posted by softv

Well, just before completing the drafting of this message, I thought I would just check with the latest ".Net Core" C# gui controls. In my sample test (in Windows 10), the RichTextBox and DataGridView of C+ behaved exactly the same way like your RichTextBox and VBFlexGrid in every case (incl. what happens subsequently after the character at 69632 is displayed). So, wonder now, whether after all, the underlying COM (or library interfaces or whatever) of MS itself has problems.

---

That indicates that there is a problem in the underlying API.

Somehow MS Word (and others) maybe renders differently and don't rely on OS API's.

If that's true then I cannot help you because I can only use the OS API's for rendering and don't have another option.

Quote:

---

Originally Posted by Krool

Normally the OLEGuids.tlb is not needed for a compiled VBCCR16.

---

Krool,

When I updated the OLEGuids.tbl from vintage VBCCR14 the VBFlexGrid demo is giving the same error I posted above. (worked withe VBCCR14 OLEGuids.tbl) Do you have a VBFlexGrid demo that works with the current OLEGuids.tbl?

BTW:

Using VBCCR16 with my AxtiveX DLL portable GUI forms works great. They callback to ScriptBasic for events and the ScriptBasic COM/OLE extension module communicates with it calling methods and GET/SET properties I've defined as class functions / properties. The Auto and Select button callbacks create ScriptBasic threads which communicate via common memory variables and a VB timer control that updates the form every 2.5 seconds.

Attachment 178454

Quote:

---

Originally Posted by ScriptBASIC

When I updated the OLEGuids.tbl from vintage VBCCR14 the VBFlexGrid demo is giving the same error I posted above. (worked withe VBCCR14 OLEGuids.tbl) Do you have a VBFlexGrid demo that works with the current OLEGuids.tbl?

---

Something is wrong on your side. The OLEGuids.tlb is 1:1 the same for VBFlexGrid and for the VBCCR controls.
Maybe you have an outdated VBFlexGrid download prior to 15-Apr-2020..

On a PC without OLEGuids.tlb I can load and run VBFLXGRD14 and VBCCR16/VBCCR17 in the IDE without a problem. Also the executable dealing with the OCX just runs fine.
You only need the OLEGuids.tlb when using the Std-EXE version of the controls(!) and then for compilation only.
A Std-EXE version using the OCX does not even need the OLEGuids.tlb for compilation.

Krool,

I have been searching far and wide and can't find out how to disable columns from being edited. I only have one column out of 6 I need to edit and get a callback that it changed. Can you point me to an example how this is done?

Thanks!

BTW: Downloading the latest VBFlexGridDemo is working with my VBCCR16 and dependencies. (Windows 10 Pro)

John

Quote:

---

Originally Posted by ScriptBASIC

I have been searching far and wide and can't find out how to disable columns from being edited. I only have one column out of 6 I need to edit and get a callback that it changed. Can you point me to an example how this is done?

---

You can disable a column from being edited by handling the BeforeEdit event and return Cancel = True.

The chart will illustrate the flow of the various events: Attachment 167489

Krool,

Brilliant and simple, THANK YOU!

Events can be your friend. ;)

Krool,

Is the code demo for multi-row select as shown in post #367 available anywhere? I read through the complete thread and didn't find anything about how to do multi-row / random selects other than post #367 but no code attached.

Krool,

I was able to get my random row select working with the CellClick event. I use the left mouse button to select a cell (changing background) and right mouse button to reset it. I keep an array of the selected rows.

Is It possible to know indexes of all selected rows (without mouse properties)?

Hi Krool,

Is there a way to disable horizontal scrolling of the VBFlexGrid? When I click on the last (right most) column it shifts my first column off the grid screen and displays a dark grey area as a next column that doesn't exist. Attached is how I would like to keep the grid positioned.

Attachment 179229

Quote:

---

Originally Posted by ScriptBASIC

Hi Krool,

Is there a way to disable horizontal scrolling of the VBFlexGrid? When I click on the last (right most) column it shifts my first column off the grid screen and displays a dark grey area as a next column that doesn't exist. Attached is how I would like to keep the grid positioned.

Attachment 179229

---

This is a bug. I see in the screen that you have the .ScrollBars property set to 'Vertical'.
In the normal MS(H)FlexGrid this will result that the LeftCol will not change upon mouse clicks and key movements. (Even not when changing by code - very strict therefore)

Currently the VBFlexGrid will behave always as if .ScrollBars is set to 'Both'.

I noted it down and will be fixed soon. Thanks!

EDIT:
Bugfix done. Small change done in the internal SetRowColParams method with a big effect. (marked as blue)
It's a very strict way of disabling it but it seems the MS(H)FlexGrid doing the same thing. (Not even allowing .LeftCol changed by code when .ScrollBars is set to 'Vertical' for instance)

Code:

---

Private Sub SetRowColParams(ByRef RCP As TROWCOLPARAMS)
[...]
Select Case PropScrollBars
    Case vbSBNone
        If (.Mask And RCPM_TOPROW) = RCPM_TOPROW Then .Mask = .Mask And Not RCPM_TOPROW
        If (.Mask And RCPM_LEFTCOL) = RCPM_LEFTCOL Then .Mask = .Mask And Not RCPM_LEFTCOL
    Case vbHorizontal
        If (.Mask And RCPM_TOPROW) = RCPM_TOPROW Then .Mask = .Mask And Not RCPM_TOPROW
    Case vbVertical
        If (.Mask And RCPM_LEFTCOL) = RCPM_LEFTCOL Then .Mask = .Mask And Not RCPM_LEFTCOL
End Select
[...]

---

Update related to yesterday's bugfix.

Included new flags RCPF_FORCETOPROWMASK and RCPF_FORCELEFTCOLMASK which is used in the internal SetRowColParams method.

Code:

---

Private Sub SetRowColParams(ByRef RCP As TROWCOLPARAMS)
[...]
Select Case PropScrollBars
    Case vbSBNone
        If Not (.Flags And RCPF_FORCETOPROWMASK) = RCPF_FORCETOPROWMASK Then
            If (.Mask And RCPM_TOPROW) = RCPM_TOPROW Then .Mask = .Mask And Not RCPM_TOPROW
        End If
        If Not (.Flags And RCPF_FORCELEFTCOLMASK) = RCPF_FORCELEFTCOLMASK Then
            If (.Mask And RCPM_LEFTCOL) = RCPM_LEFTCOL Then .Mask = .Mask And Not RCPM_LEFTCOL
        End If
    Case vbHorizontal
        If Not (.Flags And RCPF_FORCETOPROWMASK) = RCPF_FORCETOPROWMASK Then
            If (.Mask And RCPM_TOPROW) = RCPM_TOPROW Then .Mask = .Mask And Not RCPM_TOPROW
        End If
    Case vbVertical
        If Not (.Flags And RCPF_FORCELEFTCOLMASK) = RCPF_FORCELEFTCOLMASK Then
            If (.Mask And RCPM_LEFTCOL) = RCPM_LEFTCOL Then .Mask = .Mask And Not RCPM_LEFTCOL
        End If
End Select
[...]

---

This way there is a internal way of forcing a scroll, even if there is no scrollbar. (of course the 'Scroll' event will not be fired as no scrollbar exist)

Reason for such a forcing is that when changing the Rows/Cols property and the current TopRow/LeftCol is > Rows/Cols and the scrollbar do not exist accordingly then no cells are visible anymore (only fixed cells)
The MS(H)FlexGrid behaves in this way to "scroll" so that at least one row/column is shown. (even if there is no scrollbar)
Therefore such flags are now used in the Rows/Cols property.

Who knows, maybe somebody finds out that another property also needs such exception. Then the flag is already available and easy fix-able.

Attachment 179260

Sorry：

Compile Error
Subroutine or function is not defined

Quote:

---

Originally Posted by smileyoufucn

Attachment 179260

Sorry：

Compile Error
Subroutine or function is not defined

---

You use an outdated "Startup.bas".
Please download the complete VBFlexGridDemo and replace everything. (FlexInitIDEStopProtection does not exist anymore since a while)

Quote:

---

Originally Posted by Krool

You use an outdated "Startup.bas".
Please download the complete VBFlexGridDemo and replace everything. (FlexInitIDEStopProtection does not exist anymore since a while)

---

vbforums: "VBFlexGridDemo.zip" and "ComCtlsDemo.zip" and “ActiveX Control Version” OK
github: "VBFLXGRD-master\Standard EXE Version\VBFlexGridDemo.vbp" and "VBCCR-master\Standard EXE Version" NG

Suggest update

Quote:

---

Originally Posted by smileyoufucn

vbforums: "VBFlexGridDemo.zip" and "ComCtlsDemo.zip" and “ActiveX Control Version” OK
github: "VBFLXGRD-master\Standard EXE Version\VBFlexGridDemo.vbp" and "VBCCR-master\Standard EXE Version" NG

Suggest update

---

Thanks. Done

Use Set VBFlexGrid1.DataSource = rst
Must use assignment code: ColKey(i) = strKey,
Otherwise, an error will be prompted: Invalid col Value

Is my usage method wrong?

Code:

---

Option Explicit
Public Cnn As New ADODB.Connection
Private Sub Form_Load()
    Dim i As Long
    Dim strSQL As String
    Dim strKey As String
    Dim rst As ADODB.Recordset
    OpenConn App.Path & "\Data.mdb"
    strSQL = "SELECT * FROM Sys_tblUsers"
    Set rst = GetRs(strSQL)
    Set VBFlexGrid1.DataSource = rst
    Set rst = GetRs(strSQL)
    i = VBFlexGrid1.Cols - 1
    With VBFlexGrid1
        For i = 0 To i - 1
            strKey = rst.Fields(i).Name
'            .ColKey(i) = strKey'Invalid col Value
            Debug.Print "VBFlexGrid2  i=" & i & "  = " & .ColKey(.ColIndex(strKey)); ""
        Next
    End With
End Sub


Public Sub OpenConn(mdbFileName As String)
    Cnn.CursorLocation = adUseClient
    Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFileName
End Sub

Public Function GetRs(Query As String) As ADODB.Recordset
    Set GetRs = New ADODB.Recordset
    GetRs.Open Query, Cnn, adOpenStatic, adLockOptimistic
End Function

---

Test1:

Attachment 179262


Test2:
Attachment 179263


Test3:
Attachment 179261


Demo File:
Attachment 179264

Code:

---

Public Property Set DataSource(ByVal Value As MSDATASRC.DataSource)
    Set PropDataSource = Value
    If VBFlexGridDesignMode = False Then
        If Not PropDataSource Is Nothing Then
            If PropRecordset Is Nothing Then Set PropRecordset = CreateObject("ADODB.Recordset")
            With PropRecordset
                If .State <> 0 Then .Close
                If StrPtr(PropDataMember) = 0 Then .DataMember = "" Else .DataMember = PropDataMember
                Set .DataSource = PropDataSource
                If .State <> 0 Then
                    If .RecordCount > -1 Then        '  The cursor type of the Recordset affects whether the number of records can be determined.
                        Me.Rows = PropFixedRows + .RecordCount
                        Me.Cols = PropFixedCols + .Fields.Count
                        Dim iRow As Long, iCol As Long
                        If PropFixedRows > 0 Then
                            For iCol = 0 To (.Fields.Count - 1)
                                Me.TextMatrix(0, iCol + PropFixedCols) = .Fields(iCol).Name
                                '''''''''''''''''''''''''Add'''''''''''''''''''''''''''''''''''
                                VBFlexGridColsInfo(iCol + PropFixedCols).Key = .Fields(iCol).Name
                                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                            Next iCol
                        End If
                        If .RecordCount > 0 Then
                            Dim ArrRows As Variant
                            ArrRows = .GetRows(, 1)  ' adBookmarkFirst
                            Dim LBoundCols As Long, UBoundCols As Long
                            LBoundCols = LBound(ArrRows, 1)
                            UBoundCols = UBound(ArrRows, 1)
                            Dim LBoundRows As Long, UBoundRows As Long
                            LBoundRows = LBound(ArrRows, 2)
                            UBoundRows = UBound(ArrRows, 2)
                            For iRow = LBoundRows To UBoundRows
                                For iCol = LBoundCols To UBoundCols
                                    If Not IsNull(ArrRows(iCol, iRow)) Then
                                        Me.TextMatrix((iRow + (0 - LBoundRows)) + PropFixedRows, (iCol + (0 - LBoundCols)) + PropFixedCols) = ArrRows(iCol, iRow)
                                    Else
                                        Me.TextMatrix((iRow + (0 - LBoundRows)) + PropFixedRows, (iCol + (0 - LBoundCols)) + PropFixedCols) = vbNullString
                                    End If
                                Next iCol
                            Next iRow
                        End If
                    End If
                End If
            End With
        Else
            Set PropRecordset = Nothing
        End If
    End If
    UserControl.PropertyChanged "DataSource"
End Property

---

When you use .ColIndex and did not assign .ColKey before then you get the error.
Your Test 2 is the correct approach.
Test 3 is also ok. But on each update you need to keep your changes in mind.

thank you very much
VSFLEXGRID.ocx defaults to the TEST3 scheme.
Is MSFLEXGRID.ocx the TEST2 solution by default?
If the TEST3 scheme is used by default in the project, will there be any incompatibility with MSFLEXGRID.ocx?
If there is no compatibility problem, then using the Test3 solution in the project should be easier and more convenient to use.

Quote:

---

Originally Posted by smileyoufucn

thank you very much
VSFLEXGRID.ocx defaults to the TEST3 scheme.
Is MSFLEXGRID.ocx the TEST2 solution by default?
If the TEST3 scheme is used by default in the project, will there be any incompatibility with MSFLEXGRID.ocx?
If there is no compatibility problem, then using the Test3 solution in the project should be easier and more convenient to use.

---

MSFlexGrid has no .ColKey/.ColIndex ...
So if VSFlexGrid behave as Test 3 then I will check this out soon and maybe implement your change into the "master" version.

Quote:

---

Originally Posted by Krool

MSFlexGrid has no .ColKey/.ColIndex ...
So if VSFlexGrid behave as Test 3 then I will check this out soon and maybe implement your change into the "master" version.

---

Krool, thank you very much
If it can be updated to the project, it will be more convenient to use.

Add VSFlexGrid1 Test：


Attachment 179273

Code:

---

Option Explicit
Public Cnn As New ADODB.Connection
Private Sub Form_Load()
    Dim i As Long
    Dim strSQL As String
    Dim strKey As String
    Dim rst As ADODB.Recordset
    OpenConn App.Path & "\Data.mdb"
    strSQL = "SELECT * FROM Sys_tblUsers"
    Set rst = GetRs(strSQL)
    Set VBFlexGrid1.DataSource = rst
    i = VBFlexGrid1.Cols - 1
    With VBFlexGrid1
        For i = 0 To i - 1
            strKey = rst.Fields(i).Name
            .ColKey(i) = strKey  'VBFlexGrid2
            Debug.Print "VBFlexGrid2  i=" & i & "  = " & .ColKey(.ColIndex(strKey)); ""
        Next
    End With
    ''''''''''''Add New VSFlexGrid1 Test ''''
    Set VSFlexGrid1.DataSource = rst                  '
    i = VSFlexGrid1.Cols - 1
    With VSFlexGrid1
        For i = 0 To i - 1
            strKey = rst.Fields(i).Name
            '''              .ColKey(i) = strKey '''VSFlexGrid1 No need for this line of code
            Debug.Print "VSFlexGrid1  i=" & i & "  = " & .ColKey(.ColIndex(strKey)); ""
        Next
    End With
End Sub

---

Attachment 179272

A bit late, but just stumbled over it (post #336 and following ones, about Sorting Data in the VBFlex)

Quote:

---

Originally Posted by Krool

The error says "functionality is disabled".
Reason is crystal clear as you have a virtual binding to a sqlite recordset.
If you want sorting - fine. Just append a "ORDER BY ..." in your sqlite SELECT statement.

---

Quote:

---

Originally Posted by newbie2

In fact I need to sort them after having fed the flexgrid control.

---

That's possible, by using the Sort-Property of the (bound) SQLite-Rs ... in the same way as with ADO-Rs
This way one can avoid additional roundtrips over the DB (or the DB-Server) just for ordering-purposes.

To be able to reach the Sort-Property (behind your Binding-Class), one can simply expose the internal mRs via a Property Get:

Code:

---

Option Explicit

Implements IVBFlexDataSource
Private mRs As cRecordset

'a Public Method, to allow Binding to an outside DataSource (in this case an SQLite-cRecordset)
Public Function BindTo(Rs As cRecordset) As IVBFlexDataSource
  Set mRs = Rs
  Set BindTo = Me 'return a reference of our Class-instance to the outside
End Function

Public Property Get Rs() As cRecordset 'expose mRs to the outside
  Set Rs = mRs
End Property

'*** Ok, finally the 5 method Implementations of IVBFlexDataSource
Private Function IVBFlexDataSource_GetFieldCount() As Long
  IVBFlexDataSource_GetFieldCount = mRs.Fields.Count
End Function
Private Function IVBFlexDataSource_GetFieldName(ByVal Field As Long) As String
  IVBFlexDataSource_GetFieldName = mRs(Field).Name
End Function
Private Function IVBFlexDataSource_GetRecordCount() As Long
  IVBFlexDataSource_GetRecordCount = mRs.RecordCount
End Function

Private Function IVBFlexDataSource_GetData(ByVal Field As Long, ByVal Record As Long) As String
  If Record < mRs.RecordCount Then IVBFlexDataSource_GetData = mRs.ValueMatrix(Record, Field)
End Function
Private Sub IVBFlexDataSource_SetData(ByVal Field As Long, ByVal Record As Long, ByVal NewData As String)
  If Record < mRs.RecordCount Then mRs.ValueMatrix(Record, Field) = NewData
End Sub

---

A "sort-switching by ColumnName" would then be possible in the Form-Code via a Helper like the one below:
(passing an empty String, to restore the original Sort-Order, as it was coming from the DB-Select)

Code:

---

Private Sub SwitchSorting(FieldName As String, Optional ByVal Descending As Boolean)
  myDataSource.Rs.Sort = FieldName & IIf(Descending, " Desc", "")
  myVBFlexGrid.Refresh
End Sub

---

HTH

Olaf

Quote:

---

Originally Posted by smileyoufucn

If it can be updated to the project, it will be more convenient to use.

---

Done. :)

The blue marked code was now inserted into the DataSource property.

Code:

---

If PropFixedRows > 0 Then
    For iCol = 0 To (PropFixedCols - 1)
        VBFlexGridColsInfo(iCol).Key = vbNullString
    Next iCol
    For iCol = 0 To (.Fields.Count - 1)
        Me.TextMatrix(0, iCol + PropFixedCols) = .Fields(iCol).Name
        VBFlexGridColsInfo(iCol + PropFixedCols).Key = .Fields(iCol).Name
    Next iCol
End If

---

Quote:

---

Originally Posted by Krool

Done. :)

The blue marked code was now inserted into the DataSource property.

Code:

---

If PropFixedRows > 0 Then
    For iCol = 0 To (PropFixedCols - 1)
        VBFlexGridColsInfo(iCol).Key = vbNullString
    Next iCol
    For iCol = 0 To (.Fields.Count - 1)
        Me.TextMatrix(0, iCol + PropFixedCols) = .Fields(iCol).Name
        VBFlexGridColsInfo(iCol + PropFixedCols).Key = .Fields(iCol).Name
    Next iCol
End If

---

---

Krool：
The test is OK. Thank you very much for your help!

Krool,

Thanks for the fix for horizontal scrolling!

Hating to add a question given your substantial contributions already made, but my search through the thread did not yield an answer. In short, is there a way to land on a cell that has been set as a dropdown combo and have it not drop the list, just shot the text and the button. (We are hoping to use this as a container for editable fields of a variety of different types. Thanks!

Don

Quote:

---

Originally Posted by TheLeePiper

Hating to add a question given your substantial contributions already made, but my search through the thread did not yield an answer. In short, is there a way to land on a cell that has been set as a dropdown combo and have it not drop the list, just shot the text and the button. (We are hoping to use this as a container for editable fields of a variety of different types. Thanks!

Don

---

Use the .ComboButtonValue property and set to 'FlexComboButtonValueUnpressed' to collapse the drop-down list.
At best you place that line of code in the "EnterEdit" event.

Quote:

---

Originally Posted by Krool

Use the .ComboButtonValue property and set to 'FlexComboButtonValueUnpressed' to collapse the drop-down list.
At best you place that line of code in the "EnterEdit" event.

---

I had just what you mentioned, but in the EnterCell function. When moved to EnterEdit, it quickly drops the list then closes it again. I'll pay with the order of calls to see if I can make that stop. Thanks!