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
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
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.
Like this thread? Share it with others