Multi-dependent Validation autocomplete VBA code targeting only certain colums and cells

[dhally]

Hello.
My spreadsheet contains regular validation and multi-dependent validation cells. I have a Autocomplete VBA code from Contextures which works great. The code is also modified to operate from a "single" mouse click.

My issue: I would like for the VBA code to be linked only to the columns that have mulit-dependent validation instead of the entire range of columns, which of course some contain other simple validated list's of 1-3 selections not needing autocomplete.

My spreadsheet column range is A:AC.
The targeted columns for the code would be M, Q, S, U and V

Any help with the modification of this code would be greatly appreciated.

Here is the code:

'==========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=========================================
Private Sub Worksheet(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'==========================

 

[p45cal]

Just a general way to restrict when/where things happen:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RangeToRespond As Range
Set RangeToRespond = Intersect(UsedRange, Range("M:M,Q:Q,S:S,U:V"), Target)
If Not RangeToRespond Is Nothing Then
    Debug.Print RangeToRespond.Address(0, 0)    'just to show it working.
    'rest of your processing here
   'eg.:
   'For each cll in RangeToRespond.cells
   '   cll.value = "yikes!"
   'Next cll
End If
End Sub

 

[dhally]

Ah...simple. I understand that! Thank you very much.