Results 1 to 2 of 2

Thread: Error! Could not get the column property, Invalid argumnet

  1. #1

    Error! Could not get the column property, Invalid argumnet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello EveryOne!

    I have got a UserForm which pulls data from Excel sheet and show it on UserForm.

    UserForm1 has TextBox's as follow:

    ComboBox1.......Select Year
    ComboBox2.......Select Batch
    TextBoxYear........Showing Year
    TextBoxBatch...... Showing Batch number
    TextBox1 ........... Showing Month 1 data
    TextBox2.............Showing Month 2 data
    TextBox3...............Showing Month 3 data
    .
    .
    .
    .
    TextBox12...........Showing Month 12 data



    It shows data correctly in UserForm1 for TextBoxYear, TextBoxBatch and TextBox1 to TextBox7 but when it reaches TextBox8 (Month 8) it give error "Could not get Column property, Invalid argument"

    and it does not show data in TextBox8 (Month 8) .......to..........TextBox12 (Month 12) and give error in code from
    ".TextBox8 = ListBox1.Column(10)"

    I have attached workbook with userform.

    Please Help
    Kind Regards

    z-eighty2

    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    While I'd question the motive for doing things this way.... It is what you asked. Try changing your ComboBox2_Click() to this

    Code:
    Private Sub ComboBox2_Click()
    
        Dim lr as Long
        Dim x as Long
        Dim sh As Worksheet
        Dim RowToUse As Long
        Dim vData As Variant
        
        Set sh = Sheets("Sheet1")
    
    With sh
        lr = .Range("A" & Rows.Count).End(xlUp).Row 'Determine last row
        
        For x = 2 To lr
            If ((.Cells(x, 2) = ComboBox1.Text) And (.Cells(x, 3) = ComboBox2.Text)) Then RowToUse = x
        Next x
    
            With .Range(.Cells(RowToUse, 2), .Cells(RowToUse, 15))
               vData = .Value
            End With
             
                With ListBox1
                    .Clear
                    .ColumnCount = 3   'the number of columns
                    .ColumnWidths = "40;40;55" 'set the column widths
                    .List = vData
                End With
     End With
    
    End Sub
    and then changing list box columns in ListBox1_Click() to be 0 thru 13 rather than 1 thru 14.

Posting Permissions

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