VBA: Move through combobox code with shift + tab (multiple cmds)

[exceliousss]

Hi i made a combobox, because i needed the autofill. Now i want to move through the combobox with shift + tab -> offset (0,-1)
My code so far:

Private Sub Manufacturer_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)'Moving through dropdown cell (keycode left/right arrow key + tab)
    Select Case KeyCode
        Case 9 'tab'
            Application.ActiveCell.Offset(0, 1).Activate
        Case 39 'right arrow key'
            Application.ActiveCell.Offset(0, 1).Activate
        Case 37 'left arrow key'
            Application.ActiveCell.Offset(0, -1).Activate
        Case 13 'enter'
            Application.ActiveCell.Offset(1, 0).Activate 
End Select
   End Sub

Thanks in advance!

 

[Yongle]

I do not understand what you are trying to achieve
Start at the beginning

1. Is the combobox on a userfrm or on a worksheet?
2. How many columns on combobox?
3. How many rows?
4. Why do you want to move through the combobox?
5. What is supposed to happen after you land on a value?

etc

 

[exceliousss]

Hi Yongle,


1. The combobox is on a worksheet
2. In the sheet there are
3. approx. 500-2000 rows
4. I dont want to use my mouse. Instead i want to make the combo-box work like a 'normal cell'
5. I want to type to get an exact value from a list. A normal dropdown doenst work like i want it to work and then you have to scroll down (not an option, because these lists have more than 20.000+ different values) and you dont have an autofill option.

Now i have the follow questions:
- Also if somebody knows the code how to 'move' through the box with shift + tab?
- Now i have a column with approx. 500 rows and i want to click and drag (or with ctrl c/ctrl v) to copy the value of 1 combo-box to all the other combo-boxes. is this possible?

VBA code below

[COLOR=#333333][IMG]http://icons.iconarchive.com/icons/double-j-design/ravenna-3d/24/File-Copy-icon.png[/IMG]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Manufacturer_Change()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Dropdown autofill list
    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("Manufacturer")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    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 = "" Then
                xArr = Split(xStr, ",")
                Me.Manufacturer.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.Manufacturer.DropDown
    End If
End Sub
Private Sub Manufacturer_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Moving through dropdown cell (keycode left/right arrow key + tab)
    Select Case KeyCode
        Case 9 'tab'
            Application.ActiveCell.Offset(0, 1).Activate
        Case 39 'right arrow key'
            Application.ActiveCell.Offset(0, 1).Activate
        Case 37 'left arrow key'
            Application.ActiveCell.Offset(0, -1).Activate
        Case 13 'enter'
            Application.ActiveCell.Offset(1, 0).Activate
    
    End Select
    
End Sub</code>[/COLOR]
[COLOR=#333333]
[B]​[/B][/COLOR]

Thanks in advance!

 

[Yongle]

Apologies
- hopefully someone else will come along very soon who understands what you want

Good luck