Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA ".Clear" Error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default VBA ".Clear" Error
Hello there,

I am trying to creat an Update/Delete form for a livestock inventory
database for a ranch in northern CA. I have the coding as follows, but I keep
getting an unspecified error that highlights my ".Clear" in tbe Private Sub
LoadData() section. Would anyone have any suggestions on how to get past
this? The coding for the entire form is copied below. Thank you!!

CODE:


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
If Trim(Me.Date_Entered_tb.Value) = "" Then Exit Sub
If Trim(Me.Sex_tb.Text) = "" Then Exit Sub
If Trim(Me.birth_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.ParceL_Number_tb.Value) = "" Then Exit Sub
If Trim(Me.weaning_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.Index_tb.Value) = "" Then Exit Sub
If Trim(Me.Breed_tb.Value) = "" Then Exit Sub
If Trim(Me.DOB_tb.Value) = "" Then Exit Sub
If Not IsNumeric(Me.Animal_ID_tb.Text) Then Exit Sub ' what does this mean?
pointer = lstData.ListIndex
For Index = 1 To Source.Rows.Count
If Source.Cells(Index, 1) = Trim(Me.Type_cmb.Value) Then
Source.Cells(Index, 2) = Trim(Me.Date_Entered_tb.Value)
Source.Cells(Index, 3) = Trim(Me.Index_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.Type_cmb.Value
msg = 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(, 6).Delete xlShiftUp
LoadData
Else
MsgBox Index & "Not found!"
End If
End Sub

Private Sub lstdata_click()
With lstData
Me.Type_cmb.Value = .List(.ListIndex, 1)
Me.Date_Entered_tb.Value = .List(.ListIndex, 2)
Me.Index_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, "A").Value
If Not animal.Exists(animals) Then
animal.Add animals, animals
Type_cmb.AddItem animals
End If
Next
End Sub

Private Sub LoadData()
Me.Type_cmb = ""
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 'HERE IS THE LOCATION OF THE MAIN ERROR
animals = Me.Type_cmb.Value
For Index = 1 To Source.Rows.Count
If animals = Source.Cells(Index, 1) 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)
..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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 586
Default VBA ".Clear" Error
You are using the With...End With Statement with a non object. You must have
an object to use the With Statement. I do not see where 1stData is declared
as an object.


--
Cheers,
Ryan


"Binaface" wrote:

Hello there,

I am trying to creat an Update/Delete form for a livestock inventory
database for a ranch in northern CA. I have the coding as follows, but I keep
getting an unspecified error that highlights my ".Clear" in tbe Private Sub
LoadData() section. Would anyone have any suggestions on how to get past
this? The coding for the entire form is copied below. Thank you!!

CODE:


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
If Trim(Me.Date_Entered_tb.Value) = "" Then Exit Sub
If Trim(Me.Sex_tb.Text) = "" Then Exit Sub
If Trim(Me.birth_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.ParceL_Number_tb.Value) = "" Then Exit Sub
If Trim(Me.weaning_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.Index_tb.Value) = "" Then Exit Sub
If Trim(Me.Breed_tb.Value) = "" Then Exit Sub
If Trim(Me.DOB_tb.Value) = "" Then Exit Sub
If Not IsNumeric(Me.Animal_ID_tb.Text) Then Exit Sub ' what does this mean?
pointer = lstData.ListIndex
For Index = 1 To Source.Rows.Count
If Source.Cells(Index, 1) = Trim(Me.Type_cmb.Value) Then
Source.Cells(Index, 2) = Trim(Me.Date_Entered_tb.Value)
Source.Cells(Index, 3) = Trim(Me.Index_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.Type_cmb.Value
msg = 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(, 6).Delete xlShiftUp
LoadData
Else
MsgBox Index & "Not found!"
End If
End Sub

Private Sub lstdata_click()
With lstData
Me.Type_cmb.Value = .List(.ListIndex, 1)
Me.Date_Entered_tb.Value = .List(.ListIndex, 2)
Me.Index_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, "A").Value
If Not animal.Exists(animals) Then
animal.Add animals, animals
Type_cmb.AddItem animals
End If
Next
End Sub

Private Sub LoadData()
Me.Type_cmb = ""
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 'HERE IS THE LOCATION OF THE MAIN ERROR
animals = Me.Type_cmb.Value
For Index = 1 To Source.Rows.Count
If animals = Source.Cells(Index, 1) 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)
.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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default VBA ".Clear" Error
First, this:

If Not IsNumeric(Me.Animal_ID_tb.Text) Then Exit Sub ' what does this mean?

Says that if that field (the Animal ID textbox???) doesn't look like a number,
then the update procedure should stop.

Second, I bet you're populating that listbox (lstData) with a rowsource--either
via the properties window or through some code you didn't share.

You could either stop doing that or use code like:

LstData.RowSource = ""
LstData.Clear

This will remove all the items from the listbox--is that what you wanted?

Binaface wrote:

Hello there,

I am trying to creat an Update/Delete form for a livestock inventory
database for a ranch in northern CA. I have the coding as follows, but I keep
getting an unspecified error that highlights my ".Clear" in tbe Private Sub
LoadData() section. Would anyone have any suggestions on how to get past
this? The coding for the entire form is copied below. Thank you!!

CODE:

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
If Trim(Me.Date_Entered_tb.Value) = "" Then Exit Sub
If Trim(Me.Sex_tb.Text) = "" Then Exit Sub
If Trim(Me.birth_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.ParceL_Number_tb.Value) = "" Then Exit Sub
If Trim(Me.weaning_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.Index_tb.Value) = "" Then Exit Sub
If Trim(Me.Breed_tb.Value) = "" Then Exit Sub
If Trim(Me.DOB_tb.Value) = "" Then Exit Sub
If Not IsNumeric(Me.Animal_ID_tb.Text) Then Exit Sub ' what does this mean?
pointer = lstData.ListIndex
For Index = 1 To Source.Rows.Count
If Source.Cells(Index, 1) = Trim(Me.Type_cmb.Value) Then
Source.Cells(Index, 2) = Trim(Me.Date_Entered_tb.Value)
Source.Cells(Index, 3) = Trim(Me.Index_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.Type_cmb.Value
msg = 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(, 6).Delete xlShiftUp
LoadData
Else
MsgBox Index & "Not found!"
End If
End Sub

Private Sub lstdata_click()
With lstData
Me.Type_cmb.Value = .List(.ListIndex, 1)
Me.Date_Entered_tb.Value = .List(.ListIndex, 2)
Me.Index_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, "A").Value
If Not animal.Exists(animals) Then
animal.Add animals, animals
Type_cmb.AddItem animals
End If
Next
End Sub

Private Sub LoadData()
Me.Type_cmb = ""
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 'HERE IS THE LOCATION OF THE MAIN ERROR
animals = Me.Type_cmb.Value
For Index = 1 To Source.Rows.Count
If animals = Source.Cells(Index, 1) 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)
.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

--

Dave Peterson
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default VBA ".Clear" Error
HI Mr. Peterson, Thank you so much for your quick response! It was very
helpful in removing my .Clear error. I decided to remove the row source from
the list box to solve the problem.
If it is not too much trouble, I was wondering if you could explain the
reference in the coding below:

Private Sub LoadAnimals()
Dim animal As New Scripting.Dictionary
For Index = 1 To Source.Rows.Count 'WHAT DOES THIS INDEX REFER TO?
animals = Source.Cells(Index, "B").Value '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

'code pulled from a portion of the LoadData:
With lstData
..Clear
animals = Me.Type_cmb.Value
For Index = 1 To Source.Rows.Count 'WHAT DOES THIS INDEX REFER TO?
If animals = Source.Cells(Index, 2) Then
..AddItem Source.Cells(Index, 1) '


Thank you again! This is like piecing together a puzzle, and my supervisors
have no clue how to do this either!

Sincerely,
Kristina

"Dave Peterson" wrote:

First, this:

If Not IsNumeric(Me.Animal_ID_tb.Text) Then Exit Sub ' what does this mean?

Says that if that field (the Animal ID textbox???) doesn't look like a number,
then the update procedure should stop.

Second, I bet you're populating that listbox (lstData) with a rowsource--either
via the properties window or through some code you didn't share.

You could either stop doing that or use code like:

LstData.RowSource = ""
LstData.Clear

This will remove all the items from the listbox--is that what you wanted?

Binaface wrote:

Hello there,

I am trying to creat an Update/Delete form for a livestock inventory
database for a ranch in northern CA. I have the coding as follows, but I keep
getting an unspecified error that highlights my ".Clear" in tbe Private Sub
LoadData() section. Would anyone have any suggestions on how to get past
this? The coding for the entire form is copied below. Thank you!!

CODE:

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
If Trim(Me.Date_Entered_tb.Value) = "" Then Exit Sub
If Trim(Me.Sex_tb.Text) = "" Then Exit Sub
If Trim(Me.birth_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.ParceL_Number_tb.Value) = "" Then Exit Sub
If Trim(Me.weaning_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.Index_tb.Value) = "" Then Exit Sub
If Trim(Me.Breed_tb.Value) = "" Then Exit Sub
If Trim(Me.DOB_tb.Value) = "" Then Exit Sub
If Not IsNumeric(Me.Animal_ID_tb.Text) Then Exit Sub ' what does this mean?
pointer = lstData.ListIndex
For Index = 1 To Source.Rows.Count
If Source.Cells(Index, 1) = Trim(Me.Type_cmb.Value) Then
Source.Cells(Index, 2) = Trim(Me.Date_Entered_tb.Value)
Source.Cells(Index, 3) = Trim(Me.Index_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.Type_cmb.Value
msg = 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(, 6).Delete xlShiftUp
LoadData
Else
MsgBox Index & "Not found!"
End If
End Sub

Private Sub lstdata_click()
With lstData
Me.Type_cmb.Value = .List(.ListIndex, 1)
Me.Date_Entered_tb.Value = .List(.ListIndex, 2)
Me.Index_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, "A").Value
If Not animal.Exists(animals) Then
animal.Add animals, animals
Type_cmb.AddItem animals
End If
Next
End Sub

Private Sub LoadData()
Me.Type_cmb = ""
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 'HERE IS THE LOCATION OF THE MAIN ERROR
animals = Me.Type_cmb.Value
For Index = 1 To Source.Rows.Count
If animals = Source.Cells(Index, 1) 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)
.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

--

Dave Peterson

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default VBA ".Clear" Error
In the earlier message, you had these lines of code:

Private Source As Range
Private Index As Long

In this line:
For Index = 1 To Source.Rows.Count
index is just a counter so you can loop through the rows in source.

Sometimes (always???), it's easier to break the code into little pieces and see
what happens in those smaller pieces.

Try this in a test workbook:

Option Explicit
Sub testme()

Dim mySource As Range
Dim myIndex As Long

Set mySource = ActiveSheet.Range("c3:d9")

For myIndex = 1 To mySource.Rows.Count
MsgBox myIndex & vbLf & mySource.Rows(myIndex).Address
Next myIndex

End Sub

I used different variable names. Both Source and Index are terms that VBA
owns. And even though it won't confuse excel/vba, it may confuse me.

ps. Try this version, too:

Option Explicit
Sub testme()

Dim mySource As Range
Dim myIndex As Long

Set mySource = ActiveSheet.Range("c3:d4, g9:L11")

For myIndex = 1 To mySource.Rows.Count
MsgBox myIndex & vbLf & mySource.Rows(myIndex).Address
Next myIndex
End Sub

Just to show you how mysource.rows.count only looks at the first area in that
range.




Binaface wrote:

HI Mr. Peterson, Thank you so much for your quick response! It was very
helpful in removing my .Clear error. I decided to remove the row source from
the list box to solve the problem.
If it is not too much trouble, I was wondering if you could explain the
reference in the coding below:

Private Sub LoadAnimals()
Dim animal As New Scripting.Dictionary
For Index = 1 To Source.Rows.Count 'WHAT DOES THIS INDEX REFER TO?
animals = Source.Cells(Index, "B").Value '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

'code pulled from a portion of the LoadData:
With lstData
.Clear
animals = Me.Type_cmb.Value
For Index = 1 To Source.Rows.Count 'WHAT DOES THIS INDEX REFER TO?
If animals = Source.Cells(Index, 2) Then
.AddItem Source.Cells(Index, 1) '

Thank you again! This is like piecing together a puzzle, and my supervisors
have no clue how to do this either!

Sincerely,
Kristina

"Dave Peterson" wrote:

First, this:

If Not IsNumeric(Me.Animal_ID_tb.Text) Then Exit Sub ' what does this mean?

Says that if that field (the Animal ID textbox???) doesn't look like a number,
then the update procedure should stop.

Second, I bet you're populating that listbox (lstData) with a rowsource--either
via the properties window or through some code you didn't share.

You could either stop doing that or use code like:

LstData.RowSource = ""
LstData.Clear

This will remove all the items from the listbox--is that what you wanted?

Binaface wrote:

Hello there,

I am trying to creat an Update/Delete form for a livestock inventory
database for a ranch in northern CA. I have the coding as follows, but I keep
getting an unspecified error that highlights my ".Clear" in tbe Private Sub
LoadData() section. Would anyone have any suggestions on how to get past
this? The coding for the entire form is copied below. Thank you!!

CODE:

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
If Trim(Me.Date_Entered_tb.Value) = "" Then Exit Sub
If Trim(Me.Sex_tb.Text) = "" Then Exit Sub
If Trim(Me.birth_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.ParceL_Number_tb.Value) = "" Then Exit Sub
If Trim(Me.weaning_weight_tb.Value) = "" Then Exit Sub
If Trim(Me.Index_tb.Value) = "" Then Exit Sub
If Trim(Me.Breed_tb.Value) = "" Then Exit Sub
If Trim(Me.DOB_tb.Value) = "" Then Exit Sub
If Not IsNumeric(Me.Animal_ID_tb.Text) Then Exit Sub ' what does this mean?
pointer = lstData.ListIndex
For Index = 1 To Source.Rows.Count
If Source.Cells(Index, 1) = Trim(Me.Type_cmb.Value) Then
Source.Cells(Index, 2) = Trim(Me.Date_Entered_tb.Value)
Source.Cells(Index, 3) = Trim(Me.Index_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.Type_cmb.Value
msg = 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(, 6).Delete xlShiftUp
LoadData
Else
MsgBox Index & "Not found!"
End If
End Sub

Private Sub lstdata_click()
With lstData
Me.Type_cmb.Value = .List(.ListIndex, 1)
Me.Date_Entered_tb.Value = .List(.ListIndex, 2)
Me.Index_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, "A").Value
If Not animal.Exists(animals) Then
animal.Add animals, animals
Type_cmb.AddItem animals
End If
Next
End Sub

Private Sub LoadData()
Me.Type_cmb = ""
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 'HERE IS THE LOCATION OF THE MAIN ERROR
animals = Me.Type_cmb.Value
For Index = 1 To Source.Rows.Count
If animals = Source.Cells(Index, 1) 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)
.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

--

Dave Peterson


--

Dave Peterson
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
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
"Invalid property" after "Clear Form" B[_4_] Excel Programming 1 April 19th 06 04:57 AM
What is Error "Method "Paste" of object "_Worksheet" failed? vat Excel Programming 7 February 17th 06 08:05 PM
"Clean Me" Macro is giving "#VALUE!" error in the Notes field. Ryan Watkins Excel Programming 1 June 11th 05 12:25 AM
Getting "compile error" "method or data member not found" on reinstall Bp Excel Programming 1 April 23rd 04 04:42 PM


All times are GMT +1. The time now is 08:12 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