text in caps

[gypo]

Post your address and I'll send you a workbook that includes the macro.

I don't know why I can't get it to work have tried everything that was posted send workbook to pabr_2001@yahoo.com Thanks

Sheet was e-mailed to me did not work, I don’t know what is happening! it is suppose to change what is typed in column E to caps right? Using Excel ’97. Again thanks for the help. Now I am very

Go to Tools>Macro>Security and change the setting from High to Medium.

I also did everything posted and security is set to medium. I'm using Excel 2000 and it does not work for me either.

now I know I am not the only one that it want work for!

Is your calculation set to automatic? (tools->Options->calculation->automatic).

Yes

[Russell Hauf]

Try the following. Formulas usually will automatically change to uppercase, and the parts that don't you usually don't want to make uppercase anyway (let me know if anyone can think of a reason for this). Hope this helps,

-Russell

Private Sub Worksheet_Change(ByVal Target As Range) 

On Error GoTo HandleErr 

    Dim cell As Range 

    

    Application.EnableEvents = False 

    

    For Each cell In Target 

        If cell.Column = 5 Then 

            If cell.Formula = "" Then 

                cell.Text = UCase(cell.Text) 

            End If 

        End If 

    Next cell 

    

ExitHere: 

    Application.EnableEvents = True 

    Exit Sub 

HandleErr: 

    MsgBox Err.Description 

    Resume ExitHere 

End Sub 

[ This Message was edited by: Russell Hauf on 2002-02-27 13:03 ]

<<<
Correction. Better make that :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, cell As Range
Application.EnableEvents = False
If Not Intersect(Selection, Columns(5)) Is Nothing Then
Set rng1 = Intersect(Selection, Columns(5))
Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
For Each cell In rng2
If cell.Formula <> "" Then
cell.Formula = Format(cell.Formula, ">")
End If
Next cell
End If
Application.EnableEvents = True
End Sub
>>>


Setting rng1 as the intersection of "selection" and the affected column has an unexpected result - it only affects the cell below the changed cell. By setting rng1 = columns (x) you won't miss any cells.