Macro Bombs Excel 2000

[jaslake]

I've developed a workbook with macros in Excel 2007 (Compatibility

Mode), Windows Vista Operating System. The purpose of the Macos

is to allow auto complete on a validated data entry field.

The macros run fine on this platform. I then copied the workbook

to my desktop, Excel 2000, Windows 2000 Professional Operating

System. One of the macros is killing Excel on this platform.

The code is as follows:

Private Sub TempCombo_KeyDown(ByVal _
                              KeyCode As MSForms.ReturnInteger, _
                              ByVal Shift As Integer)
    Dim cboTemp As OLEObject
    Set ws = ActiveSheet
    Set cboTemp = ws.OLEObjects("TempCombo")

    'Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
    Case 9
        cboTemp.Visible = False
        ActiveCell.Offset(0, 1).Activate
    Case 13
        cboTemp.Visible = False
        ActiveCell.Offset(0, 1).Activate
    Case Else
        'do nothing
    End Select

End Sub

and:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error GoTo errHandler1

    If Not Intersect(Target, Range("I12:I2024")) Is Nothing Then
        If Target.Cells.Offset(0, -1).Value <> "" Then
            ActiveCell.Offset(1, -7).Select
        Else: GoTo exitHandler
        End If
    End If
    If Intersect(Target, Range("F12:F2024")) Is Nothing Then GoTo 

exitHandler
    Set cboTemp = ws.OLEObjects("TempCombo")
    On Error Resume Next
    If cboTemp.Visible = True Then
        With cboTemp
            .Top = 10
            .Left = 10
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
            .Value = ""
        End With
    End If

    On Error GoTo errHandler1
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = "Categories!" + Sheets

("Categories").Range(str).Address
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
    End If
exitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.Protect
    Exit Sub
errHandler1:
    Resume exitHandler

End Sub

I found this code at http://<br /> <br /> http://www.co...DataVal14.html from a lead on

this site.

These macros run fine on platform Excel 2007 (compatibility mode),

Vista but bomb out on Excel 2000, Windows 2000. The line of code

cboTemp.Visible = False

in the TempCombo_KeyDown macro appears to be the culprit.

If I comment out this line(s), the macro runs but the ComboBox

remains visible. Not an overwhelming problem but not as neat.


I get the following error message under Excel 2000:

"Excel.exe has generated errors and will be closed by Windoes.

You will need to restart the program. An error log is being

created."

The error log reports:

Application exception occirred:
App: (pid=2180)
When: 6/1/2009 @ etc.
Exception number: c0000005 (access violation)

I've been wrestling with this for three days with various coding

but cannot get Excel 2000 to buy it.

I'v also been wrestling with the Forum for several days; I'll try

to upload the file later if I can ever get this tread posted.

Any ideas on how I can modify the code to make it compatible would

be greatly appreciated.

Thanks for you help.

John