text in caps

[Guest]

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

 

[Guest]

On 2002-02-27 15:53, Anonymous wrote:

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

This is because you have your Move Selection After Entry set to Down. The posted macro works if Move Selection After Entry is unchecked. I'm not sure whether your suggested amendment fixes it - no time to test it right now.

 

[Guest]

Still want work

 

[Guest]

On 2002-02-27 17:09, Anonymous wrote:
Still want work

What, even after un-checking MoveSelectionAfterEntry?

 

[Guest]

The marco crashes when it gets to For Each cell In rng2

 

[NateO]

You need the version with the errortrapper. Before you place it in your worksheet code run this in a normal module:

sub workit()
application.enableevents = true
end sub

Then, replace the code you were working on with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, cell As Range
On Error GoTo errorhandler
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
errorhandler:
Application.EnableEvents = True
End Sub

HTH (For Chr&$t's Sake) (j/k),

Nate
This message was edited by NateO on 2002-02-27 19:35

 

[Guest]

Ran the code first still want work

 

[NateO]

You ran sub workit() first and (while it worked before) the errortrapper caused it to fail? Can't be...I'm at a loss....Sorry.

Either way, Cheers, Nate
This message was edited by NateO on 2002-02-27 19:46

 

[Paul B]

Just thought I would let you know it does not work for me . I am using Excel '97

 

[Guest]

I knew I could not be the only one! I see a lot of people have looked at this post, I wonder how many it works for and how many it does not?

 

[Ivan F Moala]

On 2002-02-27 19:43, Anonymous wrote:
Ran the code first still want work

When using the event procedures you should
always use the events Target.....change
the Selection to target and see if this works

eg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, cell As Range

On Error GoTo errorhandler
Application.EnableEvents = False
If Not Intersect(Target, Columns(5)) Is Nothing Then
Set rng1 = Intersect(Target, 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
errorhandler:
Application.EnableEvents = True
End Sub


HTH

Ivan