Forcing to Uppercase

[Connie Martin]

Quite some time ago I posted a question on this database regarding 'forcing
uppercase' and was given two macros, which I've used time and time again:
one to force the entire worksheet to uppercase and the other one was to
select just certain ranges to be uppercase, no matter how they were typed.
Neither one of the macros is quite right for what I need now. I need a macro
for just one cell.

Here's the macro I have to force the entire worksheet to uppercase:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
End Sub

This is the macro I have to force certain ranges to upper case:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.cells.count > 1 then exit sub
If Not (Application.Intersect(Target, Range("B18:B100,F18:F100")) Is
Nothing) _
Then
With Target
If Not .HasFormula Then
application.enableevents = false
.Value = UCase(.Value)
application.enableevents = true
End If
End With
End If
End Sub

How do I modify either macro to be just one cell?

Connie

 

[Mike H]

Maybe like this

If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
End Sub


Mike

 

[Connie Martin]

Mike, that doesn't appear to work. I changed the $A$1 to the applicable cell
in my worksheet which is I23. However, I23 is I,J,K & L merged. But when
you click in the merged cells it says I23. So maybe this is creating a
problem with your macro. I then wondered if I use the macro for a range and
include the range of the merged cells if it would work and it does. So, I
tried yours in a blank worksheet, using A1 but it doesn't seem to work.
Connie

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Range("A1")
If Not .HasFormula Then
On Error GoTo endit
Application.EnableEvents = False
.Value = UCase(.Value)
End If
End With
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Mike H]

Connie,

Just a personal opinion but merging cells will sooner or later bite you on
the leg and I wouldn't ever do it but there you are, that's just me. The
macro does work even on merged cells if you address the top left cell of the
merged group. the reason it probably isn't now is that events are probably
disabled. run this sub and try again

Sub hh()
Application.EnableEvents = True
End Sub