Excel VBA - Capitalizing all selected cells in col

问题:

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 capitalize all cells in a selected column, but only if I double click on the column itself. It seems that I cannot receive events with Worksheet_BeforeDoubleClick when clicking the column fields..

Is there some workaround for this?

回答1:

Like I mentioned, Why not a shortcut key?. You can assign a shortcut key for your macro as shown below

Now all you have to do is select the column and press the shortcut key.

Also, instead of looping through every cell in a column, here is a code which is based on a ONE LINER HACK by Peter Albert.

Put this in a module.

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

Screenshot:

回答2:

If DoubleClick is not mandatory, you could use BeforeRightClick. If you want to keep original right click context menu, you could import the module and check for 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

Another option is to assign a Ctrl+[] in Macro options to a macro instead of an event handling and call the macro to process the Selection object.