CODEWITHSUNDEEP
vbaexcelevents
Federico Sanchez
Federico Sanchez

Reputation: 145

Worksheet_BeforeDoubleClick for a selection

i have the below code that doesn't work for a selection:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng1 As Range
Dim I As Integer

If Selection.Count > 1 Then

    For Each cell In Selection
        If I = 0 Then
        Set Rng1 = Range(cell.Address)
        Else
        Set Rng1 = Union(Range(cell.Address), Rng1)
        End If
    I = I + 1
    Next cell
    MsgBox "You have selected the range " & Rng1.Address(False, False)
    Rng1.End(xlDown).Offset(0, 1).Activate
Else
    MsgBox "you are in " & ActiveCell.Address(False, False)
    ActiveCell.Offset(1, 0).Activate
End If


End Sub

I tried selecting a range of cells and then double clicking, does anybody know if that is even possible?

Upvotes: 1

Views: 1040

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Because double-clicking collapses any multi-cell selection you have previously made, you'd need to keep track of it, and then check whether Target is within it.

Something like:

Dim rng As Range

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not rng Is Nothing Then
        If Not Application.Intersect(Target, rng) Is Nothing Then
            Debug.Print "Clicked in selected range: " & rng.Address()
        Else
            Debug.Print "Cell: " & Target.Address()
        End If
        Set rng = Nothing
    Else
        Debug.Print "No previous range: clicked in " & Target.Address()
    End If
End Sub

'keeping track of the last multi-cell range selected....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Set rng = Target
End Sub

Upvotes: 3

Related Questions