Macro Bombs Excel 2000

**jaslake** · 06-01-2009, 04:01 PM

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

**Leith Ross** · 06-01-2009, 05:12 PM

Hello jaslake,

I haven't run the workbook in Excel 2000 yet, but I did see this error in the second macro (marked in red).

    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

Sheets is a workbook collection object that holds all the Worksheets, Chart sheets, Dialog sheets, and Excel4Macro sheets in a Workbook. Sheets can not be used to variable. The ListFillRange must be a string that contains the Range address, and optionally the Worksheet name.

**jaslake** · 06-01-2009, 05:27 PM

Hi Leith.

I know that line of code appears to be incorrect. Please understand, I have rewritten this post several times because, apparently, your servers were (are) having a problem. Finally, the last time, I copied the text of the post to notepad so I could simply paste when I was able to finally upload the post.

That line of code got truncated into two lines as follows:

.ListFillRange = "Categories!" + Sheets

("Categories").Range(str).Address

If you delete the line break or whatever it is and make the code read

.ListFillRange = "Categories!" + Sheets("Categories").Range(str).Address

I think it will work for you. Let me know if this doesn't work for you and I will repost the code.
I should add, the macro bombs when you have selected an item in column F, Category then hit the tab key. I sure hope you can figure this out. It's been driving me nuts.

Thanks, John

**jaslake** · 06-01-2009, 10:57 PM

I'm reposting this code. My last post was made from a copy/paste from notepad. I had tried to post for three days and had server problems. This code is directly from the project.

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

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

The rest of my original post still applies. The macro bombs when you have selected an item in worksheet "Check Register", column F, Category then hit the tab key. I sure hope you can figure this out. It's been driving me nuts.
Hopefully you can help.

Thanks, John

**Leith Ross** · 06-02-2009, 12:16 AM

Hello John,

You were right about the server problems. Everything should be back to normal according to forum's owner. I have been occupied about all day with other things and just back to look at the posts. If you could post the workbook, it would make it faster to troubleshoot the trouble the problem. I have Excel 2000 and can run it as is.