0

I'm very new to VBA. I'm trying to populate 14 different text boxes on a form every time a user selects a row in the Listbox on the same form. However, I get the below error message every time.

Error! Could not get the column property, Invalid argument

I did some research and I see that it may be because I have more than 10 columns and to use an array instead, but I'm not sure how to implement this. Here's my code

Private Sub LoadData()
Dim strIndvdlID As String

With Me.lbAudit
    CurrentListIndex = .ListIndex

    Me.txtGUID.value = .Column(COL_GUID, .ListIndex)
    strIndvdlID = .Column(COL_iNDVDLID, .ListIndex)
    Me.txtLocationID.value = .Column(COL_LocationID, .ListIndex)
    Me.txtName.value = .Column(COL_Name, .ListIndex)
    Me.txtDataLead.value = .Column(COL_DataLead, .ListIndex)
    Me.txtHML.value = .Column(COL_HML, .ListIndex)
    Me.txtDirector.value = .Column(COL_Director, .ListIndex)
    Me.txtManager.value = .Column(COL_Manager, .ListIndex)
    Me.txtLead.value = .Column(COL_Lead, .ListIndex)
    Me.txtType.value = .Column(COL_Type, .ListIndex)
    Me.txtPlannedStartDate.value = .Column(COL_PlannedStartDate, .ListIndex)
    Me.txtPlannedEndDate.value = .Column(COL_PlannedEndDate, .ListIndex)
    Me.txtActualStartDate.value = .Column(COL_ActualStartDate, .ListIndex)
    Me.txtActualEndDate.value = .Column(COL_ActualEndDate, .ListIndex)     
End With
End Sub
C-Lo
  • 59
  • 8
  • 2
    You have a missing comma here (COL_GUID .ListIndex) but what was the error message ? – CDP1802 Mar 15 '20 at 08:05
  • You say „I get the below error message every time”. What „below”? Was the problem only that comma missing, which CDP1802 observed? – FaneDuru Mar 15 '20 at 10:22
  • Hi @FaneDuru I have updated the post with the error message I get. The problem does not seem to be the missing Comma. When I take out the code lines from the "PlannedStartDate" to the "ActualEndDate" it work as expected, so maybe the issue is the number of columns? I'm just not sure how to resolve it. Thanks. – C-Lo Mar 15 '20 at 16:44
  • 1
    Index numbers start at 0 so for the first column the COL_ constant should be 0. The 10 column limit applies to putting data in not reading it out. – CDP1802 Mar 15 '20 at 16:47
  • 1
    Suggest to show how you assigned your listbox by **editing** your post (not replying via comment). My assumption is that you didn't use the array method - c.f. [populate listbox with multiple columns](https://stackoverflow.com/questions/47528558/vba-excel-populate-listbox-with-multiple-columns/47531440#47531440) ... the assignment can be done by using the both the `.List` & the `.Column` property. :-) – T.M. Mar 15 '20 at 18:14
  • 1
    Thanks All. I figured it out. The issue was with my listbox column. I had 1 less column in my listbox than i was trying to read out. – C-Lo Mar 17 '20 at 04:43

0 Answers0