Textbox not filling from listbox

B

Binaface

Hello again :)

I have the following code that fills a listbox based on a selection from a
combobox. I want to make it so that when I select a record from the listbox,
the textboxes above it are populated so they can be edited, or the record can
be deleted. The textboxes will not fill when a record is selected, so the
form doesn't work.
When I use F8 to scroll through the code, it highlights my UserForm
Initialize, and the LoadAnimals sub, but I don't see what is wrong with
either of those. The form initializes just fine, and populates the listbox
just fine. How can I get the textboxes to populate based on a single selected
record in the listbox?

Here is the code:

Option Explicit
Private Source As Range
Private Index As Long
Private animals As String

Private Sub btnUpdate_Click()
Dim pointer As String
pointer = lstData.ListIndex
For Index = 1 To Source.Rows.Count
If Source.Cells(Index, 2) = Me.Type_cmb.Value Then
Source.Cells(Index, 1) = Trim(Me.Indx_tb.Value)
Source.Cells(Index, 3) = Trim(Me.Date_Entered_tb.Value)
Source.Cells(Index, 4) = Trim(Me.Animal_ID_tb.Value)
Source.Cells(Index, 5) = Trim(Me.DOB_tb.Value)
Source.Cells(Index, 6) = Trim(Me.ParceL_Number_tb.Value)
Source.Cells(Index, 7) = Trim(Me.Sire_ID_tb.Value)
Source.Cells(Index, 8) = Trim(Me.Dam_ID_tb.Value)
Source.Cells(Index, 9) = Trim(Me.Sex_tb.Value)
'Source.Cells(Index, 10) = Trim(Me.Age_of_Dam_tb.Value)
'Source.Cells(Index, 11) = Trim(Me.dam_weight_tb.Value)
'Source.Cells(Index, 12) = Trim(Me.Breed_tb.Value)
'Source.Cells(Index, 13) = Trim(Me.birth_weight_tb.Value)
'Source.Cells(Index, 14) = Trim(Me.weaning_weight_tb.Value)
Exit For
End If
Next
LoadData
lstData.ListIndex = pointer
End Sub

Private Sub Done_Click()
Unload Me
End Sub


Private Sub Type_cmb_change()
LoadData
End Sub

Private Sub cmdDelete_click()
If lstData.ListIndex = -1 Then Exit Sub
Dim Index As String
Dim msg As String
Index = Me.Indx_tb.Value
msg = lstData.List(lstData.ListIndex, 1)
msg = msg & "" & lstData.List(lstData.ListIndex, 2)
msg = msg & "" & lstData.List(lstData.ListIndex, 3)
msg = msg & "" & lstData.List(lstData.ListIndex, 4)
msg = msg & "" & lstData.List(lstData.ListIndex, 5)
msg = msg & "" & lstData.List(lstData.ListIndex, 6)
msg = msg & "" & lstData.List(lstData.ListIndex, 7)
msg = msg & "" & lstData.List(lstData.ListIndex, 8)
msg = msg & "" & lstData.List(lstData.ListIndex, 9)
'msg = msg & "" & lstData.List(lstData.ListIndex, 10)
'msg = msg & "" & lstData.List(lstData.ListIndex, 11)
'msg = msg & "" & lstData.List(lstData.ListIndex, 12)
'msg = msg & "" & lstData.List(lstData.ListIndex, 13)
'msg = msg & "" & lstData.List(lstData.ListIndex, 14)
If MsgBox(msg, vbYesNo + vbDefaultButton2, "DELETE #" & Index & "from" &
Type_cmb) = vbYes Then
RemoveItem Index
End If
End Sub

Private Sub RemoveItem(Index As String)
Dim found As Range
Dim OK As Boolean
With Worksheets("Manual Livestock Data")
For Each found In .Range(.Range("A7"), .Range("A7").End(xlDown))
If found = Index Then
OK = True
Exit For
End If
Next
End With
If OK Then
found.Resize(, 1).Delete xlShiftUp
LoadData
Else
MsgBox Index & "Not found!"
End If
End Sub

Private Sub lstdata_click()
With lstData
Me.Indx_tb.Value = .List(.ListIndex, 1)
Me.Type_cmb.Value = .List(.ListIndex, 2)
Me.Date_Entered_tb.Value = .List(.ListIndex, 3)
Me.Animal_ID_tb.Value = .List(.ListIndex, 4)
Me.DOB_tb.Value = .List(.ListIndex, 5)
Me.ParceL_Number_tb.Value = .List(.ListIndex, 6)
Me.Sire_ID_tb.Value = .List(.ListIndex, 7)
Me.Dam_ID_tb.Value = .List(.ListIndex, 8)
Me.Sex_tb.Value = .List(.ListIndex, 9)
'Me.Age_of_Dam_tb.Value = .List(.ListIndex, 10)
'Me.dam_weight_tb.Value = .List(.ListIndex, 11)
'Me.Breed_tb.Value = .List(.ListIndex, 12)
'Me.birth_weight_tb.Value = .List(.ListIndex, 13)
'Me.weaning_weight_tb.Value = .List(.ListIndex, 14)
End With
End Sub

Private Sub UserForm_Initialize()
With Worksheets("Manual Livestock Data")
Set Source = .Range(Range("A7"), .Range("AA7").End(xlDown))
End With
LoadAnimals
End Sub

Private Sub LoadAnimals()
Dim animal As New Scripting.Dictionary
For Index = 1 To Source.Rows.Count
animals = Source.Cells(Index, "B").Value 'this pulls the animal type from
table column 2
If Not animal.Exists(animals) Then
animal.Add animals, animals
Type_cmb.AddItem animals
End If
Next
End Sub

Private Sub LoadData()
Me.Sex_tb = ""
Me.Sire_ID_tb = ""
Me.birth_weight_tb = ""
Me.ParceL_Number_tb = ""
Me.Date_Entered_tb = ""
Me.Dam_ID_tb = ""
Me.weaning_weight_tb = ""
Me.Animal_ID_tb = ""
Me.Indx_tb = ""
Me.Age_of_Dam_tb = ""
Me.Breed_tb = ""
Me.DOB_tb = ""
Me.dam_weight_tb = ""
With lstData
..Clear
animals = Me.Type_cmb.Value
For Index = 1 To Source.Rows.Count
If animals = Source.Cells(Index, 2) Then
..AddItem Source.Cells(Index, 1)
..List(.ListCount - 1, 1) = Source.Cells(Index, 1)
..List(.ListCount - 1, 2) = Source.Cells(Index, 2)
..List(.ListCount - 1, 3) = Source.Cells(Index, 3)
..List(.ListCount - 1, 4) = Source.Cells(Index, 4)
..List(.ListCount - 1, 5) = Source.Cells(Index, 5)
..List(.ListCount - 1, 6) = Source.Cells(Index, 6)
..List(.ListCount - 1, 7) = Source.Cells(Index, 7)
..List(.ListCount - 1, 8) = Source.Cells(Index, 8)
..List(.ListCount - 1, 9) = Source.Cells(Index, 9)
'get an error when trying to run form due to these next five columns... argh
why?
'.List(.ListCount - 1, 10) = Source.Cells(Index, 10)
'.List(.ListCount - 1, 11) = Source.Cells(Index, 11)
'.List(.ListCount - 1, 12) = Source.Cells(Index, 12)
'.List(.ListCount - 1, 13) = Source.Cells(Index, 13)
'.List(.ListCount - 1, 14) = Source.Cells(Index, 14)
End If
Next
End With
End Sub

Thank you! And Happy Thanksgiving!
 
J

JLGWhiz

In response to your previous posting, it was pointed out that you are trying
to use a reserved word for a variable and VBA does not like that. The word
"Index" is a VBA property used with collections to designate the order of
precedence in referring to collection objects, i.e. Sheets(1), Shapes(3),
etc. When I tried to run your "Private Sub btnUpdate_Click()" sub, it threw
an error message that said the variable was trying to use an illegal word. I
suggest you change that term to some other word to represent your row
variable in the button click event code.
 
B

Binaface

Thank you for your response :)

I thought that the previous suggestion indicated that it did not like my
"Index_tb" that I had named one of my textboxes; as per the suggestion, I
changed the text box name to Indx_tb instead so it could not be confused with
Index. Where else did I need to change it? Sorry, this is all very new to me.
Thank you again!
 
J

JLGWhiz

Perhaps I was not clear in the first posting. I should have started with the
Dim statement where you declare Index As Long. That statement and anywhere
in your code that you use that variable should be changed. Otherwise, you
will enccounter strange problems because VBA sees "Index" as a specifically
defined key word that is used in conjunction with collections. I believe you
consistently used it as a row value where you used Cells(Index, 2), etc.
 
B

Binaface

Hello again :)
I have changed Index to "NDEX" in the coding, although I left the .ListIndex
the same as I thought this was a VBA command. I ran the code again, and I
don't get an application error anymore, but the textboxes still do not
populate from the selected record in the listbox. When I use F8 to scroll
through the code, I get highlights in the Initialize and LoadTypes subs, but
I am not sure why. I triple-checked the named ranges and worksheets names to
ensure it wasn't a simple typing error, and that is not the case.
Here is the updated code:

Option Explicit
Private Source As Range
Private NDEX As Long
Private animals As String

Private Sub btnUpdate_Click()
Dim pointer As String
If Animal_ID_tb.Value = "" Then Exit Sub
pointer = lstData.ListIndex
For NDEX = 1 To Source.Rows.Count
If Source.Cells(NDEX, 2) = Me.Type_cmb.Value Then
Source.Cells(NDEX, 1) = Trim(Me.Indx_tb.Value)
Source.Cells(NDEX, 3) = Trim(Me.Date_Entered_tb.Value)
Source.Cells(NDEX, 4) = Trim(Me.Animal_ID_tb.Value)
Source.Cells(NDEX, 5) = Trim(Me.DOB_tb.Value)
Source.Cells(NDEX, 6) = Trim(Me.ParceL_Number_tb.Value)
Source.Cells(NDEX, 7) = Trim(Me.Sire_ID_tb.Value)
Source.Cells(NDEX, 8) = Trim(Me.Dam_ID_tb.Value)
Source.Cells(NDEX, 9) = Trim(Me.Sex_tb.Value)
'Source.Cells(NDEX, 10) = Trim(Me.Age_of_Dam_tb.Value)
'Source.Cells(NDEX, 11) = Trim(Me.dam_weight_tb.Value)
'Source.Cells(NDEX, 12) = Trim(Me.Breed_tb.Value)
'Source.Cells(NDEX, 13) = Trim(Me.birth_weight_tb.Value)
'Source.Cells(NDEX, 14) = Trim(Me.weaning_weight_tb.Value)
Exit For
End If
Next
LoadData
lstData.ListIndex = pointer
End Sub

Private Sub Done_Click()
Unload Me
End Sub


Private Sub Type_cmb_change()
LoadData
End Sub

Private Sub cmdDelete_click()
If lstData.ListIndex = -1 Then Exit Sub
Dim NDEX As String
Dim msg As String
NDEX = Me.Indx_tb.Value
msg = lstData.List(lstData.ListIndex, 1)
msg = msg & "" & lstData.List(lstData.ListIndex, 2)
msg = msg & "" & lstData.List(lstData.ListIndex, 3)
msg = msg & "" & lstData.List(lstData.ListIndex, 4)
msg = msg & "" & lstData.List(lstData.ListIndex, 5)
msg = msg & "" & lstData.List(lstData.ListIndex, 6)
msg = msg & "" & lstData.List(lstData.ListIndex, 7)
msg = msg & "" & lstData.List(lstData.ListIndex, 8)
msg = msg & "" & lstData.List(lstData.ListIndex, 9)
'msg = msg & "" & lstData.List(lstData.ListIndex, 10)
'msg = msg & "" & lstData.List(lstData.ListIndex, 11)
'msg = msg & "" & lstData.List(lstData.ListIndex, 12)
'msg = msg & "" & lstData.List(lstData.ListIndex, 13)
'msg = msg & "" & lstData.List(lstData.ListIndex, 14)
If MsgBox(msg, vbYesNo + vbDefaultButton2, "DELETE #" & NDEX & "from" &
Type_cmb) = vbYes Then
RemoveItem NDEX
End If
End Sub

Private Sub RemoveItem(NDEX As String)
Dim found As Range
Dim OK As Boolean
With Worksheets("Manual Livestock Data")
For Each found In .Range(.Range("A7"), .Range("A7").End(xlDown))
If found = NDEX Then
OK = True
Exit For
End If
Next
End With
If OK Then
found.Resize(, 1).Delete xlShiftUp
LoadData
Else
MsgBox NDEX & "Not found!"
End If
End Sub

Private Sub lstdata_click()
With lstData
Me.Indx_tb.Value = .List(.ListIndex, 1)
Me.Type_cmb.Value = .List(.ListIndex, 2)
Me.Date_Entered_tb.Value = .List(.ListIndex, 3)
Me.Animal_ID_tb.Value = .List(.ListIndex, 4)
Me.DOB_tb.Value = .List(.ListIndex, 5)
Me.ParceL_Number_tb.Value = .List(.ListIndex, 6)
Me.Sire_ID_tb.Value = .List(.ListIndex, 7)
Me.Dam_ID_tb.Value = .List(.ListIndex, 8)
Me.Sex_tb.Value = .List(.ListIndex, 9)
'Me.Age_of_Dam_tb.Value = .List(.ListIndex, 10)
'Me.dam_weight_tb.Value = .List(.ListIndex, 11)
'Me.Breed_tb.Value = .List(.ListIndex, 12)
'Me.birth_weight_tb.Value = .List(.ListIndex, 13)
'Me.weaning_weight_tb.Value = .List(.ListIndex, 14)
End With
End Sub

Private Sub Userform_Initialize()
With Worksheets("Manual Livestock Data")
Set Source = .Range(Range("A7"), .Range("AA7").End(xlDown))
End With
LoadTypes
End Sub

Private Sub LoadTypes()
Dim animal As New Scripting.Dictionary
For NDEX = 2 To Source.Rows.Count
animals = Source.Cells(NDEX, "B").Value 'this pulls the animal type from
table column 2
If Not animal.Exists(animals) Then
animal.Add animals, animals
Type_cmb.AddItem animals
End If
Next
End Sub

Private Sub LoadData()
Me.Sex_tb = ""
Me.Sire_ID_tb = ""
Me.birth_weight_tb = ""
Me.ParceL_Number_tb = ""
Me.Date_Entered_tb = ""
Me.Dam_ID_tb = ""
Me.weaning_weight_tb = ""
Me.Animal_ID_tb = ""
Me.Indx_tb = ""
Me.Age_of_Dam_tb = ""
Me.Breed_tb = ""
Me.DOB_tb = ""
Me.dam_weight_tb = ""
With lstData
..Clear
animals = Me.Type_cmb.Value
For NDEX = 1 To Source.Rows.Count
If animals = Source.Cells(NDEX, 2) Then
..AddItem Source.Cells(NDEX, 1)
..List(.ListCount - 1, 1) = Source.Cells(NDEX, 1)
..List(.ListCount - 1, 2) = Source.Cells(NDEX, 2)
..List(.ListCount - 1, 3) = Source.Cells(NDEX, 3)
..List(.ListCount - 1, 4) = Source.Cells(NDEX, 4)
..List(.ListCount - 1, 5) = Source.Cells(NDEX, 5)
..List(.ListCount - 1, 6) = Source.Cells(NDEX, 6)
..List(.ListCount - 1, 7) = Source.Cells(NDEX, 7)
..List(.ListCount - 1, 8) = Source.Cells(NDEX, 8)
..List(.ListCount - 1, 9) = Source.Cells(NDEX, 9)
'get an error when trying to run form due to these next five columns... argh
why?
'.List(.ListCount - 1, 10) = Source.Cells(NDEX, 10)
'.List(.ListCount - 1, 11) = Source.Cells(NDEX, 11)
'.List(.ListCount - 1, 12) = Source.Cells(NDEX, 12)
'.List(.ListCount - 1, 13) = Source.Cells(NDEX, 13)
'.List(.ListCount - 1, 14) = Source.Cells(NDEX, 14)
End If
Next
End With
End Sub

Thank you once again kind soul!!! :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Textboxes not populating from listbox 3
VBA ".Clear" Error 4
Textboxes not poplating from listbox 1
Deleting row from entry in listbox on a form error 6
Sum values in listbox 3
User Form: Combo Box - filter out repeats. 1
Tom Ogilvy or anyone else who can help! 2
Fill a Listbox with mult. Variables in userform 2

Top