application defined or object defined error

Hi,

i have this code that i wrote in excel 2007
its work exelent but when i am trying to run the script in excel 2003 i got an error 1004
applocation defined or object defined error
this code change the datavalidation list dynamicly depands on a value in a cell

i hope that you can help me

shay

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$C$66" Then
        If Range("CompBranch") = "áçø òðó" Or Range("CompBranch") = "" Then
            Range("CompSubBranch").Select
            Sheet1.Unprotect Password:="veryfunny!"
            With Selection.Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
            :=xlBetween
            End With
            Sheet1.Protect Password:="veryfunny!"
            Range("CompSubBranch") = Empty
            Exit Sub
        End If
        Call GenerateSubBranchList
        Range("CompSubBranch") = "áçø úú òðó"
    End If
        
End Sub

Sub GenerateSubBranchList()
    Dim rng As String
    rng = GetRange(Range("H66").Value)
    Range("CompSubBranch").Select
    Sheet1.Unprotect Password:="veryfunny!"

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="='List'!" & rng
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With    
    Sheet1.Protect Password:="veryfunny!"    
End Sub

Function GetRange(val As Integer) As String
   Dim startPnt As Integer 
   Dim endPnt As Integer
   For Each Item In Range("'List'!Q2:Q200")
    If Item.Value = val And startPnt = Empty Then startPnt = Item.Row
    If Item.Value = (val + 1) And endPnt = Empty Or Item.Value = Empty And endPnt =Empty Then endPnt = Item.Row - 1
  Next
   GetRange = "$P" & startPnt & ":$P" & endPnt
End Function

Hi and welcome to the forum.

Which line of your code gives you the error?

Regards

A few things that I notice

Range("CompSubBranch") = Empty

Empty is used as an unitialized vaiable. Just use

Range("CompSubBranch") = ""

For a validation (between) I expect a Formula like

Formula1:="1", Formula2:="3"

I would not use this : Sheet1.Protect

Instead

Dim Ws1 as Worksheet

Set Ws1 = Worksheets("Sheet1")
Ws1.Protect

why have

        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True

When the strings are "" (empty) ?

I understand that your rng goes upto Q200, but where it could go wrong:

Function GetRange(val As Integer) As String
   Dim startPnt As Integer 
   Dim endPnt As Integer
   For Each Item In Range("'List'!Q2:Q200")
    If Item.Value = val And startPnt = Empty Then startPnt = Item.Row
    If Item.Value = (val + 1) And endPnt = Empty Or Item.Value = Empty And endPnt =Empty Then endPnt = Item.Row - 1
  Next
   GetRange = "$P" & startPnt & ":$P" & endPnt
End Function

Use startPnt = "" and
An integer can take values up to 32,767.

startPnt = Item.Row, so theoretically it can become as large as Cells.rows.Count, which is already 65,536 in XL2003.
This will give an overflow error '6'

Where it goes wrong (I think) is here Range("'List'!Q2:Q200")

   Set WsList = Worksheets("List")
   For Each Item In WsList.Range("Q2:Q200")