Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Textbox not filling from listbox
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Textbox not filling from listbox
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!
--
Kristina
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Textbox not filling from listbox
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.


"Binaface" wrote:

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!
--
Kristina
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Textbox not filling from listbox
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!
--
Kristina


"JLGWhiz" wrote:

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.


"Binaface" wrote:

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!
--
Kristina
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Textbox not filling from listbox
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.

"Binaface" wrote:

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!
--
Kristina


"JLGWhiz" wrote:

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.


"Binaface" wrote:

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!
--
Kristina
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Textbox not filling from listbox
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!!! :)
--
Kristina
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Textbox filling cells jnf40 Excel Programming 1 October 5th 07 02:26 PM
Filling ListBox another excel file suomi Excel Programming 1 January 24th 07 12:47 AM
Filling a listbox HSalim[MVP] Excel Programming 1 October 25th 06 08:37 PM
filling userform listbox simonhall[_4_] Excel Programming 2 June 16th 06 01:43 AM
Filling a textbox with text in another cell Charles Excel Programming 2 August 19th 04 01:32 AM


All times are GMT +1. The time now is 03:57 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement