Consulting

Results 1 to 6 of 6

Thread: Filtered visible cells to Userform as row source.

  1. #1

    Filtered visible cells to Userform as row source.

    Hi, i try to figured out, how to load my filtered data to userform by rows (as row source). I googled and found some code, which works almost good. My data are numbers, text, blanks cells, dates.
    I have 2 issues.
    1.)
    My data has 12 columns, but when i use my code it stops (error) on row
    .List(.ListCount - 1, 10) = oneCell.Offset(0, 10).Value
    2.)
    When i cut (fix) my code(not for 12 but only for 9 columns) , then it works without error, but it make each row from my data as row and also as column and of course i miss my last 3 columns. I mean, that my data are appeared like this:

    e.g. my filtered visible source data are
    1,2,3,4,5,6
    7,8,9,10,11,12

    in user form are shown inccorrect as
    1, 2, 3, 4, 5, 6
    2, 3, 4, 5, 6
    3, 4, 5, 6
    4, 5, 6,
    5, 6
    6
    7,8,9,10,11,12
    8,9,10,11,12
    9,10,11,12
    10,11,12
    11,12
    12


    i want it in user form
    exactly as it are in source
    1,2,3,4,5,6
    7,8,9,10,11,12


    Run time error 380: Could not set list of property...

    Any suggestions pls.?
    thx for all help

    This is my code:

    Private Sub UserForm_Initialize()
    
    Dim cell As Range
    Dim MyArrP As Variant, i As Long
    Dim CIF As Variant, Rowz
    Dim LogExist As Variant
    Dim LOGYtab As Range
    Dim LR As Variant
    
    CIF = Workbooks("Preh.xlsm").Sheets("view").Range("B1").Value
    With Workbooks("Preh.xlsm").Sheets("zrkadlo")
    .Unprotect
    .Range("$A$3:$FR$100000").AutoFilter Field:=162, Criteria1:=CIF
    End With
    
    Dim oneCell As Range
    
      LR = Workbooks("Preh.xlsm").Sheets("zrkadlo").Range("FF65536").End(xlUp).Row
    
    
    With Workbooks("Preh.xlsm").Sheets("zrkadlo")
    .Unprotect
    
    
    'With ListBox_Prechodne     'FUNKCNE --------------------
    '     For Each oneCell In Sheets("zrkadlo").Range("ff4:f" & LR).SpecialCells(xlCellTypeVisible)
    ''     For Each oneCell In Sheets("zrkadlo").Range("ff4:F100000").SpecialCells(xlCellTypeVisible)
    '        .AddItem CStr(oneCell.Value)
    '    Next oneCell
    'End With
    
    With ListBox_Prechodne
      .ColumnCount = 13
         For Each oneCell In Sheets("zrkadlo").Range("ff4:fQ" & LR).SpecialCells(xlCellTypeVisible)
    '     For Each oneCell In Sheets("zrkadlo").Range("ff4:F100000").SpecialCells(xlCellTypeVisible)
            .AddItem CStr(oneCell.Value)
             .List(.ListCount - 1, 1) = oneCell.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = oneCell.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = oneCell.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = oneCell.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = oneCell.Offset(0, 5).Value
            .List(.ListCount - 1, 6) = oneCell.Offset(0, 6).Value
            .List(.ListCount - 1, 7) = oneCell.Offset(0, 7).Value
            .List(.ListCount - 1, 8) = oneCell.Offset(0, 8).Value
            .List(.ListCount - 1, 9) = oneCell.Offset(0, 9).Value
           .List(.ListCount - 1, 10) = oneCell.Offset(0, 10).Value
           .List(.ListCount - 1, 11) = oneCell.Offset(0, 11).Value
           .List(.ListCount - 1, 12) = oneCell.Offset(0, 12).Value
        Next oneCell
    End With
    
    '.ShowAllData
    End With
    'oneCell = ""
    
    End Sub

  2. #2
    My guess, and it is only a guess since you have not provided a workbook to examine or test, is that the range you are looping though


    Sheets("zrkadlo").Range("ff4:fQ" & LR).SpecialCells(xlCellTypeVisible)

    contains cells that are not in column FF, so your "offset" statement oneCell.Offset(0, n).Value (where n = 1-12) is not starting from where you think it is, which gives you the odd looking result. Does it work if you limit the additem to only cells in Column FF?


            
          With ListBox_Prechodne
                .ColumnCount = 13
                For Each onecell In Sheets("zrkadlo").Range("ff4:fQ" & LR).SpecialCells(xlCellTypeVisible)
                    '     For Each oneCell In Sheets("zrkadlo").Range("ff4:F100000").SpecialCells(xlCellTypeVisible)
    
    
                    If Split(onecell.Address, "$")(1) = "FF" Then
                        .AddItem CStr(onecell.Value)
                        .List(.ListCount - 1, 1) = onecell.Offset(0, 1).Value
                        .List(.ListCount - 1, 2) = onecell.Offset(0, 2).Value
                        .List(.ListCount - 1, 3) = onecell.Offset(0, 3).Value
                        .List(.ListCount - 1, 4) = onecell.Offset(0, 4).Value
                        .List(.ListCount - 1, 5) = onecell.Offset(0, 5).Value
                        .List(.ListCount - 1, 6) = onecell.Offset(0, 6).Value
                        .List(.ListCount - 1, 7) = onecell.Offset(0, 7).Value
                        .List(.ListCount - 1, 8) = onecell.Offset(0, 8).Value
                        .List(.ListCount - 1, 9) = onecell.Offset(0, 9).Value
                        .List(.ListCount - 1, 10) = onecell.Offset(0, 10).Value
                        .List(.ListCount - 1, 11) = onecell.Offset(0, 11).Value
                        .List(.ListCount - 1, 12) = onecell.Offset(0, 12).Value
                    End If
                Next onecell
            End With

  3. #3
    Nice it worked OK with on error resume next. It shows data as i wish. Mainly .

    Form is ok, my data are in rows as i want and only onec. But It still occurs error on the line
      .List(.ListCount - 1, 10) = onecell.Offset(0, 10).Value
    So i insert
    On error resume next
    Now it works ok, but still i miss my columns 10, 11, and 12.
    In column, 10 where return errors i have date in this format:
    16. 5. 2017 12:24:34
    Strange is, that this error (when i try to debug code) show on row 10, 11 and 12. So maybe its not the date, but maybe is problem 2 digits column definition (10,11,12......). Only guess.


    Why its problem for my code read this format?
    When i select with my mouse cursor on command
    .List(.ListCount - 1, 10)
    , it shows
    this
    "Could not get list property.Invalid argument"

  4. #4
    Now i googled, that i can have only 10 columns when i use AddItem
    So i will try googled solutionf for my 12 columns

  5. #5
    AddItem is not the only way to populate a listbox. If instead of using AddItem, you load your data into an array and then assign the array to the .list property you should be able to get around the 10 column limit.

  6. #6
    OK, thx. I will try to find some examples, that i see how to make it this way (array).
    thx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •