Excel VBA - 双击大小写列中的所有选定单元格 [英] Excel VBA - Capitalizing all selected cells in column on double click

问题描述

我有一个非常简单的VBA脚本，可以使用所选单元格：

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

它按预期工作，但有时我想将所有列中的所有单元格大小写，但只有当我双击列本身时。看来，当点击列字段时，我不能收到带有 Worksheet_BeforeDoubleClick 的事件。

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..

是否有一些解决方法？

推荐答案

像我提到的，为什么不是快捷键？您可以为您的宏分配一个快捷键，如下所示

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.

此外，代替循环遍历列中的每个单元格，这里是一个基于 彼得Albert 。

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.

将其放在一个模块中。

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

截图：

这篇关于Excel VBA - 双击大小写列中的所有选定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！