Change Text to Uppercase Excel 2003

[Chris Hankin]

Hello,

Could someone please advise on how I can change text to uppercase in
column B of my worksheet using Excel 2003 - I do not wish to add any
more columns, so I guess a VBA macro might be required? I usually enter
mixed-case text into a cell in column B, so I wish to have this mix-case
text automatically change to uppercase please.

Thanks,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

 

[Charlotte E.]

Just on top of my head (not tested):


Dim TCell As Range

For Each TCell In Range("B:B")
TCell.Value = UCase(TCell.Value)
Next


Give it a try

 

[Dave Peterson]

If you want it automatic, you can tie into a worksheet_change event. This runs
each time you make a change to the worksheet.

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the newly opened code window:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub 'only one cell at a time
End If

If Intersect(Target, Me.Range("b:b")) Is Nothing Then
Exit Sub 'only check column B
End If

If Target.HasFormula Then
Exit Sub 'don't touch formulas
End If

On Error GoTo ErrHandler:
Application.EnableEvents = False
Target.Value = UCase(Target.Value)

ErrHandler:
Application.EnableEvents = True

End Sub

If you like Edit|Undo, you may not like this. Most macros that do anything kill
the undo/redo stack.

 

[Chris Hankin]

Thanks Charlotte E. for your reply - I tried it out and it work fine -
so thanks again - really appreciated.


*** Sent via Developersdex http://www.developersdex.com ***

 

[Chris Hankin]

Hi Dave, thankyou so much for your help - really appreciated. Your code
works fine. Thanks again



*** Sent via Developersdex http://www.developersdex.com ***