Autofill ComboBox/Data Validation does not work for numbers

[Rumpkin]

Hello,
I am using an Autofill Code for in a a dropdown list.
I have formulas in other cells that are dependent on the drop down cell entry and work when the Autofill Code and Combobox are removed.
The code works fine if the entry is alphanumeric but will not work if the entry is only numeric. Below is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim xCombox As OLEObject

Dim xStr As String

Dim xWs As Worksheet

Set xWs = Application.ActiveSheet

On Error Resume Next

Set xCombox = xWs.OLEObjects("OperCombo")

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

.LinkedCell = Target.Address

End With

xCombox.Activate

Me.OperCombo.DropDown

End If

End Sub

Private Sub OperCombo_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

Case 37

Application.ActiveCell.Offset(0, -1).Activate

Case 39

Application.ActiveCell.Offset(0, 1).Activate

Case 16, 9

Application.ActiveCell.Offset(0, -1).Activate

End Select

End Sub

<colgroup><col></colgroup><tbody>
</tbody>

 

[RobertSF]

I don't know the answer, but do yourself a favor and post readable code. That will make people who know more likely to stop and take the time to provide a solution.

Here's your code, unchanged but formatted and properly indented. I hope someone posts a solution for you.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    
    Set xWs = Application.ActiveSheet
    
    On Error Resume Next
    
    Set xCombox = xWs.OLEObjects("OperCombo")
    
    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
            .LinkedCell = Target.Address
        End With
        
        xCombox.Activate
        Me.OperCombo.DropDown
    
    End If
    
End Sub
[HR][/HR]
Private Sub OperCombo_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
    Case 37
        Application.ActiveCell.Offset(0, -1).Activate
    Case 39
        Application.ActiveCell.Offset(0, 1).Activate
    Case 16, 9
        Application.ActiveCell.Offset(0, -1).Activate
    End Select
    
End Sub

 

[Rumpkin]

Thanks,
I do not know how to post it other copy it in to the body of the email
Will you tell me how? I am running another worksheet routine so I wonder if that would interfere.

 

[Rumpkin]

Thanks. here is the corrected code in the correct format.

Private Sub OperCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
Dim varVal As Variant
On Error Resume Next
varVal = --ActiveCell.Value
If IsEmpty(varVal) Then
  varVal = ActiveCell.Value
End If

Select Case KeyCode
  Case 9  'tab
    ActiveCell.Value = varVal
    ActiveCell.Offset(0, 1).Activate
  Case 13 'enter
    ActiveCell.Value = varVal
    ActiveCell.Offset(1, 0).Activate
  Case 37
    Application.ActiveCell.Offset(0, -1).Activate
  Case 39
    Application.ActiveCell.Offset(0, 1).Activate
  Case 16, 9
    Application.ActiveCell.Offset(0, -1).Activate
  Case Else
    'do nothing
End Select
End Sub
Private Sub OperCombo_LostFocus()
  With Me.OperCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("OperCombo")
    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
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.OperCombo.DropDown
    End If
End Sub