VBFlexGrid Control (Replacement of the MSFlexGrid control)

[lizanodiaz]

Good morning Krool, I hope you are well, well the present is that in the mega link that I just posted is the improved vbflexgrid with features such as toggle button, progress bar, etc.
It would be very helpful if you adapted it to your latest version of your vbflexgrid.
Honestly, it would be a great advance in the project.


We would all be grateful.

Mega link:
https://mega.nz/file/9Zl0ET6Q#ZIERxd...tnZN4FVrzctYGY

[Calcu]

And if you can solve the problem with 2 or more grids in the same mdi form, that hangs everything in IDE... i will be eternally grateful too :-)

[Krool]

And if you can solve the problem with 2 or more grids in the same mdi form, that hangs everything in IDE... i will be eternally grateful too :-)

I can't replicate. What do you mean exactly? Demo ?

[smileyoufu]

Good morning Krool, I hope you are well, well the present is that in the mega link that I just posted is the improved vbflexgrid with features such as toggle button, progress bar, etc.
It would be very helpful if you adapted it to your latest version of your vbflexgrid.
Honestly, it would be a great advance in the project.


We would all be grateful.

Mega link:
https://mega.nz/file/9Zl0ET6Q#ZIERxd...tnZN4FVrzctYGY

Hello Lizanodiaz!

The hyperlink cannot be opened. Can you upload the source code file for reference and learning? Thank you very much!

[lizanodiaz]

Hello Lizanodiaz!

The hyperlink cannot be opened. Can you upload the source code file for reference and learning? Thank you very much!

Here please:

https://www.4shared.com/s/fZvAhWyOdku

[Krool]

Update released.

Included the AllowIncrementalSearch property.

[Calcu]

post 855 + 856 - you will have an example and a video

it was when 2 or more grid were in a same MDI form, if you write any line of code or add any control and press save, you will see the grids grayed.

and if you execute the program, it hangs.

you said it was because UserControl_Terminate was not fired first time, i think is for this, only 1 grid is unloaded from memory and then when you run it again, just closes everything

[Krool]

post 855 + 856 - you will have an example and a video

it was when 2 or more grid were in a same MDI form, if you write any line of code or add any control and press save, you will see the grids grayed.

and if you execute the program, it hangs.

you said it was because UserControl_Terminate was not fired first time, i think is for this, only 1 grid is unloaded from memory and then when you run it again, just closes everything

That's natural. The IDE can zombify UserControls (hatched/grayed) when it wants to. There is no way to avoid it.
A possible solution would be to use the OCX.
Others use the OCX during IDE design-time and when compiling switch to the Std-Exe version. See MountainMan's OCX2StdExe Update/Compile Utility

[Krool]

I'm a big fan of Before* events with a Cancel parameter, e.g.:

Public Event BeforePaste(ByRef Text As String, ByRef Cancel As Boolean)

Setting Cancel = True will abort the paste operation.


Another advantage of a BeforePaste event is that you can "massage" the incoming data if necessary (for example, de-formatting currency strings, normalizing date string, etc...).

Update released.

Included the BeforePaste(ByRef Text As String, ByRef Cancel As Boolean) and AfterPaste() event.

Included the ParseClip/ConstructClip function.

Those parse/construct helper functions are especially useful in the BeforePaste event. Of course they can be used outside of it as well.
Example:

Private Sub VBFlexGrid1_BeforePaste(Text As String, Cancel As Boolean)
With VBFlexGrid1
.ClipSeparatorRow = vbCrLf
.ClipSeparatorCol = vbTab
If Right$(Text, Len(.ClipSeparatorRow)) = .ClipSeparatorRow Then ' Excel
    ' Exclude last empty line.
    Text = Left$(Text, Len(Text) - Len(.ClipSeparatorRow))
End If
' Parse clip string into an two-dimensional array indexed by row/col subscripts.
Dim ArrRows As Variant
ArrRows = .ParseClip(Text)
'
' Validate and modify the array...
'
' Re-construct text from the modified array.
Text = .ConstructClip(ArrRows)
End With
End Sub

The BeforePaste event can also be useful in conjunction with .ClipPasteMode = FlexClipPasteModeAutoSelection
Example:

' Ensure that auto selection will have enough rows available.
.ClipPasteMode = FlexClipPasteModeAutoSelection
Dim TopRow As Long
.GetSelRange TopRow, 0, 0, 0
If (TopRow + (UBound(ArrRows) + 1)) > .Rows Then .Rows = (TopRow + (UBound(ArrRows) + 1))

The AfterPaste event is most useful to make some auto sizing adjustment.
Example:

Private Sub VBFlexGrid_AfterPaste()
With VBFlexGrid1
Dim LeftCol As Long, RightCol As Long
.GetSelRange 0, LeftCol, 0, RightCol
.AutoSize LeftCol, RightCol, FlexAutoSizeModeColWidth
End With
End Sub

The .ParseClip function also have optional Rows/Cols parameters to restrict maximal ubounds for the created array.

ByRef Text As String, Optional ByVal Rows As Long = -1, Optional ByVal Cols As Long = -1

[Calcu]

But it only happens when 2 or more grids are in the same form, if you have only one, it never hangs, i'm pretty sure it's happening when trying to terminate the second control, it's hanging in there :-(

I' using a control (uctabmdi) that has a routine to stop all subclasing passing the windows handle, maybe with something like this it will stop hanging?, i tried to apply it to your control, but... lack of knowledege to do it :-(

Attached the uctabmdi.ctl, maybe you can see how it handles subclassing and... it shows an idea about how to avoid this problem ?

A really big thanks for everything.

[Calcu]

Hi all,

A question. If i have one combo cell, with 3 items (s|n|x).. can I paste other option (for example a 't') without reloading everything ?

I think in a combo cell i can't write anything.

EDIT!

Found it. just simple as change

.ColComboMode(.Col) = FlexComboModeDropDown

with

.ColComboMode(.Col) = FlexComboModeEditable



Thanks!

[Erwin69]

I feel a little embarrassed having to ask this, but I’m drawing a blank, and searches didn’t bring up any solution.

The user can select a range of cells in the grid, and then execute an action against the selected cells. However, the exact action depends on the cell’s contents.

My idea is to cycle through the cells, evaluate each cell’s contents, and apply the correct action. However, how do I know which cells are the ones that are selected?

[Krool]

Update released.

Included the ColWordWrapOption/ColWordWrapOptionFixed run-time property.

This was an over-due feature missing from the MSHFlexGrid.

Now it is possible to overwrite per column from the default text formatting of the general WordWrap and SingleLine property.

[Krool]

I feel a little embarrassed having to ask this, but I’m drawing a blank, and searches didn’t bring up any solution.

The user can select a range of cells in the grid, and then execute an action against the selected cells. However, the exact action depends on the cell’s contents.

My idea is to cycle through the cells, evaluate each cell’s contents, and apply the correct action. However, how do I know which cells are the ones that are selected?

Yes, looping through selection is then the way to go.

Code example:

Dim TopSel As Long, LeftSel As Long, BottomSel As Long, RightSel As Long
VBFlexGrid1.GetSelRange TopSel, LeftSel, BottomSel, RightSel
Dim iRow As Long, iCol As Long
For iRow = TopSel To BottomSel
    For iCol = LeftSel To RightSel
        Select Case VBFlexGrid1.TextMatrix(iRow, iCol)
            Case "content1 of each cell in selection"
                ' Take your action
            Case "content2 of each cell in selection"
                ' or something else
        End Select
    Next iCol
Next iRow

[Erwin69]

Yes, looping through selection is then the way to go.

Code example:

Dim TopSel As Long, LeftSel As Long, BottomSel As Long, RightSel As Long
VBFlexGrid1.GetSelRange TopSel, LeftSel, BottomSel, RightSel
Dim iRow As Long, iCol As Long
For iRow = TopSel To BottomSel
    For iCol = LeftSel To RightSel
        Select Case VBFlexGrid1.TextMatrix(iRow, iCol)
            Case "content1 of each cell in selection"
                ' Take your action
            Case "content2 of each cell in selection"
                ' or something else
        End Select
    Next iCol
Next iRow

I'm actually evaluating the CellPicture, which doesn't seem to work with the TextMatrix. I now simply select the cell using the counters in the For...Next loops, and do the check. Or would there be a better way?

[Krool]

I'm actually evaluating the CellPicture, which doesn't seem to work with the TextMatrix. I now simply select the cell using the counters in the For...Next loops, and do the check. Or would there be a better way?

Instead of .TextMatrix use:

VBFlexGrid1.Cell(FlexCellPicture, iRow, iCol)

[softv]

Dear Krool,

Warm Greetings!

I observed just a while ago the following:
--
I have 3 columns in a grid. The 2nd column has checkboxes. The columns are resizable. If I resize all the columns to near-zero width, then the checkboxes alone show up at the end of all the shrunk columns. If my observation is not right, then kindly let me know what one has to do so that the checkboxes do not get displayed.
--

By the way, in a normal combobox, using API, we can increase the width of the dropdown so that the longest item is also visible fully. How to do the same with the combo dropdown in our Flexgrid. I tried a hack some weeks back but it did not work (If I remember right, I think I increased the width of the column header in BeforeEdit event (to match the length of the longest item in the list) and then again shrunk back the width to its original size).

I take this opportunity to once again thank you profoundly for ALL your wonderful wonderful wonderful controls and great support.

Kind Regards.

[Krool]

Dear Krool,

Warm Greetings!

I observed just a while ago the following:
--
I have 3 columns in a grid. The 2nd column has checkboxes. The columns are resizable. If I resize all the columns to near-zero width, then the checkboxes alone show up at the end of all the shrunk columns. If my observation is not right, then kindly let me know what one has to do so that the checkboxes do not get displayed.
--

By the way, in a normal combobox, using API, we can increase the width of the dropdown so that the longest item is also visible fully. How to do the same with the combo dropdown in our Flexgrid. I tried a hack some weeks back but it did not work (If I remember right, I think I increased the width of the column header in BeforeEdit event (to match the length of the longest item in the list) and then again shrunk back the width to its original size).

I take this opportunity to once again thank you profoundly for ALL your wonderful wonderful wonderful controls and great support.

Kind Regards.

1. Thanks, will look into it.

2. It can done as following: (example)

Private Sub VBFlexGrid1_EditSetupWindow(BackColor As stdole.OLE_COLOR, ForeColor As stdole.OLE_COLOR)
Dim RC As RECT
If VBFlexGrid1.hWndComboList <> 0 Then
    GetWindowRect VBFlexGrid1.hWndComboList, RC
    Const MIN_WIDTH_PX As Long = 100
    If (RC.Right - RC.Left) < MIN_WIDTH_PX Then
        SetWindowPos VBFlexGrid1.hWndComboList, 0, 0, 0, MIN_WIDTH_PX, (RC.Bottom - RC.Top), SWP_NOACTIVATE Or SWP_NOMOVE Or SWP_NOOWNERZORDER Or SWP_NOZORDER
    End If
End If
End Sub

[Krool]

Dear Krool,

Warm Greetings!

I observed just a while ago the following:
--
I have 3 columns in a grid. The 2nd column has checkboxes. The columns are resizable. If I resize all the columns to near-zero width, then the checkboxes alone show up at the end of all the shrunk columns. If my observation is not right, then kindly let me know what one has to do so that the checkboxes do not get displayed.
--

By the way, in a normal combobox, using API, we can increase the width of the dropdown so that the longest item is also visible fully. How to do the same with the combo dropdown in our Flexgrid. I tried a hack some weeks back but it did not work (If I remember right, I think I increased the width of the column header in BeforeEdit event (to match the length of the longest item in the list) and then again shrunk back the width to its original size).

I take this opportunity to once again thank you profoundly for ALL your wonderful wonderful wonderful controls and great support.

Kind Regards.

I can't repro with the checkboxes. Demo please..

[Krool]

Update released.

Changing TopRow/LeftCol by app code now works even when there are no scrollbars. (like in vsFlexGrid)

Also the Scroll event is fired then in such a "no scroll bar" scenario, forced by code. (again, like in vsFlexGrid)

Minor tweak also, the order of 2x Scroll event (e.g. Ctrl+End) is now TopRow then LeftCol to match MS(H)FlexGrid and vsFlexGrid.
Should be negligible but worth mentioning. (prior to this change the LeftCol was changed first and then TopRow)

[Erwin69]

Hi Krool,

I meant to ask this before, forgot, but your message reminded me:

Is it possible to display a vertical scrollbar permanently, even if the number of rows wouldn't require one? I like the columns to fill the width of the grid nicely, so that there is no "unused space" on the right, but it's always a pain to deal with resizing them depending on whether a vertical scrollbar is displayed or not.

Thanks,
Erwin

[Krool]

Hi Krool,

I meant to ask this before, forgot, but your message reminded me:

Is it possible to display a vertical scrollbar permanently, even if the number of rows wouldn't require one? I like the columns to fill the width of the grid nicely, so that there is no "unused space" on the right, but it's always a pain to deal with resizing them depending on whether a vertical scrollbar is displayed or not.

Thanks,
Erwin

Did you consider the ExtendLastCol property?
And/Or the DisableNoScroll property?

[Erwin69]

Did you consider the ExtendLastCol property?
And/Or the DisableNoScroll property?

Learning every day! DisableNoScroll will do the trick.

Didn't find the ExtendLastCol, has it been added after release 1.4?

[Calcu]

Uh.. a LOT of good changes since 1.4 xD

Just update it, speed, new features... buf..

[Erwin69]

Hi,

FASTEST WAY TO MOVE A SET OF DATA INTO A GRID
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?

Thanks,
Erwin

[Calcu]

Exactly the same way, with the .getrows feature

Sorry, i read too fast, the .getrows is working to fill de grid, about the export feature ... i will wait for Krool answer xD

[Krool]

Hi,

FASTEST WAY TO MOVE A SET OF DATA INTO A GRID
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?

Thanks,
Erwin

The fastest way is to not move the data into grid.
Just use .GetRows of your db and use .FlexDataSource to feed the grid from your .GetRows array per-demand for the current viewport.

[Erwin69]

Thanks for the quick reply, but I wonder if that is the best approach given the specific situation I'm dealing with.

I didn't want to make the original question too complex/large, but maybe I should have added some additional info.

The process is as follows:

1. The user selects some options
2. Based on those the data is retrieved from the database (normally < 1000 records, usually even < 300)
3. The user then applies "actions" to the grid (think popup menu or double click cells to mark them)
4. Cell contents change based on the action that is applied.

Once done, all rows that require and action are "batch processed".

Actions are not done on the records that were originally retrieved. As a generic example, that should be easier to understand than the industry specific thing I'm working on, think of retrieving a list of tasks that then are assigned to different people. Tasks in rows, people in columns. Double clicking the cell where the column and row crosses, changes its contents in a + when a task is assigned, or a - when it's taken away from the person. In the batch processing, an email is created for each person with the assigned tasks.

Knowing this, do you still feel that using the FlexDataSource is the best way to go?

[Krool]

How do you currently feed the grid? 1000 rows shouldbe feeded instantly with just setting .TextMatrix with 2 loops..

[Erwin69]

How do you currently feed the grid? 1000 rows shouldbe feeded instantly with just setting .TextMatrix with 2 loops..

I currently use two nested For...Next loops to cycle through rows and columns, and assign values to each cell. I figured that there would be something as TextMatrix that would help making that easier / quicker, but couldn't find an example of it.

(You may have seen a similar question in the Common Controls Replacement thread related to the ListView control. In that case we're more often dealing with larger numbers of records, where the listview is more used as an step in between the database and an Excel sheet. While I was at it, I thought to ask the question here as well as part of learning new things, and potentially reconsidering which control to use for what.)

[Calcu]

Hi all, is there any way to remember the last active cell ?

(not so easy xD) i mean: i have a MDI program, VBflexgrid is on one Form, i open the form and fill the grid, i select row 4 and column 6, not for edit, just select it.

I change to another program, firefox, for example, and then when i return to my program, R4-C6 is not selected.

I tried to save the row / col in form_lostfocus in 2 variables, and return them in form_getfocus but it's not working...

Any idea ?

[Krool]

Hi all, is there any way to remember the last active cell ?

(not so easy xD) i mean: i have a MDI program, VBflexgrid is on one Form, i open the form and fill the grid, i select row 4 and column 6, not for edit, just select it.

I change to another program, firefox, for example, and then when i return to my program, R4-C6 is not selected.

I tried to save the row / col in form_lostfocus in 2 variables, and return them in form_getfocus but it's not working...

Any idea ?

Normally the grid does not change the Row/Col (focus rect)
Do you have a demo showing the issue ? I cannot follow to be honest.

[Krool]

Update released.

Included the CellTag property which holds a variant to store any extra data.
The variant is allocated only when needed to keep memory usage low.

Included enum FlexClearTag for the .Clear method and included enum FlexCellTag for the .Cell property.

[Erwin69]

I noticed that the number of rows has to be larger than the number of fixed rows when setting this through the properties in the IDE. However, it is no problem to change the number of rows to be equal to the number of fixed rows through code.


The same question applies to columns.


My approach is often to show the user an empty grid with only the header row, which then will be expanded with additional columns depending on selections.


Is there a reason for this, or is it a bug? Or maybe this is fixed in a newer release than the 1.4 OCX I'm currently using?


It's not a big thing, as it's easy to workaround in code, but I was just wondering.

Regards,
Erwin

[Krool]

I noticed that the number of rows has to be larger than the number of fixed rows when setting this through the properties in the IDE. However, it is no problem to change the number of rows to be equal to the number of fixed rows through code.


The same question applies to columns.


My approach is often to show the user an empty grid with only the header row, which then will be expanded with additional columns depending on selections.


Is there a reason for this, or is it a bug? Or maybe this is fixed in a newer release than the 1.4 OCX I'm currently using?


It's not a big thing, as it's easy to workaround in code, but I was just wondering.

The property page does not allow this, correct. As there all properties will be set. However, you can also set the Rows equal to FixedRows in the IDE. Just use the property pane and not the property page.

[Krool]

Update released.

Included the ColImageList As Variant property. (like in vsFlexGrid)
Either an ImageList object or an hImageList handle can be set. (vsFlexGrid allows only handle)
Numeric text will be treated as an image index. (both, object or handle)
Otherwise it will be tried to access the image index by it's key. (object only)
The image alignment is based on the text alignment. (CellAlignment/ColAlignment/FixedAlignment)
The cell text will become hidden if an image got drawn.

Included the CellImageCallback event where the app can provide the image value.
The Handled parameter must be set to True to overwrite the default behavior.
It enables also a way to ensure that no image will be tried to retrieve for certain cells. (e.g. for fixed rows)

[Krool]

Update released.

I have 3 columns in a grid. The 2nd column has checkboxes. The columns are resizable. If I resize all the columns to near-zero width, then the checkboxes alone show up at the end of all the shrunk columns. If my observation is not right, then kindly let me know what one has to do so that the checkboxes do not get displayed.

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

[Krool]

Included the overdue OLEDragDropScroll property. (default to True)

The 1.6 OCX got this feature included (as defaulting to True anyway) but w/o property..

[Krool]

Update released.

Included the DropHighlight/DropHighlightMode run-time property.

The demo project got updated to use OLEDragDrop (instead of the "old" DragDrop) for the 'DragRow' demonstration.
Due to OLEDragDropScroll the VBFlexGrid can now scroll while dragging the row.

[Krool]

Update released.

Included the InsertMark/InsertMarkMode run-time property.
Included the HitTestInsertMark function.

The demo project got updated to demonstrate the insertion mark feature.