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]

Morning everyone I wonder if anyone kindly point me in the right direction where I am going wrong ?

Thanks

 

[dmt32]

Morning everyone I wonder if anyone kindly point me in the right direction where I am going wrong ?

Thanks

Hi,
with a complex project its helpful to forum if you can place copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it.

Dave

 

[hsolanki]

Hi Dave Sorry. Please see below sample book. whereby when you search name through textbox 43 on top left in the userform, it shows data in the listbox which when you are then double click on the list box i get an error half way through the listbox.

Data Entry Application in VBA v6.xlsm

drive.google.com

Thank you once again Dave

 

[dmt32]

Hi,

I have had a quick glance at your project & can see that you have put a lot of effort in to it but as a personal observation, I would say what you are trying to do would probably better suited to Access database.

Access is a Microsoft database management system (DBMS). It’s relational database engine that can be used for both small and large database systems. In Excel, worksheets are flat. This means they are not related at multiple levels, databases in Access are related & can be easily shared. The advantage of a DBMS is that it’s easy to store information in one place and refer to it in other places. A table can simply be referenced in other tables and when the data changes, it’s automatically updated in all the referenced places.

Access makes much easier in creating forms and features so you can easily enter records whereas in Excel and as you are discovering, userforms have to be fully coded by the developer to achieve the required functionality – for complex projects like yours, they can be a lot of work.

It’s really your choice but before you go much further with your project in Excel suggest take a look here for some guidance:MS Access Tutorial - Tutorialspoint

& If want to progress with Access, contact the Access Forum here for assistance.

Dave

 

[hsolanki]

Hi Dave Many thanks but unfortunately i am not too familiarized with the MS access therefore i am going have to learn this perhaps my next project. but this one i need to submit this quite soon and struggling with this few error on this.

i wold appreciate if you kindly be able to point me in this right direction of where i have gone on this.

and i am quite keen to know more about the MS Access for my next project. and I will definitely start looking into once i have completed this current project.

 

[dmt32]

If I get an idle moment I will take a look at it further - I would add that when developing a userform with so many controls its simplifies coding to list all the input controls on a worksheet & then read them into an array. The array can also include such things like if control is required entry by user, it's data type & any error message - This approach negates the need to continually hard code each individual control.

Dave

 

[hsolanki]

Hi Dave Many thanks once again, no problem.

I am quite fairly new to VBA and still making my way to get in to deeper of coding thought.

 

[hsolanki]

If I get an idle moment I will take a look at it further - I would add that when developing a userform with so many controls its simplifies coding to list all the input controls on a worksheet & then read them into an array. The array can also include such things like if control is required entry by user, it's data type & any error message - This approach negates the need to continually hard code each individual control.

Dave

Hi Dave

hope you had a good weekend, just wondering if you had a chance to have it look and sorry for this

 

[dmt32]

Hi Dave

hope you had a good weekend, just wondering if you had a chance to have it look and sorry for this

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