Textboxes not populating from listbox

K

Kristina

Hello there :)

I am making an update delete form that should function such that my listbox
is populated based on a combobox selection, and then when a record in the
listbox is selected the data is put into textboxes that can then be updated,
or the record can be deleted.
My form populates the listbox based on the selection made in the combobox,
but the textboxes remain blank when I select a record in the listbox. I don't
know what I am doing wrong and I have only ever done a very simple
update/delete userform before. This is more complicated. Does anyone have any
suggestions? My code is as follows:


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

Private Sub btnUpdate_Click()
Dim pointer As String
If Animal_ID_tb.Text = "" Then Exit Sub
pointer = lstData.ListIndex
For Index = 1 To Source.Rows.Count
If Source.Cells(Index, 1) = Me.Index_tb.Value Then
Source.Cells(Index, 2) = Trim(Me.Type_cmb.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 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.Index_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)
Worksheets("Manual Livestock Data").Unprotect
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.Index_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()
Me.Type_cmb.SetFocus
With Worksheets("Manual Livestock Data")
Set Source = .Range(Range("A7"), .Range("N7").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.Index_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!!!!
 
J

JLGWhiz

You have an illegal use of "Index" in the Sub btnUpdate_Click(). If you open
that code module and use F8 to walk through the code, it should highlight the
problem for you. All you need to do is change the spelling slightly to Indx
or Ndx. It is where you try to use it as a variable for the row number in
your named Range "Source".
 
J

JLGWhiz

One other thing, your description of what should happen says that the combo
box selection triggers the filling of the textboxes, but your code has an If
.... Then statement that indicates it is reliant on the textbox named
"Index_tb".
 
B

Binaface

Thank you so much :) I updated my coding with your suggestions; however, the
textboxes are still not populating from the listbox when a record is selected
in the listbox. Could I be overlooking another coding error? Thank you
again!!
The updated coding is as follows:


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

Private Sub btnUpdate_Click()
Dim pointer As String
If Animal_ID_tb.Text = "" Then Exit Sub
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, 2) = Trim(Me.Type_cmb.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("N7").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
 

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

VBA ".Clear" Error 4
Textbox not filling from listbox 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