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
 

hsolanki

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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

dmt32

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

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
Hi Dave when I tried using the row source I get everything shown other than using the search match data.
 

dmt32

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

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

Forum statistics

Threads
1,144,580
Messages
5,725,099
Members
422,590
Latest member
Mikeyyy

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