|
Textboxes not populating from listbox
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!!!! |
Textboxes not populating from listbox
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". "Kristina" wrote: 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!!!! |
Textboxes not populating from listbox
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". "Kristina" wrote: 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!!!! |
Textboxes not populating from listbox
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 -- Kristina "JLGWhiz" wrote: 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". "Kristina" wrote: 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!!!! |
| All times are GMT +1. The time now is 12:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com