Textboxes not poplating from listbox

  • Thread starter Thread starter Binaface
  • Start date Start date
B

Binaface

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!!!!
 
On a first look this seems to be related to Excel. If so, you're in the
wrong newsgroup.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Tue, 25 Nov 2008 21:16:01 -0800 schrieb Binaface:
 
Back
Top