Last part of VBA code no longer executes

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
716
Office Version
  1. 365
Platform
  1. 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

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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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

Well-known Member
Joined
Jul 24, 2019
Messages
716
Office Version
  1. 365
Platform
  1. Windows
I originally had only this code:

VBA 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

Well-known Member
Joined
Jul 24, 2019
Messages
716
Office Version
  1. 365
Platform
  1. Windows
....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

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,354
Messages
5,624,195
Members
416,017
Latest member
moritz210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top