Excel VBA - using all selected cells in a double-click column

Question

I have a very simple VBA script that capitalizes the selected cell:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  ActiveCell.Value = UCase(ActiveCell.Value)
End Sub

It works as expected, but sometimes I would like to use all the cells in the selected column, but only if I double-click on the column itself. It seems that I cannot receive events with Worksheet_BeforeDoubleClickwhen clicking the fields of a column.

Is there any workaround for this?

Answer 1

As I mentioned, why not use the keyboard shortcut ?. You can assign a key combination for a macro, as shown below.

Now you only need to select the column and press the shortcut key.

, , , , HACK .

.

Sub ChangeToUpper()
    Dim rng As Range

    '~~> Check if what the user selected is a valid range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If

    Set rng = Selection

    rng = WorksheetFunction.Transpose(Split(UCase(Join( _
          WorksheetFunction.Transpose(rng), vbBack)), vbBack))
End Sub

:

Answer 2

DoubleClick , BeforeRightClick. , Ctrl/Alt/Shift

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim condition As Boolean
    condition = True ' check Ctrl/Alt/Shift using http://www.cpearson.com/excel/KeyTest.aspx
    If condition Then
        MsgBox "Right Click at " & Target.Address
        Cancel = True
    End If
End Sub

- Ctrl+[] Selection.