Hello All,
I have the following code I found years ago and I was trying to utilize it again for another application. I am now receiving some errors and I can only imagine it has something to do with newer versions of Excel. The code allows for a user to type text in a cell were a dropdown exists (without requiring a combo box) and auto completes the text as the user continues to type. Something is broken and I need assistance please.
'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
'Cancel As Boolean)
' On Error GoTo errHandler
'If Target.Validation.Type = 3 Then
' strr = Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
' End If
' UserForm1.Show
'exitHandler:
' Application.ScreenUpdating = True
' Application.EnableEvents = True
' Exit Sub
'errHandler:
' Resume exitHandler
'End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
strr =
Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
End If
UserForm1.Show
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True
If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("ComboBox1")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
'errHandler:
'Application.EnableEvents = True
' Exit Sub
End Sub '====================================
Private Sub ComboBox1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
I have the following code I found years ago and I was trying to utilize it again for another application. I am now receiving some errors and I can only imagine it has something to do with newer versions of Excel. The code allows for a user to type text in a cell were a dropdown exists (without requiring a combo box) and auto completes the text as the user continues to type. Something is broken and I need assistance please.
'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
'Cancel As Boolean)
' On Error GoTo errHandler
'If Target.Validation.Type = 3 Then
' strr = Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
' End If
' UserForm1.Show
'exitHandler:
' Application.ScreenUpdating = True
' Application.EnableEvents = True
' Exit Sub
'errHandler:
' Resume exitHandler
'End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
strr =
Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
End If
UserForm1.Show
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True
If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("ComboBox1")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
'errHandler:
'Application.EnableEvents = True
' Exit Sub
End Sub '====================================
Private Sub ComboBox1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub