|
Macro Bombs Excel 2000
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:
Code:
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:
Code:
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
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
Last edited by jaslake; 06-03-2009 at 04:45 PM.
|