VBA Run-time Error "13" - Question

[Guest]

I am receiving a run-time error "13" when I try to run a USerform in VBA.
When I try to debug the problem - I do not get an indication where the
problem is. Below is the code. Does anyone see what might be causing the
error?

Private Sub cmdadd_click()
Dim lRow As Long
Dim lDate As Long
Dim ws As Worksheet
Set ws = Worksheets("Scrip Purchases")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row

lDate = Me.txtDate.Value

'check for a family name
If Trim(Me.cbofamily.Value) = "" Then
Me.cbofamily.SetFocus
MsgBox "Please enter family name"
Exit Sub
End If

'copy the data to the database
With ws
..Cells(lRow, 1).Value = Me.txtDate.Value
..Cells(lRow, 2).Value = Me.cbofamily.Value
..Cells(lRow, 3).Value = Me.cbovendor.Value
..Cells(lRow, 4).Value = Me.txtdenomination.Value
..Cells(lRow, 5).Value = Me.txtquantity.Value
End With

'clear the data
Me.txtDate.Value = Format(Date, "medium date")
Me.cbofamily.Value = ""
Me.cbovendor.Value = ""
Me.txtdenomination.Value = ""
Me.txtquantity.Value = ""
Me.txtDate.SetFocus

End Sub

Private Sub cmdclose_Click()
Unload Me

End Sub

Private Sub UserForm_Initialize()
Dim cfamily As Range
Dim cvendor As Range
Dim ws As Worksheet
Set ws = Worksheets("Lookuplists")

For Each cfamily In ws.Range("familylists")
With Me.cbofamily
..AddItem cfamily.Value
..List(.List - 1, 1) = cfamily.Offset(0, 1).Value
End With
Next cfamily

For Each cvendor In ws.Range("vendorlists")
With Me.cbovendor
..AddItem cvendor.Value
..List(.List - 1, 1) = cvendor.Offset(0, 1).Value
End With
Next cvendor

Me.txtdenomination.Value = ""
Me.txtquantity.Value = ""
End Sub

 

[Dave Peterson]

I bet it's a typo.

..List(.List - 1, 1) = cfamily.Offset(0, 1).Value
should be:
..List(.ListCount - 1, 1) = cfamily.Offset(0, 1).Value

(same with cVendor, too).

Just an aside.

You can change properties in the properties window for any of your controls--or
you can change the properties in code.

Sometimes, it's easier (for me anyway) to see what's going on by using code
exclusively.

I'd make sure the .columncount is 2 and the .rowsource is empty -- just in
case...

Option Explicit
Private Sub UserForm_Initialize()

Dim cFamily As Range
Dim cVendor As Range
Dim ws As Worksheet
Set ws = Worksheets("Lookuplists")

With Me.CBOFamily
.ColumnCount = 2
.RowSource = ""
End With

With Me.CBOVendor
.ColumnCount = 2
.RowSource = ""
End With

For Each cFamily In ws.Range("familylists")
With Me.CBOFamily
.AddItem cFamily.Value
.List(.ListCount - 1, 1) = cFamily.Offset(0, 1).Value
End With
Next cFamily

For Each cVendor In ws.Range("vendorlists")
With Me.CBOVendor
.AddItem cVendor.Value
.List(.ListCount - 1, 1) = cVendor.Offset(0, 1).Value
End With
Next cVendor

Me.TxtDenomination.Value = ""
Me.TxtQuantity.Value = ""

End Sub

If you still have trouble adding the values to the combobox, you may be trying
to add errors (#value's, div/0, #ref's) to the combobox list. .Value will fail
for those.

You could avoid them using
if iserror(cfamily.value) then
'do something
....

Or you could just add the .Text to the combobox. .Text is nice when you know
that your dates/times/quantities are formatted in the worksheet cell nicely.

 

[Guest]

Dave-

Thanks - for catching my typo - made the change you mentioned and the User
Form works perfectly.