Run-time error '13': Type mismatch
 

I am new to learning how to program with VBA and Excel. This code came
from the book, “VBA for Excel Made Simple” that I borrowed from the
library. When I run the code, I keep getting the error message: Run-time
error '13': Type mismatch.

The purpose of this form is to insert an extra row and add the
representative name in the RepName list.

I have also attached the sample excel file and the explanation that
came from the actual book. Thank you for all your help in advance.


Code:
--------------------

Private Sub UserForm_Initialize()
RepBox = ""
SalesBox = "0"
For Each Cell In Range("rep_name")
RepList.AddItem Cell.Value
Next
End Sub

Private Sub AddButton_Click()
RepBox.SetFocus
With RepBox
If .Value = "" Then
MsgBox "Enter a name for the rep to be added"
.SetFocus
End If
If .Value = "" Then
MsgBox "Enter a sales value for the rep given"
SalesBox.SetFocus
End If
End With
With Worksheets("weeklysales").Range("total")
newSumSales = SalesBox.Value + .Offset(0, 1)
.EntireRow.Insert
.Offset(-1, 0) = RepBox.Value
.Offset(-1, 1) = SalesBox.Value
.Offset(0, 1) = newSumSales
Range("sales_to_date", .Offset(-1, 1)).Name = "sales_to_date"
End With
RepList.AddItem RepBox.Value
Unload Me
End Sub

Private Sub CancelButton_Click()
Unload Me
End
End Sub

--------------------


+-------------------------------------------------------------------+
|Filename: SALESMAN.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=75|
+-------------------------------------------------------------------+

--
siamadu
------------------------------------------------------------------------
siamadu's Profile: http://www.thecodecage.com/forumz/member.php?userid=128
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=63314

Run-time error '13': Type mismatch
 

Firstly you should qualify all your statements and set your objects
properly adding Me. in front of RepBox etc is a must.
Private Sub UserForm_Initialize()
Me.RepBox = ""
Me.SalesBox = "0"
For Each Cell In Range("rep_name")
Me.RepList.AddItem Cell.Value
Next
End Sub
Private Sub AddButton_Click()
Me.RepBox.SetFocus
With Me.RepBox
If .Value = "" Then
MsgBox "Enter a name for the rep to be added"
..SetFocus
End If
If .Value = "" Then
MsgBox "Enter a sales value for the rep given"
SalesBox.SetFocus
End If
End With
With Worksheets("weeklysales").Range("total")
newSumSales = Me.SalesBox.Value + .Offset(0, 1)
..EntireRow.Insert
..Offset(-1, 0) = RepBox.Value
..Offset(-1, 1) = SalesBox.Value
..Offset(0, 1) = newSumSales
Range("sales_to_date", .Offset(-1, 1)).Name = "sales_to_date"
End With
Me.RepList.AddItem Me.RepBox.Value
Unload Me
End Sub
Private Sub CancelButton_Click()
Unload Me
End
End Sub

Secondly in your workbook you do not have a range or declared variable
for newSumSales!

siamadu;227505 Wrote:
I am new to learning how to program with VBA and Excel. This code came
from the book, “VBA for Excel Made Simple” that I borrowed from the
library. When I run the code, I keep getting the error message: Run-time
error '13': Type mismatch.

The purpose of this form is to insert an extra row and add the
representative name in the RepName list.

I have also attached the sample excel file and the explanation that
came from the actual book. Thank you for all your help in advance.


Code:
--------------------

Private Sub UserForm_Initialize()
RepBox = ""
SalesBox = "0"
For Each Cell In Range("rep_name")
RepList.AddItem Cell.Value
Next
End Sub

Private Sub AddButton_Click()
RepBox.SetFocus
With RepBox
If .Value = "" Then
MsgBox "Enter a name for the rep to be added"
.SetFocus
End If
If .Value = "" Then
MsgBox "Enter a sales value for the rep given"
SalesBox.SetFocus
End If
End With
With Worksheets("weeklysales").Range("total")
newSumSales = SalesBox.Value + .Offset(0, 1)
.EntireRow.Insert
.Offset(-1, 0) = RepBox.Value
.Offset(-1, 1) = SalesBox.Value
.Offset(0, 1) = newSumSales
Range("sales_to_date", .Offset(-1, 1)).Name = "sales_to_date"
End With
RepList.AddItem RepBox.Value
Unload Me
End Sub

Private Sub CancelButton_Click()
Unload Me
End
End Sub

--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=63314