Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2007

Tip: Looking for answers? Try searching our database.

VBA Run-time Error "13" - Question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JWNJ - 26 May 2007 14:36 GMT
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 - 26 May 2007 15:16 GMT
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.

> 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
[quoted text clipped - 67 lines]
> Me.txtquantity.Value = ""
> End Sub

Signature

Dave Peterson

JWNJ - 26 May 2007 20:29 GMT
Dave-

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

> I bet it's a typo.
>
[quoted text clipped - 135 lines]
> > Me.txtquantity.Value = ""
> > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.