Excel VBA 2003-2010 Compatibility - Runtime Error 13 Type Mismatch

[Sangdrax]

Good Afternoon,

I have created a set of code which works in Excel 2010 and 2007 to do the following:

1.) User fills in form as per questions.
2.) User is asked if they have completed other requirements.
3.) Form unlocks checkbox options so user can continue with form.

I am now seeing issues in 2003 where there is a Runtime Error 13 Type Mismatch.
As far as I can tell there are no functions or formulas that would cause this within the sheet itself which leads me to think VBA may be the culprit.

Please see below for VBA:

Private Sub Worksheet_Change(ByVal Target As Range)


'Handle changes to a range - will fire the function when the range matches the intersect
If Not Intersect(Target, Range("D7")) Is Nothing Then updateCheckboxes
If Not Intersect(Target, Range("D9")) Is Nothing Then updateCheckboxes
If Not Intersect(Target, Range("D11")) Is Nothing Then updateCheckboxes
If Not Intersect(Target, Range("D13")) Is Nothing Then updateCheckboxes
If Not Intersect(Target, Range("L9")) Is Nothing Then updateCheckboxes
If Not Intersect(Target, Range("L11")) Is Nothing Then updateCheckboxes
If Not Intersect(Target, Range("J13")) Is Nothing Then updateCheckboxes
If Not Intersect(Target, Range("L13")) Is Nothing Then updateCheckboxes
End Sub


Function updateCheckboxes()
If (Range("D7") <> "" And Range("D9") <> "" And Range("D11") <> "" And Range("D13") <> "" And Range("L9") <> "" And Range("L11") <> "" And Range("J13").Value <> "No" And Range("J13").Value <> "" And Range("L13").Value <> "INVALID RESPONSE") Then
CheckBox1.Enabled = True
chkEcart.Enabled = True
CheckBox3.Enabled = True
CheckBox4.Enabled = True
CheckBox5.Enabled = True
CheckBox6.Enabled = True
Else
CheckBox1.Enabled = False
chkEcart.Enabled = False
CheckBox3.Enabled = False
CheckBox4.Enabled = False
CheckBox5.Enabled = False
CheckBox6.Enabled = False
End If
End Function


Any assistance with this would be greatly appreciated!! I'm sure it's a simple solution but I can't see what.
Kind Regards,

S

 

[dmt32]

Hi,
tried your code on my old laptop running 2003 & could not create your problem.

See if this update to your code makes any difference.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range


    Set rng = Me.Range("D7,D9,D11,D13,L9,L11,J13,L13")


    'Handle changes to a range - will fire the function when the range matches the intersect
    If Not Intersect(Target, rng) Is Nothing Then UpdateCheckboxes Target:=rng


End Sub




Sub UpdateCheckboxes(ByVal Target As Range)
    Dim valid As Boolean
    Dim Item As Range


    For Each Item In Target
        If Item.Address <> "$L$13" And Len(Item) = 0 Or _
           Item.Value = "INVALID RESPONSE" Then GoTo SetCheckBox
    Next Item


    valid = True


SetCheckBox:
    With Target.Parent
        .CheckBox1.Enabled = valid
        .chkEcart.Enabled = valid
        .CheckBox3.Enabled = valid
        .CheckBox4.Enabled = valid
        .CheckBox5.Enabled = valid
        .CheckBox6.Enabled = valid
    End With


End Sub

Dave

 

[Sangdrax]

Thanks a lot Dave, that worked a treat!

No longer getting any of the errors.

Greatly Appreciated.

S