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

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
204
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
 
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
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
Hi Dave when I tried using the row source I get everything shown other than using the search match data.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top