asked on

first row on grid issue

Hello all

I need your help on fixing an issue in my code.

If you look at the 2 picture bellow, you will see that i picture one, i have the first row where i have all the columns name. So far, so good.

But when i activate a code, it delete rows but suddenly, the column names are now in row 2. That's not good.

How can i fix this in my code?

Thanks for your help.

Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String

    QuestionToMessageBox = "Want to proceed?"

    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Want to proceed?")

    If YesOrNoAnswerToMessageBox = vbNo Then
         MsgBox "Cancelled"
    Else
         Dim lngRow As Long
    Dim lngListLoop As Long
    Dim dicUnique As New Scripting.Dictionary
    Dim lngRowHeight As Long
    Dim lngCount As Long
    
    lngRowHeight = MSHFlexGrid2.RowHeight(0)
    For lngListLoop = 0 To List1.ListCount - 1
        If List1.Selected(lngListLoop) Then
            dicUnique.Add List1.List(lngListLoop), 1
        End If
    Next
    Me.MousePointer = vbHourglass
    MSHFlexGrid2.Redraw = False
   lngRow = 1

    Do While True
        If dicUnique.Exists(MSHFlexGrid2.TextMatrix(lngRow, 1)) Then
            MSHFlexGrid2.RowHeight(lngRow) = lngRowHeight
            lngCount = lngCount + 1
            lngRow = lngRow + 1
        Else
            MSHFlexGrid2.RemoveItem (lngRow)
        End If
        If lngRow = MSHFlexGrid2.Rows Then Exit Do

    Loop
    
    MSHFlexGrid2.Redraw = True
    Me.MousePointer = vbDefault
    dicUnique.RemoveAll
    Set dicUnique = Nothing

        'Count de ligne dans le grid
        Dim zz As Long, txt As String, total1 As Long
For zz = 1 To MSHFlexGrid2.Rows - 1
  If Len(MSHFlexGrid2.TextMatrix(zz, 3)) Then total1 = total1 + 1

Next zz
lblTotalrecord1 = CStr(total1)

Open in new window

screen-shot-1.JPG
untitledScreen-shot-2.JPG

aikimark

The code you posted does not fill the grid, it only does the row-hiding you needed from this earlier question:
https://www.experts-exchange.com/questions/26960466/Hide-MSHFlexgrid2-rows-if.html

Please post the code that fills the grid.

Wilder1626

ASKER

Hello aikimark

When i fill the grid, everything is fine, see picture 1.

It's only when i click on the code in my first post, that it move the title to row 2.

Here is my full code where the issue could be:

Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String

    QuestionToMessageBox = "Want to proceed?"

    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Want to proceed?")

    If YesOrNoAnswerToMessageBox = vbNo Then
         MsgBox "Cancelled"
    Else
    
    Dim lngRow As Long
    Dim lngListLoop As Long
    Dim dicUnique As New Scripting.Dictionary
    Dim lngRowHeight As Long
    Dim lngCount As Long
    
    lngRowHeight = MSHFlexGrid2.RowHeight(0)
    For lngListLoop = 0 To List1.ListCount - 1
        If List1.Selected(lngListLoop) Then
            dicUnique.Add List1.List(lngListLoop), 1
        End If
    Next
    
    Me.MousePointer = vbHourglass
    MSHFlexGrid2.Redraw = False
   lngRow = 1

    Do While True
        If dicUnique.Exists(MSHFlexGrid2.TextMatrix(lngRow, 1)) Then
            MSHFlexGrid2.RowHeight(lngRow) = lngRowHeight
            lngCount = lngCount + 1
            lngRow = lngRow + 1
        Else
            MSHFlexGrid2.RemoveItem (lngRow)
        End If
        If lngRow = MSHFlexGrid2.Rows Then Exit Do

    Loop
    
    MSHFlexGrid2.Redraw = True
    Me.MousePointer = vbDefault
    dicUnique.RemoveAll
    Set dicUnique = Nothing

        'Count de ligne dans le grid
        Dim zz As Long, txt As String, total1 As Long
For zz = 1 To MSHFlexGrid2.Rows - 1
  If Len(MSHFlexGrid2.TextMatrix(zz, 3)) Then total1 = total1 + 1

Next zz
lblTotalrecord1 = CStr(total1)

  If lblTotalrecord <> "" And lblTotalrecord1 <> "" Then
        Command5.Enabled = True
        Command7.Enabled = True

    Else
        Command5.Enabled = False
        Command7.Enabled = False
    End If


    With MSHFlexGrid2
       .ColSel = 1
       .Sort = flexSortStringAscending
    End With
    




    MsgBox "Filtered Rows: " & lngCount
    MsgBox "List filtered"
    End If
    
     'NEED TO BE FIX
        Dim yy As Long, txt11 As String, total11 As Long
For yy = 1 To MSHFlexGrid2.Rows - 1
On Error Resume Next
  If (MSHFlexGrid2.TextMatrix(yy, 3)) = "NEED TO BE FIX" & (MSHFlexGrid2.TextMatrix(yy, 1)) = List1.Text Then total11 = total11 + 1

Next yy
need_to_fix1 = CStr(total11)

'Affiché le nombre de need to be fix
Amount1.Visible = True
need_to_fix1.Visible = True

Open in new window

Wilder1626

ASKER

If this can help, this is the code to file the grid:

'HEADING FOR GRID
    MSHFlexGrid2.Clear
    MSHFlexGrid2.Rows = 2
    MSHFlexGrid2.Cols = 2
    
    Label12.Caption = ""
    
    
    Dim xlObject     As Excel.Application
    Dim xlWb         As Excel.Workbook
    Dim NoOfRows     As Long
    Dim NoOfColumns  As Long
    
    Dim vArray As Variant
    Dim lngRow As Long
    Dim lngCol As Long
    Dim dicUnique As New Scripting.Dictionary
    Dim vItem As Variant
    
    On Error GoTo MyErrHandler
   
    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (xlam, xlsx, xltm, xlt, xlsm, xltx, xls, txt, csv)"
        .FileName = "*.xl*"
        .InitDir = "C:\Documents and Settings\all users\Desktop"
        .ShowOpen
        If Not .FileName = "" Then
            Set xlObject = New Excel.Application
            Set xlWb = xlObject.Workbooks.Open(.FileName)

            'Clipboard.Clear
            'xlObject.Cells.Copy     ' Copy all cells in active worksheet.
            FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheet, NoOfRows, NoOfColumns
            With xlObject.ActiveWorkbook.ActiveSheet
                vArray = .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell))
            End With
            With MSHFlexGrid2
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               '.Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               
               For lngRow = 1 To NoOfRows
                 For lngCol = 1 To NoOfColumns
                    .TextMatrix(lngRow - 1, lngCol - 1) = vArray(lngRow, lngCol)
                 Next
               Next
               .Redraw = True      'Now draw
            End With
            xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
            xlWb.Close
            xlObject.Application.Quit
            Set xlWb = Nothing
            Set xlObject = Nothing
        End If
    End With
    
    'Hide amount need to fix
    Amount1.Visible = False
    need_to_fix1.Visible = False
  
     Command3.Enabled = True
    Command4.Enabled = True
    
         'Count
        Dim zz As Long, txt As String, total1 As Long
For zz = 1 To MSHFlexGrid2.Rows - 1
  If Len(MSHFlexGrid2.TextMatrix(zz, 3)) Then total1 = total1 + 1

Next zz
lblTotalrecord1 = CStr(total1)

  If lblTotalrecord <> "" And lblTotalrecord1 <> "" Then
        Command5.Enabled = True
        Command7.Enabled = True

    Else
        Command5.Enabled = False
        Command7.Enabled = False
    End If


    With MSHFlexGrid2
       .ColSel = 1
       .Sort = flexSortStringAscending
    End With




     
    'Concatenate
    Dim k As Long
    
    For k = 1 To MSHFlexGrid2.Rows - 1
    
        If MSHFlexGrid2.TextMatrix(k, 1) <> "" Then
           MSHFlexGrid2.TextMatrix(k, 15) = MSHFlexGrid2.TextMatrix(k, 0) & MSHFlexGrid2.TextMatrix(k, 4) & MSHFlexGrid2.TextMatrix(k, 1) & MSHFlexGrid2.TextMatrix(k, 6) & MSHFlexGrid2.TextMatrix(k, 2) & MSHFlexGrid2.TextMatrix(k, 7) & MSHFlexGrid2.TextMatrix(k, 8) & MSHFlexGrid2.TextMatrix(k, 9) & MSHFlexGrid2.TextMatrix(k, 10)
        End If
    
    Next k
    
    
   

    
    
     'Auto column fit
    Dim r As Long
    Dim C As Long
    Dim cell_wid As Single
    Dim col_wid As Single

    For C = 0 To MSHFlexGrid2.Cols - 1
        col_wid = 0
        For r = 0 To MSHFlexGrid2.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid2.TextMatrix(r, C))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next r
        MSHFlexGrid2.ColWidth(C) = col_wid + 120
    Next C
    
'MSHFlexGrid2.ColWidth(11) = 0
'MSHFlexGrid2.ColWidth(12) = 0
'MSHFlexGrid2.ColWidth(13) = 0
'MSHFlexGrid2.ColWidth(14) = 0
'MSHFlexGrid2.ColWidth(15) = 0
    
    
     'Fill listbox with unique values from first column in flexgrid
    Me.List1.Clear
    For lngRow = 1 To NoOfRows
        vItem = MSHFlexGrid2.TextMatrix(lngRow, 1)
        If dicUnique.Exists(vItem) Then
        Else
            dicUnique.Add vItem, 1
            List1.AddItem vItem
        End If
    Next
    dicUnique.RemoveAll
    Set dicUnique = Nothing


Amount1.Visible = False
need_to_fix1.Visible = False

'MSHFlexGrid2.ColWidth(11) = 0
'MSHFlexGrid2.ColWidth(12) = 0
'MSHFlexGrid2.ColWidth(13) = 0
'MSHFlexGrid2.ColWidth(14) = 0
'MSHFlexGrid2.ColWidth(15) = 0


    Exit Sub
    



MyErrHandler:
    Err.Clear

Open in new window

Brook Braswell

Wilder,
It appears to me that you simply have an Extra row in your header rather than the header as the second row...
place this in the code likely after your Do Loop

MSHFlexGrid2.RowHeight(0) = 0

Wilder1626

ASKER

Hello Brook1966

If i'm doing this:

   Do While True
        If dicUnique.Exists(MSHFlexGrid2.TextMatrix(lngRow, 1)) Then
            MSHFlexGrid2.RowHeight(lngRow) = lngRowHeight
            lngCount = lngCount + 1
            lngRow = lngRow + 1
        Else
            MSHFlexGrid2.RemoveItem (lngRow)
        End If
        If lngRow = MSHFlexGrid2.Rows Then Exit Do

    Loop
    
    MSHFlexGrid2.RowHeight(0) = 0

Open in new window

Now, there's no more name column.

ASKER CERTIFIED SOLUTION

Brook Braswell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Oh yes. I like this

Now it work. Thanks