Error! Could not get the column property, Invalid argumnet

z-eighty2

New member
Joined
Feb 13, 2014
Messages
2
Reaction score
0
Points
0
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

 

Attachments

  • Book A1.xlsm
    28.1 KB · Views: 56

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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.
 
Top