Limit VBA code to a range of cells.

[andrewgirgis]

I have this code that i would like to limit to a range of cells (Column D) but i am not sure how to go about it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2017/8/15
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        Dim TargetName As String: TargetName = ""
        On Error Resume Next
            TargetName = Target.Name
        On Error GoTo 0
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 500
            .Height = Target.Height + 5
            .ListFillRange = xStr
            .LinkedCell = Target.Address

        End With
        xCombox.Activate
        Me.TempCombo.DropDown
        End If
End Sub

 

[mumps]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2017/8/15
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    If Target.Column = 4 Then
        Set xCombox = xWs.OLEObjects("TempCombo")
        With xCombox
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
        End With
        If Target.Validation.Type = 3 Then
            Target.Validation.InCellDropdown = False
            Cancel = True
            xStr = Target.Validation.Formula1
            xStr = Right(xStr, Len(xStr) - 1)
            Dim TargetName As String: TargetName = ""
            On Error Resume Next
                TargetName = Target.Name
            On Error GoTo 0
            If xStr = "" Then Exit Sub
            With xCombox
                .Visible = True
                .Left = Target.Left
                .Top = Target.Top
                .Width = Target.Width + 500
                .Height = Target.Height + 5
                .ListFillRange = xStr
                .LinkedCell = Target.Address
            End With
            xCombox.Activate
            Me.TempCombo.DropDown
        End If
    End If
End Sub