Thread: Macro Bombs Excel 2000
View Single Post
  #4  
Old 06-01-2009, 10:57 PM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 757
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
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.

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
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
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
Reply With Quote