Populate Userform from Listbox Record

Hello. I am new to this forumn and excel vba but having a great time learning

I am trying to create a Userform with inputs populated to a Listbox. My "add new record" populates fields to the Listbox/database and 1st few Userform fields populate when clicking a Listbox record but not all.

Below is the code I am using to initialize and Listbox click. All Userform fields populate when clicking a Listbox record but State.Value and any after do not populate and I get a

"Could not get column property. invalid property argument"


Private Sub ListBox1_click()
Client.Value = ListBox1.Column(0)
Officer.Value = ListBox1.Column(1)
Documentor.Value = ListBox1.Column(2)
Revenue.Value = ListBox1.Column(3)
Naics.Value = ListBox1.Column(4)
Contact.Value = ListBox1.Column(5)
Title.Value = ListBox1.Column(6)
Telephone.Value = ListBox1.Column(7)
Address.Value = ListBox1.Column(8)
City.Value = ListBox1.Column(9)
State.Value=Listbox1.column(10) <State.value and any after do not populate>
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
Dim X As Integer
On Error Resume Next
For i = 0 To 12
Me.ListBox1.AddItem
For X = 1 To Sheet4.Range("A" & Rows.Count).End(xlUp).Row
Me.ListBox1.List(i - 1, X - 1) = Sheet4.Cells(i, X)
Next X
Next i
End Sub

Thanks in advance

Hi. Try:

Private Sub UserForm_Initialize()

    With ThisWorkbook.Sheets("Sheet4")

        ' Fill ListBox
        Me.listBox1.List = .Range("A2:L" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
    End With

End Sub

Excellent. All Userform fields populate from selected Lisbox record and Add New Works

I think I have one last function I need help with. Need to be able to update the database when a userform field is changed.

I tried the following but no luck

Thanks

Private Sub CommandButton2_Click()
If Me.Client.Value = "" Then
MsgBox "Select Record"
Else
For X = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(X) = True Then
ListBox1.Selected(X) = False

Dim checkrows
Sheet4.Activate
checkrows = Sheet4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Client.Value = Cells(checkrows, 1)
Officer.Value = Cells(checkrows, 2)
End If
Next
End Sub

Originally Posted by torachan

attached the basics of simple (Add,Update,Delete) procedures for data handling of an elementary pseudo database.
please look at the forum rules and put your code between code tags (# code #) as per posts 2 & 3.
to enter new data first press 'Clear' - the rest should be intuitive.

nice. is possible to add search by name. thank

Thanks for the reply

I modified the code to match my textbox names but am getting an "Object Required" error

Do I need to change "TB11" in the with statement. what is TB11?

I am so close so thanks in advance

Private Sub CommandButton2_Click()
If Me.Client.Text = "" Then
MsgBox "No Record selected", , "Errors"
Exit Sub
End If
With tb11.ListRows(sc + 1)
.Range(2).Value = Client.Value
.Range(3).Value = Officer.Value
.Range(4).Value = Documentor.Value
End With
MsgBox ("Details Changed"), vbOKOnly + vbInformation, "SAVED"
End Sub

Don't want my knuckles slapped. Just very new but getting there.

Thanks eveyrone

Hello again.

With everyone's help I have completed all but one piece (not including formatting textboxes) Have attached file

I still am having difficulty getting a record to update from the userform. I recieved one reply but could not get it to work

Here is the code on the update buttion. The goal is to select a record on the listbox and then update by the corresponding text box.


Private Sub CommandButton2_Click()
If Me.Client.Text = "" Then
MsgBox "No Record selected", , "Errors"
Exit Sub
End If
With Sheet1.ListRows(.ListIndex + 1)
.Range(2).Value = Client.Value
.Range(3).Value = Officer.Value
.Range(4).Value = Documentor.Value
End With

MsgBox ("Details Changed"), vbOKOnly + vbInformation, "SAVED"
End Sub

Hello torachan

Your "bargain" is fair and appreciated. I am new to VBA and this forum and the responses are invaluable to my completing the project by the deadline onf next Monday. After project is complete I will read the forum rules to comply.

The "No Record Selected" msgbox works but when record selcted in list box get "Object Does Not Support this Method or Property"

Below is my modified code and code tag which I hope I did correctly to get the second "clue"

Sure hope I have done this correctly to earn all clues so I can complete this weekend

Thanks


Private Sub CommandButton2_Click()

If Me.Client.Text = "" Then
MsgBox "No Record selected", "Errors" 'if client textbox is blank no listbox record selected. display "No Record Selected"
Exit Sub
End If
With Worksheets("Sheet1").ListRows.ListIndex + 1 'go to record corresponding to record selected in the listbox
.Range(2).Value = Client.Value 'populate database and corresponding listbox recored based on change to textbox
.Range(3).Value = Officer.Value
.Range(4).Value = Documentor.Value
End With
MsgBox ("Details Changed"), vbOKOnly + vbInformation, "SAVED" 'msgbox confirming change made

End Sub

Code pasted did not inlcude code tag format. Any suggestions?

Private Sub CommandButton2_Click()

If Me.Client.Text = "" Then
MsgBox "No Record selected", , "Errors" 'if client textbox is blank no listbox record selected. display "No Record Selected"
Exit Sub
End If
With Worksheets("Sheet1").ListRows.ListIndex + 1 'go to record corresponding to record selected in the listbox
.Range(2).Value = Client.Value 'populate database and corresponding listbox recored based on change to textbox
.Range(3).Value = Officer.Value
.Range(4).Value = Documentor.Value
End With
MsgBox ("Details Changed"), vbOKOnly + vbInformation, "SAVED" 'msgbox confirming change made

End Sub

As a newbie I am not familiar with everything and will have to give up since I have tried desperately to understand the code tag on the internet. I understand your frustration him my lack of skills

Everyone has been so helpful and patient helping a newbie through learning and giving instructions.

If this is not what you require thank you for the time you spent responding.

VBA newbie desperate to complete my project for my employer


Private Sub CommandButton2_Click()

If Me.Client.Text = "" Then
MsgBox "No Record selected", , "Errors"
Exit Sub
End If

With Sheet1.ListRows(.ListIndex + 1)
.Range(2).Value = Client.Value
.Range(3).Value = Officer.Value
.Range(4).Value = Documentor.Value

End With

MsgBox ("Details Changed"), vbOKOnly + vbInformation, "SAVED"
End Sub

Okay. Did not show up on the last post like in the review post section. Sorry for taking your time but thanks

I am desperate to complete my project by end of day tomorrow and would REALLY appreciate your help.

I have attached a new file which I used your InvSystem file and attempted to modify it for my sheet./fields

Am still getting a few errrors. Did remove ClearForm code since I do not need

WOULD GREATLY APPRECIATE you looking at the attached file to see what I am missing. YOU ARE MY LAST HOPE

Please consider

thanks