none
Excel VBA coding issues RRS feed

  • Question

  • Don't know if this is the right directory to ask this specific questions but:

    I am using a VBA code in a Direct-X combobox so it gives me the opportunity to search in a list of variables and when selecting this variable it places it in a certain cell so I can use it for Vlookup purposes.

    So:

    - VBA code in a combobox 

    - Code looks up a variable in a certain list

    - When selecting it places the selection in a certain cell

    - From this cell other cells have a Vlookup to find more details about this particular variable.

    What happens right now: 

    - The code (below) crashes Excel everytime I look something up.

    I placed my question on a couple of forums but no one has the correct answer for this problem, and it is driving me crazy. Tried many different versions of the code but keeps crashing. Who is able to help me :) 

    Code:

    Private Sub ComboBox1_Change()
        Dim xCombox As OLEObject
        Dim xStr As String
        Dim xWs As Worksheet
        Dim xArr

        Set xWs = Application.ActiveSheet
        On Error Resume Next
        Set xCombox = xWs.OLEObjects("ComboBox1")
        With xCombox
            .ListFillRange = "Opleiding"
            .LinkedCell = "$C$5:$J$5"
            .Visible = True
        End With
        If Target.Validation.Type = 3 Then
            Target.Validation.InCellDropdown = False
            Cancel = True
            xStr = Target.Validation.Formula1
            xStr = Right(xStr, Len(xStr) - 1)
            If xStr = "" Then Exit Sub
            With xCombox
                .Visible = True
                .Left = Target.Left
                .Top = Target.Top
                .Width = Target.Width + 5
                .Height = Target.Height + 5
                .ListFillRange = xStr
                If .ListFillRange = "Opleiding" Then
                    xArr = Split(xStr, ",")
                    Me.ComboBox1.List = xArr
                End If
                .LinkedCell = Target.Address
            End With
            xCombox.Activate
            Me.ComboBox1.DropDown
        End If
    End Sub
    Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Select Case KeyCode
            Case 9
                Application.ActiveCell.Offset(0, 1).Activate
            Case 13
                Application.ActiveCell.Offset(1, 0).Activate
        End Select
    End Sub

    Friday, August 23, 2019 12:26 PM