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
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("M:O")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Column
Case 11: If Target.Row <> 1 Then Target.EntireRow.AutoFit
Case Else: Rows.Resize(Rows.Count - 1).Offset(1).RowHeight = 25
End Select
Cancel = True
End Sub