willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 886
- Office Version
- 365
- Platform
- Windows
I wanted to have data validation that could auto predict/fill in as you typed so I followed instructions online on how to do this. It worked perfectly however another code that I had no longer executes (it is the last part of the code that looks in column V for the word "Open" and calls a Macro.
Note: Column V also has the data validation combo box
Is there any way to fix it so both these codes work? Any help would be appreciated.
Thank you
Carla
Note: Column V also has the data validation combo box
Is there any way to fix it so both these codes work? Any help would be appreciated.
Thank you
VBA Code:
Private Sub TempCombo_Change()
End Sub
Private Sub Worksheet_SelectionChange(ByVal target As Range)
'Update by Extendoffice: 2018/9/21
' Update by Chris Brackett 2018-11-30
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Dim xCombox As OLEObject
Set xCombox = xWs.OLEObjects("TempCombo")
' Added this to auto select all text when activating the combox box.
xCombox.SetFocus
With xCombox
.ListFillRange = vbNullString
.LinkedCell = vbNullString
.Visible = False
End With
Dim xStr As String
Dim xArr
If target.Validation.Type = xlValidateList Then
' The target cell contains Data Validation.
target.Validation.InCellDropdown = False
' Cancel the "SelectionChange" event.
Dim Cancel As Boolean
Cancel = True
xStr = target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = vbNullString 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 = vbNullString Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 ' Tab key
Application.ActiveCell.Offset(0, 1).Activate
Case 13 ' Pause key
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
Private Sub Worksheet_Change(ByVal target As Range)
If target.CountLarge > 1 Then Exit Sub
If Not Intersect(target, Range("I:I")) Is Nothing Then
If target <> "" Then Call Issued_To
ElseIf Not Intersect(target, Range("V:V")) Is Nothing Then
If target.Text = "Open" Then Call Complete_File
End If
End Sub
Carla