text in caps

[Guest]

I want what to make sure the text in column E is caps how can this be done? Thanks

 

[gypo]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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<pre><font color='#000000'><hr align=left width=500><font color='#000080'>Private</font><font color='#000080'>Sub</font> Worksheet_Change(ByVal Target<font color='#000080'>As</font> Range)<font color='#000080'>On</font><font color='#000080'>Error</font><font color='#000080'>GoTo</font> HandleErr<font color='#000080'>Dim</font> cell<font color='#000080'>As</font> Range

Application.EnableEvents = False<font color='#000080'>For</font><font color='#000080'>Each</font> cell In Target<font color='#000080'>If</font> cell.Column = 5<font color='#000080'>Then</font><font color='#000080'>If</font> cell.Formula = ""<font color='#000080'>Then</font>
cell.Text = UCase(cell.Text)<font color='#000080'>End</font><font color='#000080'>If</font><font color='#000080'>End</font><font color='#000080'>If</font><font color='#000080'>Next</font> cell

ExitHere:
Application.EnableEvents = True<font color='#000080'>Exit</font><font color='#000080'>Sub</font>
HandleErr:
MsgBox Err.Description
Resume ExitHere<font color='#000080'>End</font><font color='#000080'>Sub</font></font></pre>
This message was edited by Russell Hauf on 2002-02-27 13:03

 

[Guest]

<<<
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.