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:
[TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/TD]
[/TR]
[TR]
[TD] Dim xCombox As OLEObject[/TD]
[/TR]
[TR]
[TD] Dim xStr As String[/TD]
[/TR]
[TR]
[TD] Dim xWs As Worksheet[/TD]
[/TR]
[TR]
[TD] Set xWs = Application.ActiveSheet[/TD]
[/TR]
[TR]
[TD] On Error Resume Next[/TD]
[/TR]
[TR]
[TD] Set xCombox = xWs.OLEObjects("OperCombo")[/TD]
[/TR]
[TR]
[TD] With xCombox[/TD]
[/TR]
[TR]
[TD] .ListFillRange = ""[/TD]
[/TR]
[TR]
[TD] .LinkedCell = ""[/TD]
[/TR]
[TR]
[TD] .Visible = False[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] If Target.Validation.Type = 3 Then[/TD]
[/TR]
[TR]
[TD] Target.Validation.InCellDropdown = False
[/TD]
[/TR]
[TR]
[TD] Cancel = True[/TD]
[/TR]
[TR]
[TD] xStr = Target.Validation.Formula1[/TD]
[/TR]
[TR]
[TD] xStr = Right(xStr, Len(xStr) - 1)[/TD]
[/TR]
[TR]
[TD] If xStr = "" Then Exit Sub[/TD]
[/TR]
[TR]
[TD] With xCombox[/TD]
[/TR]
[TR]
[TD] .Visible = True[/TD]
[/TR]
[TR]
[TD] .Left = Target.Left[/TD]
[/TR]
[TR]
[TD] .Top = Target.Top[/TD]
[/TR]
[TR]
[TD] .Width = Target.Width + 5[/TD]
[/TR]
[TR]
[TD] .Height = Target.Height + 5[/TD]
[/TR]
[TR]
[TD] .ListFillRange = xStr[/TD]
[/TR]
[TR]
[TD] .LinkedCell = Target.Address[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] xCombox.Activate[/TD]
[/TR]
[TR]
[TD] Me.OperCombo.DropDown[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
[TR]
[TD]Private Sub OperCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)[/TD]
[/TR]
[TR]
[TD] Select Case KeyCode[/TD]
[/TR]
[TR]
[TD] Case 9[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(0, 1).Activate[/TD]
[/TR]
[TR]
[TD] Case 13[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(1, 0).Activate
[/TD]
[/TR]
[TR]
[TD] Case 37[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(0, -1).Activate[/TD]
[/TR]
[TR]
[TD] Case 39[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(0, 1).Activate[/TD]
[/TR]
[TR]
[TD] Case 16, 9[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(0, -1).Activate[/TD]
[/TR]
[TR]
[TD] End Select[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/TD]
[/TR]
[TR]
[TD] Dim xCombox As OLEObject[/TD]
[/TR]
[TR]
[TD] Dim xStr As String[/TD]
[/TR]
[TR]
[TD] Dim xWs As Worksheet[/TD]
[/TR]
[TR]
[TD] Set xWs = Application.ActiveSheet[/TD]
[/TR]
[TR]
[TD] On Error Resume Next[/TD]
[/TR]
[TR]
[TD] Set xCombox = xWs.OLEObjects("OperCombo")[/TD]
[/TR]
[TR]
[TD] With xCombox[/TD]
[/TR]
[TR]
[TD] .ListFillRange = ""[/TD]
[/TR]
[TR]
[TD] .LinkedCell = ""[/TD]
[/TR]
[TR]
[TD] .Visible = False[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] If Target.Validation.Type = 3 Then[/TD]
[/TR]
[TR]
[TD] Target.Validation.InCellDropdown = False
[/TD]
[/TR]
[TR]
[TD] Cancel = True[/TD]
[/TR]
[TR]
[TD] xStr = Target.Validation.Formula1[/TD]
[/TR]
[TR]
[TD] xStr = Right(xStr, Len(xStr) - 1)[/TD]
[/TR]
[TR]
[TD] If xStr = "" Then Exit Sub[/TD]
[/TR]
[TR]
[TD] With xCombox[/TD]
[/TR]
[TR]
[TD] .Visible = True[/TD]
[/TR]
[TR]
[TD] .Left = Target.Left[/TD]
[/TR]
[TR]
[TD] .Top = Target.Top[/TD]
[/TR]
[TR]
[TD] .Width = Target.Width + 5[/TD]
[/TR]
[TR]
[TD] .Height = Target.Height + 5[/TD]
[/TR]
[TR]
[TD] .ListFillRange = xStr[/TD]
[/TR]
[TR]
[TD] .LinkedCell = Target.Address[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] xCombox.Activate[/TD]
[/TR]
[TR]
[TD] Me.OperCombo.DropDown[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
[TR]
[TD]Private Sub OperCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)[/TD]
[/TR]
[TR]
[TD] Select Case KeyCode[/TD]
[/TR]
[TR]
[TD] Case 9[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(0, 1).Activate[/TD]
[/TR]
[TR]
[TD] Case 13[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(1, 0).Activate
[/TD]
[/TR]
[TR]
[TD] Case 37[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(0, -1).Activate[/TD]
[/TR]
[TR]
[TD] Case 39[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(0, 1).Activate[/TD]
[/TR]
[TR]
[TD] Case 16, 9[/TD]
[/TR]
[TR]
[TD] Application.ActiveCell.Offset(0, -1).Activate[/TD]
[/TR]
[TR]
[TD] End Select[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]