Populate Userform from Listbox Record

[bforster007]

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

[beyond Excel]

Hello. Your VBA code is -a bit- weird.

Also, you forgot to upload your workbook to the Forum to better understand what you have.

Anyway, it seems that your main mistake is the way to populate the listbox. Try one of the following two modes:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[MikeVol]

Hi. Try:

Please Login or Register  to view this content.

[bforster007]

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

[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.

[bforster007]

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

[torachan]

please look at the forum rules and put your code between code tags (# code #) as per posts 2 & 3

before the moderators smack your knuckles and close the post.
tbl1 is short reference to Table1 the data storage is in a 'structured table' far better than using ranges that you have to keep checking for size.

[bforster007]

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

Thanks eveyrone

[bforster007]

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

[torachan]

I will give you a part clue you are mixing up Sheet1 with "Sheet1" and are not recovering the row reference correctly.
Second part of the clue will be revealed after you have tidied up your post by editing and putting all code between code tags as required by forum rules.
Hows that for a bargain offer ?

[bforster007]

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

[bforster007]

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

[bforster007]

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

[torachan]

the request is simple enough - the code tags are the # in the menu bar above the quick reply box.
the reasoning is when the code is between the tags it keeps its formatting and can be copied into the VBA code window and debugged without all the formatting that copy/paste introduces.
it is a two second process - I will not copy code that is not uploaded properly.
your code should look like posts 2 & 3 as soon as I have something to work with I will look at it.

[bforster007]

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()

Please Login or Register  to view this content.

If Me.Client.Text = "" Then

Please Login or Register  to view this content.

MsgBox "No Record selected", , "Errors"
Exit Sub
End If

Please Login or Register  to view this content.

With Sheet1.ListRows(.ListIndex + 1)

Please Login or Register  to view this content.

.Range(2).Value = Client.Value

Please Login or Register  to view this content.

.Range(3).Value = Officer.Value

Please Login or Register  to view this content.

.Range(4).Value = Documentor.Value

End With

Please Login or Register  to view this content.

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

[bforster007]

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

[ex900]

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

[bforster007]

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

[torachan]

I am quite prepared to help, however I do not work to deadlines.
We as 'helpers' also get chastised if assisting when forum rules are not abided by, so far 'moderators' have not picked up your failure to enclose your code with code tags, I have already pre-empted this hoping you would correct this.
If you had conformed we would have been 3 or 4 days further on with all problems answered, as it is past my bedtime here in the UK I have only had a brief look, there is far too much to correct to give a workable solution.
You will note under my 'signature' I do not use 'rowsource' for data handling, I have noted that you have introduced it into your 'comboboxes' (despite 'others' views I have found it to be unstable and a source of errors if its interactions are not fully appreciated.) Please enclose all your code in all your posts with code tags as requested and we can move on.

[torachan]

@bforster007, as you have not responded I assume that a solution has been found or no longer required, I will therefore consign my file to the waste bin.