Last part of VBA code no longer executes

[willow1985]

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

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

 

[DanteAmor]

Add this line:

If Not Intersect(Target, Range("V:V")) Is Nothing Then Exit Sub

You probably have to re-create the data validation combo box of the cells in column V.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2018/9/21
' Update by Chris Brackett 2018-11-30

  If Not Intersect(Target, Range("V:V")) Is Nothing Then Exit Sub

  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

 

[willow1985]

I originally had only this code:

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

However I realized that I have a lot of data validation lists on the spreadsheet that can be very long so I wanted to add something that would make it so the user doesn't have to scroll down long lists and could just start typing what they want.

I want the codes to be separate though (not to affect each other).

I still need Macro "Issued_To" to be called when Column I is not blank and Macro "Complete_File" to be called when "Open" is selected in column V.
I was just hoping I could add a code that would make all data validation lists have an auto predict for text (which means merging with a combo box according to what I read online)

The code you put above doesn't mention anything about calling macros etc.

That combo box code is new to me and I followed instructions online...

 

[willow1985]

....and now my bosses have decided to shorten the data validation lists so I don't need to make them autocomplete anymore....

Sorry for wasting your time

Thank you for your help however, I do appreciate it

 

[DanteAmor]

Don't worry, you're not the only one with bosses that change and change things, hehe, (I hope this is not read by bosses ?)

I remain at your disposal for any questions.