Force Uppercase?

  • Thread starter Thread starter bonehead
  • Start date Start date
B

bonehead

Is there a fairly straightforward way to format cells to in order to
force uppercase, like you can in a Visual Basic text box, i.e., the user
types "c" and it's changed to "C"?
 
You can't use formatting, but you can use an event macro. One way:

Put this in the worksheet code module (right-click on the worksheet
tab, choose View Code, paste the code in the window that opens,
then click the XL icon on the toolbar to return to XL):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Application.EnableEvents = False
For Each cell In Target
With cell
If Not .HasFormula Then _
.Value = UCase(.Value)
End With
Next cell
Application.EnableEvents = True
End Sub

This allows lowercase in formulas, but not direct entries.
 
See http://www.cpearson.com/excel/case.htm

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

This will automatically change the case of the data when the user enters data in the range A1:A10.
Change this range to the range you need to use for your application
 
Hi

Not just like that, a cell does not trig any events or run any code until the entry is
totally completed. But then; paste in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Formula = UCase$(Target.Formula)
Application.EnableEvents = True
End Sub

Refint this with tests for target.count (=lots of cells pasted) and similar to suit your
needs.
 
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value) (...)
This will automatically change the case of the data
Click to expand...

.... and also formulas to static values.. Be careful with those .value things ;-)

Best wishes Harald
Followup to newsgroup only please.
 
Ron said:
See http://www.cpearson.com/excel/case.htm

1 Private Sub Worksheet_Change(ByVal Target As Range)
2 Application.EnableEvents = False
3 If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
4 Target(1).Value = UCase(Target(1).Value)
5 End If
6 Application.EnableEvents = True
7 End Sub
Click to expand...

Many thanks to Ron and all others who replied. And I've also taken the
cue and done a Google search and found several useful sites for future
reference.

I set up my worksheet to handle several discontiguous ranges by using a
succession of IfElse statements, but I wonder if I could just modify the
Range() argument in Line 7 instead? For example, can I name a set of
discontiguous ranges and then pass that range name to the Range() argument?
 
Back
Top