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

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
Morning everyone I wonder if anyone kindly point me in the right direction where I am going wrong 😒

Thanks
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
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.


Thank you once again Dave :)
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,148,244
Messages
5,745,596
Members
423,964
Latest member
Rayds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top