Worksheet_SelectionChange(ByVal Target As Range), Two Cells in Target

Question

I am using the event handler, and I run certain events if either one cell or two cells are selected. The issue I'm having is, when two cells are selected, I don't know how to access the attributes of that 2nd cell (Ie, what it's value is). Any idea how I can access the value of the 2nd cell thats selected (I was hoping Target would be an array object, and I could just select by array index....)

Public Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo disError
If Target.Cells.Count > 2 Or Target.Address = Range("DataHist").Address Then Exit Sub

Dim curve As String
Dim Ticker As String
Dim TickerTwo As String
Dim lastValue As Double
TickerTwo = ""

If Target.Cells.Count = 1 Then
    Ticker = Target.Value
    lastValue = Round(Target.Offset(0, 1).Value, 3)
    curve = CheckLabel(Target)
Else
    ' This is where the issue is --------------------------------
    Ticker = Target.Cells(1, 1).Value
    TickerTwo = Target.Next.Value
    lastValue = Round(Target.Offset(0, 1).Value, 3)
    curve = CheckLabel(Target)
    ' -----------------------------------------------------------
End If

Select Case curve
    Case "na"
        Exit Sub
    Case "Test1"
        Call FillChart("Test1", Ticker, lastValue, TickerTwo)
    Case "Test2"
        Call FillChart("Test2", Ticker, lastValue, TickerTwo)

End Select

disError:
End Sub

Gary's Student Gary's Student · Accepted Answer · 2018-01-17T19:57:42

If you don't know if the User is going to select cells in a column or cells in a row, or even a block of cells, use a loop and counter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, i As Long
    i = 1
    If Target.Count > 1 Then
        For Each r In Target
            MsgBox r.Address(0, 0) & vbCrLf & i
            i = i + 1
        Next r
    End If
End Sub

Then process when i=2. It will be the cell-to-the-right if a block or row is selected or the cell-below if part of a column is selected.

While this is not pretty code, at least it will work, even if the User selects a disjoint set of cells.

EDIT#1:

You can avoid the ugly loop if you are willing to parse Selection.Address

EDIT#2:

This code (without any loops) will work if the User selects 2 and only 2 cells:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim s As String
    If Target.Count = 2 Then
        s = Split(Replace(Target.Address(0, 0), ":", ","), ",")(1)
        MsgBox "The second cell is: " & s
    End If
End Sub

Worksheet_SelectionChange(ByVal Target As Range), Two Cells in Target

1 Answers