Excel VBA - Capitalizing all selected cells in column on double click

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

I can think of a workaround using `SelectionChange`, but that would need a triple click I guess — sam092, Feb 14 '14 at 03:23
@sam092 Please post the code as an example. If nobody comes up with anything better, I will accept that solution :) — julumme, Feb 14 '14 at 04:18
Why not a Shortcut key which when pressed will do what you need? — Siddharth Rout, Feb 14 '14 at 07:10

score 1 · Answer 1 · edited May 08 '18 at 21:49

1

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.

edited May 08 '18 at 21:49

Community

1
1

answered Feb 14 '14 at 04:29

V.B.

6,236
1
33
56

I'm new to this VBA stuff, but for example holding a control or shift key and pressing the column field would work perfectly fine instead of double click I think. Is there a simple call to check some key state ? (Instead of importing a whole module) – julumme Feb 14 '14 at 04:36
well if you new then importing a module and using an API functions could be useful by itself as an exercise :) Do you need help for importing the module and checking for conditions? – V.B. Feb 14 '14 at 04:40
I guess it's not really so much about importing problem, but people being annoyed if they have to look at big chunks of imported code what it does since they don't like unknown things :) – julumme Feb 14 '14 at 05:07
1

The chunk is not that big. Also I could say that if your work with VBA is not a one-off event then that website offers a lot of high quality pieces of VBA code that worth it to investigate. But the simplest method to achieve capitalization in-plcae is a Macro + shortcut key that you could assign to an existing macro via Alt+F8. – V.B. Feb 14 '14 at 05:14
Thank you V.B. for your help VB. It was a very good suggestion. I decided to take the shortcut + column suggestion as the answer as functionally it was closest to what I had in mind. But still, thank you – julumme Feb 17 '14 at 00:39

score 1 · Accepted Answer · edited May 23 '17 at 12:05

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

enter image description here

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:

enter image description here

Ah, this is great compromise, I will accept this. Thanks! – julumme Feb 17 '14 at 00:38 — julumme, Feb 17 '14 at 00:38

Excel VBA - Capitalizing all selected cells in column on double click

2 Answers2

Linked