Capitalizing cells with double click on the whole column

[SuppoT]

I have a very simple code that capitalizes a call on a double click:

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

But sometimes, there are many fields in the column that I would like to capitalize, and in that case I would like to have a feature to double click the column itself (one of those "A", "B" etc cells that select the whole column), to capitalize all cells in this particular column.
The function BeforeDoubleClick doesn't seem to receive events from those column cells... is there some workaround for this?

Also, is there some function to capitalize the cells that are selected by click+drag?

 

[JoeMo]

A column is activated by a single click on the column header so the double-click event is not useful. See if this does what you want after you install it as sheet code. Trigger the code by clicking on a column letter.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
If Target.Address = Columns(ActiveCell.Column).Address Then
    For Each c In Intersect(Target, Me.UsedRange)
    c.Value = UCase(c.Value)
    Next c
End If
End Sub

 

[SuppoT]

Hi Joe,

Thanks for the reply!
Indeed this works, but now there might be accidental capitalization's, in case user just wanted to select some column for visibility purposes etc...

Is there no way to get double click from the column header?

 

[Rick Rothstein]

Hi Joe,

Thanks for the reply!
Indeed this works, but now there might be accidental capitalization's, in case user just wanted to select some column for visibility purposes etc...

Is there no way to get double click from the column header?

How about this as a compromise?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim Answer As Long
  Answer = MsgBox("Do you want to capitalize the whole column?" & vbLf & _
           "(Yes for whole column, No for cell only, Cancel to enter Edit Mode)", vbYesNoCancel)
  If Answer = vbYes Then
    Cancel = True
    With Intersect(Target.EntireColumn, ActiveSheet.UsedRange)
      .Value = Evaluate("IF(ROW(),UPPER(" & .Address & "))")
    End With
  ElseIf Answer = vbNo Then
    Cancel = True
    Target = UCase(Target)
  End If
End Sub