Error when double click on listbox1 to populate date on to boxes

[hsolanki]

Hello everyone

i have a user form whereby it adds data from vary text boxes and then from textbox 43 from userform when you type the names from column B it shows relevent information on the Listbox1 and then when you double click on the name on listbox1 it comes up an error halfway through the code on the column at "L" below it is the code placed on listbox1
when you double click on the listbox1 it populate information from textbox1 up to testbox10 then i get an error

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Full")
If IsNull(Me.ListBox1.Value) Then
MsgBox "Please double click on the name line", vbExclamation, "Select a Name Line"
    Unload Me
        myForm.Show
    Else

sh.Unprotect "Bhaji2020"

    If Me.ListBox1.List(Me.ListBox1.ListIndex, 0) <> "" Then
   
    Me.CommandButton1.Enabled = False
     Me.CommandButton2.Enabled = True
     Call Refresh_Data
  
    Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
    Me.TextBox1.Enabled = False
    Me.TextBox1.BackColor = RGB(155, 295, 155)
   
        Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
        Me.TextBox2.Enabled = False
        Me.TextBox2.BackColor = RGB(155, 295, 155)
   
 
    Me.TextBox3.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 2), "DD/MM/YYYY")
    Me.TextBox3.Enabled = False
    Me.TextBox3.BackColor = RGB(155, 295, 155)
  
   
    Me.TextBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
    Me.TextBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
    Me.TextBox7.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
    Me.TextBox8.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)
    Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)
    Me.TextBox9.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 8)
    Me.TextBox10.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 9), "DD/MM/YYYY")
   
    '''FROM HERE I GET AN ERROR CODE'' "Could not get the List property. Invalid argument"

'''''========= Validation for Empty boxes used for Email stamps ===========''''
        Me.TextBox37.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 10)
        Me.TextBox38.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 10)
        Me.TextBox39.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)
        Me.TextBox40.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 22)
        Me.TextBox41.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 30)
                '===================================================
       
   
       Me.TextBox11.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 11)
             Me.TextBox12.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 12), "DD/MM/YYYY")
           
                   
    Me.TextBox13.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 15)
     Me.TextBox13.Enabled = False
    Me.TextBox13.BackColor = RGB(155, 295, 155)
   
    Me.TextBox14.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 16), "DD/MM/YYYY")
    Me.TextBox14.Enabled = False
    Me.TextBox14.BackColor = RGB(155, 295, 155)
   
        Me.TextBox15.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 17)
            Me.TextBox16.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)
                Me.ComboBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 19)
               
Me.TextBox17.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 20), "DD/MM/YYYY")
    Me.TextBox18.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 21), "DD/MM/YYYY")
        Me.ComboBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 23)
            Me.TextBox19.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 24), "DD/MM/YYYY")
                Me.TextBox20.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 25)
                    Me.TextBox21.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 26)
                        Me.TextBox22.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 27)
                             Me.TextBox23.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 28)
                                 Me.TextBox24.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 29)
                                     Me.TextBox25.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 31), "DD/MM/YYYY")
                                         Me.TextBox26.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 32)
                                        
         Me.TextBox27.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 33), "DD/MM/YYYY")
          Me.TextBox28.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 34)
            Me.ComboBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 35)
             Me.TextBox29.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 36), "DD/MM/YYYY")
                Me.ComboBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 37)
                        Me.TextBox30.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 38)
                          Me.TextBox31.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 39)
                         Me.ComboBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 40)
                            Me.TextBox32.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 41)
                    Me.ListBox1.ForeColor = vbBlue
           
                   
End If

End If

sh.Protect "Bhaji2020"

End Sub

 

[hsolanki]

Hi Dave

Hi,

I have had a play & made a vein attempt to correct your code to resolve the issues you are having but in resolving one part another problem arose so decided best approach was to delete it all & start again butI do have concern that in attempting to make this part work you still have other tables in your project that have no relationships specified & presumably at some stage, you will want to include these as well?

if you were struggling with this part of your project, you are likely to continue having issues given that you will need to figure out what I have done and as you have stated, you have a limited understanding of VBA

I still assert that rather than continue trying make a spreadsheet application do what you want, take time out & look at Access, there are plenty of free tutorials & you will be amazed how quickly you can link tables up etc

I am pre-occupied with other tasks this week but if do get time, will see if I can resolve this part of your proj

Hi,

I have had a play & made a vein attempt to correct your code to resolve the issues you are having but in resolving one part another problem arose so decided best approach was to delete it all & start again butI do have concern that in attempting to make this part work you still have other tables in your project that have no relationships specified & presumably at some stage, you will want to include these as well?

if you were struggling with this part of your project, you are likely to continue having issues given that you will need to figure out what I have done and as you have stated, you have a limited understanding of VBA

I still assert that rather than continue trying make a spreadsheet application do what you want, take time out & look at Access, there are plenty of free tutorials & you will be amazed how quickly you can link tables up etc

I am pre-occupied with other tasks this week but if do get time, will see if I can resolve this part of your project.



Dave

Hi Dave

No problem, Many thanks for quick reply and giving it go

i also was reading it on forms and it appears that listbox restricted to only max 10 columns

 

[dmt32]

Hi Dave


i also was reading it on forms and it appears that listbox restricted to only max 10 columns

you can only have up to 10 columns when using the AddItem method in your code.
If you have more than 10 columns then use either the List or RowSource properties.
You would also need to use the RowSource property if displaying the ColumnHeads in the ListBox is required.

Dave

 

[hsolanki]

Hi Dave when I tried using the row source I get everything shown other than using the search match data.

 

[dmt32]

Hi Dave when I tried using the row source I get everything shown other than using the search match data.

As Rowsource is linked to the sheet you will, if filtering data, need to copy the filtered data to a separate sheet & make rowsource connection it.
Also need to be mindful that if you want to delete a record on a sheet that rowsource is connected to, you first will need to disconnect the link .

whilst this is all workable, most tend to avoid using rowsource preferring when headers are required, to place labels above the listbox.

Dave

 

[hsolanki]

As Rowsource is linked to the sheet you will, if filtering data, need to copy the filtered data to a separate sheet & make rowsource connection it.
Also need to be mindful that if you want to delete a record on a sheet that rowsource is connected to, you first will need to disconnect the link .

whilst this is all workable, most tend to avoid using rowsource preferring when headers are required, to place labels above the listbox.

Dave

Hi Dave sorry I wouldn't be a be able to do all this it is way beyond my knowledge and being capable of doing.