Forcing capital letter

[videozvideoz]

How do I make a cell default all letters to capitals so that if someone were to type "Bacon" it would appear as "BACON" automatically? Or something like "ca 90210" would become "CA 90210"

Thanks

 

[njimack]

Paste the following into a sheet module:

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

 

[videozvideoz]

Thanks but it interferred with the execution of some other VBA code I already have in there despite it being seperated off by a line. Any ideas?

 

[njimack]

You won't do this without VBA. Post your existing code.

 

[videozvideoz]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Range("E:E,G:G,I:I, K:K, M:M, O:O, Q:Q, S:S, U:U, W:W, Y:Y, AA:AA, AC:AC")) Is Nothing Then
Range("C29").Value = Range("E5").Value
Else
Range("C29").Value = Cells(5, ActiveCell.Column).Value
End If
End Sub

 

[njimack]

Try this (untested)

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = UCase(Target.Value)

If Intersect(ActiveCell, Range("E:E,G:G,I:I, K:K, M:M, O:O, Q:Q, S:S, U:U, W:W, Y:Y, AA:AA, AC:AC")) Is Nothing Then
Range("C29").Value = Range("E5").Value
Else
Range("C29").Value = Cells(5, ActiveCell.Column).Value
End If
Application.EnableEvents = True
End Sub

 

[videozvideoz]

Right... it only forces a capital letter after I click back on the cell. For example i enter "ca 1111" in cell B1 and press return. It then only changes it to capitals when I click back on B1.

 

[njimack]

Give this a go - separate change and selection_change events, both setting EnableEvents to False at the start of the code...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(ActiveCell, Range("E:E,G:G,I:I, K:K, M:M, O:O, Q:Q, S:S, U:U, W:W, Y:Y, AA:AA, AC:AC")) Is Nothing Then
Range("C29").Value = Range("E5").Value
Else
Range("C29").Value = Cells(5, ActiveCell.Column).Value
End If
Application.EnableEvents = True
End Sub

 

[videozvideoz]

Thanks for this. It works but when I go to delete multiple cells at a time it comes up with a "type mismatch" error

 

[njimack]

Thanks for this. It works but when I go to delete multiple cells at a time it comes up with a "type mismatch" error

Slight tweak - the won't fire if more than one cell is selected

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(ActiveCell, Range("E:E,G:G,I:I, K:K, M:M, O:O, Q:Q, S:S, U:U, W:W, Y:Y, AA:AA, AC:AC")) Is Nothing Then
Range("C29").Value = Range("E5").Value
Else
Range("C29").Value = Cells(5, ActiveCell.Column).Value
End If
Application.EnableEvents = True
End Sub