Hi,

I want to know how can I create a macro or formula tha only accepts text in
capital letters?

Thanks

not exactly what you want, but you can use the =upper command to convert to
upper case after entry

"juanpablo" wrote:

Hi,

I want to know how can I create a macro or formula tha only accepts text in
capital letters?

Thanks

Worksheet change perhaps,

Right click sheet tab view code and psate this in

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 'change to suit
Target = UCase(Target)
Application.EnableEvents = True
End If
End Sub

Mike

"juanpablo" wrote:

Hi,

I want to know how can I create a macro or formula tha only accepts text in
capital letters?

Thanks

"Mike H" wrote...
Worksheet change perhaps,

Right click sheet tab view code and psate this in

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 'change to suit
Target = UCase(Target)
Application.EnableEvents = True
End If
End Sub
....

This is the most poorly written VBA code I've seen in a while.

The first time an entry were made outside of A1:A10 the statement

Application.EnableEvents = False

would run but not the one resetting this to TRUE, so once the user made an
entry outside of A1:A10, event handlers would be effectively disabled.

Second, there'd be runtime errors if users paste (or even enter using
[Ctrl]+[Enter]) into multiple adjacent cells. When transforming cell
contents or values, you have to iterate through each cell. If you didn't
know that Change events might have to handle multiple cell entries, you
shouldn't be trying to Change event handler code.

This should be


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, r As Range, ac As Variant

If IsEmpty(Target) Then Exit Sub

On Error GoTo CleanUp
Application.EnableEvents = False
ac = Application.Calculation
Application.Calculation = xlCalculationManual

Set r = Intersect(Target, Range("A1:A10")).SpecialCells( _
Type:=xlCellTypeConstants, Value:=xlTextValues)

If Not r Is Nothing Then
For Each c In r
c.Value = UCase(c.Value)
Next c
End If

CleanUp:
Application.EnableEvents = True
Application.Calculation = ac
Application.Calculate

End Sub

Can't imagine why anyone would want all CAPS but here is event code to do the
job.

Works on columns 1 through 8.............edit to suit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

As you enter text in any cell it will change to CAPS.


Gord Dibben MS Excel MVP

On Mon, 23 Jul 2007 09:02:05 -0700, juanpablo
wrote:

Hi,

I want to know how can I create a macro or formula tha only accepts text in
capital letters?

Thanks