Runtime error 13 - type mismatch - dont know why

[mleosu]

I have windows XP with Microsoft Excel 2003. When I run my code, it works fine. One of my coworkers has Windows 2000 and Microsoft Excel 97 SR-2 and she gets a Runtime Error 13 - Type Mismatch when it runs through the program.

The msgboxes open, but then she gets the runtime error when she clicks ok... I dont know what is wrong? Could anyone take a look at the code below and help? I am pretty sure it has something to do with the versions of excel, but I am not sure... I need to make it run on all versions as our company is not standardized.

Private Sub CommandButton1_Click()
'verifies the order form is accurate and complete

'unprotects the worksheet
Dim password
password = "6114"
Range("A1").Select
Application.ScreenUpdating = False
ActiveSheet.Unprotect "password"

'VERIFIES THE ORDER AMOUNT AND TOTALS ENTERED ARE EQUAL
'verifies the order amount with the amount entered per denomination and notates if variance
Range("j21").Select
If ActiveCell.Value <> 0 Then
ans = MsgBox("The Order Amount and breakdown entered in denominations do not match - check totals entered", vbCritical + vbOKOnly)
End If
Range("j21").Select

'VERIFIES ORDER INFORMATION IS COMPLETE
'checks to make sure that either acct/loc or user id is provided
Range("G14").Select
If ActiveCell.Value = 1 Then
ans = MsgBox("Provide EITHER the Account and Location numbers OR the User ID", vbCritical + vbOKOnly)
If ans = vbOK Then Range("B12:e12").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
Range("g12:J12").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
Range("b14:e14").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
End If

'checks to make sure that both acct and location are provided
Range("G14").Select
If ActiveCell.Value = 2 Then
ans = MsgBox("The Account AND Location numbers must be provided.", vbCritical + vbOKOnly)
If ans = vbOK Then Range("B12:e12").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
Range("g12:J12").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
End If

'checks to make sure the request date is provided
Range("B5").Select
If ActiveCell.Value = 0 Then
ans = MsgBox("Request Date must be provided.", vbCritical + vbOKOnly)
If ans = vbOK Then Range("B5:e5").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
Else
With Selection.Interior
        .ColorIndex = 0
        .Pattern = xlSolid
    End With
End If

'checks to make sure the delivery date is providedRange("B20").Select
If ActiveCell.Value = 0 Then
ans = MsgBox("Delivery Date must be provided.", vbCritical + vbOKOnly)
If ans = vbOK Then Range("B20:e20").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
Else
With Selection.Interior
        .ColorIndex = 0
        .Pattern = xlSolid
    End With
End If

'checks to make sure the bank name is provided
Range("B10").Select
If ActiveCell.Value = 0 Then
ans = MsgBox("Bank Name must be provided.", vbCritical + vbOKOnly)
If ans = vbOK Then Range("B10:e10").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
Else
With Selection.Interior
        .ColorIndex = 0
        .Pattern = xlSolid
    End With
End If

'checks to make sure the location name is providedRange("G10").Select
If ActiveCell.Value = 0 Then
ans = MsgBox("Location Name must be provided.", vbCritical + vbOKOnly)
If ans = vbOK Then Range("G10:J10").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
Else
With Selection.Interior
        .ColorIndex = 0
        .Pattern = xlSolid
    End With
End If

'clears error when acct/loc or userid are provided
Range("G14").Select
If ActiveCell.Value = 0 Then
If ans = vbOK Then Range("B12:e12").Select
With Selection.Interior
        .ColorIndex = 0
        .Pattern = xlSolid
    End With
Range("g12:J12").Select
With Selection.Interior
        .ColorIndex = 0
        .Pattern = xlSolid
    End With
Range("b14:e14").Select
With Selection.Interior
        .ColorIndex = 0
        .Pattern = xlSolid
    End With
End If

'ORDER PASSES - READY FOR SUBMISSION 
Dim a As Integer
a = 0
'checks all required feilds for values
If IsEmpty(Range("B5").Value) Then a = 1 'req date
If IsEmpty(Range("B10").Value) Then a = 1 'bank name
If IsEmpty(Range("G10").Value) Then a = 1 'location name
If Range("G14").Value <> 0 Then a = 1 'acct/loc or userid
If IsEmpty(Range("B20").Value) Then a = 1 'delivery date
If Range("G20").Value = 0 Then a = 1 'order amt
If Range("J21").Value <> 0 Then a = 1 'order total and breakdown match
'if all required info is available, the pass message
If a = 0 Then
msg = "ORDER IS READY FOR SUBMISSION"
msg = msg & vbLf & vbLf
msg = msg & "SAVE order and EMAIL to LateOrders@brinksinc.com"
ans = MsgBox(msg, vbCritical + vbOKOnly)
If ans = vbOK Then savefile
End If

'reprotects the worksheet
ActiveSheet.Protect "password", True, True, True
End Sub

 

[SkipVought]

Hi,

This is a haystack! Needle???

On WHAT statement does the error occur?

Skip,

_[red][/red]

 

[mleosu]

After speaking to the user, the runtime error appears everytime she click on okay - on any msgbox that appears...

the code used to be (for example):

'checks to make sure the location name is providedRange("G10").Select
If ActiveCell.Value = 0 Then
ans = MsgBox("Location Name must be provided.", vbCritical + vbOKOnly)
Range("G10:J10").Select
With Selection.Interior

I added "if ans = vbokay then" before the selection of the range... but it didnt fix the problem.

 

[SkipVought]

ON WHAT STATEMENT DO YOU GET THE ERROR???

Hit the DEBUG button when you get the error.

Skip,

_[red][/red]

 

[mleosu]

I get the error on "If ActiveCell.Value = 0 Then" from the code:

'checks to make sure the bank name is provided
Range("B10").Select
If ActiveCell.Value = 0 Then
ans = MsgBox("Bank Name must be provided.", vbCritical + vbOKOnly)
If ans = vbOK Then Range("B10:e10").Select
With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
Else
With Selection.Interior
        .ColorIndex = 0
        .Pattern = xlSolid
    End With
End If

This is because it is a string value not 0 so I changed it to "if isempty(activecell.value) then" - guess Excel 97 is more picky than Excel 2000 and 2003.

I sent it to be tested and it worked... thanks for trying to help though -

 

[HughLerwill]

You don't say after which msgbox you code is failing but assuming the last. What happens in savefile? I assume it is a procedure.

Have you tried stepping through the code to see where the error actually occurs?

Your use of code like;

ans = MsgBox("Location Name must be provided.", vbCritical + vbOKOnly)
If ans = vbOK Then Range("G10:J10").Select

is a little wierd, because if the msgbox has only the ok button on it it will always return vbOk in ans. To give a choice the MsgBox should have two buttons on it as in;

ans = MsgBox("Location Name must be provided.", vbCritical + vbOKCancel)

HTH Hugh,

 

[mleosu]

This issue has been resolved...

I was pretty sure that i didnt need the if ans = vbok, but i added it in there cause i didnt see what was happeneing...but thanks for letting me know that i dont have to have that line.

i found that on a couple lines of my code, it was looking to see if a cell was = 0 ... when the user put in a string, exel 97 found this unacceptable - whcih I guess it should be - so I changed it to look if the cell is empty - then based on the result perform the actions.

savefile is just a macro that pulls data together from the form to name the file and place it as the file name in saveas window

 

[HughLerwill]

using;

If val(ActiveCell.Value) = 0 Then

may have been another option

HTH Hugh,

 

[VulcanJedi]

i'd try the isempty, isnull functions....

Tranpkp

 

[mleosu]

is there any reason why i should use val() instead of isempty() or is it just another way to do things? Just curious...

 

[HughLerwill]

isempty will return false if the cell contains anything including a numeric zero.

 

[VulcanJedi]

yup, just try it to figure stuff out, run some examples...

Tranpkp