Text Formatting

Oh!Calcutta

New Member
Joined
Jun 15, 2011
Messages
23
Hi All!

Can you help me to write a Macro for texts (Address field) to be formatted in UPPER case only?

Thanks in advance.

:eek:
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Where is this Address field? Is it in a worksheet cell or in a userform text box or somewhere else?

In a worksheet cell, you could enforce upper case via Events:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range

Set rng = Application.Intersect(Target, Range("B:C")) 'Range("B:C") is where you want to enforce upper case

'check if any B:C cells changed:
If Not rng Is Nothing Then
    'turn off events (so dont get repeatedly fired as your code changes the cells)
    Application.EnableEvents = False
    On Error GoTo exit_here
    For Each cell In rng   'iterate thru the changed cells
        If cell.Value2 <> "" Then 'check it isn't blank
            If Not IsNumeric(cell.Value2) Then  'check it isn't a number
                cell.Value2 = UCase(cell.Value2)   'change to upper case
            End If
        End If
    Next cell
End If

exit_here:
Application.EnableEvents = True   'turn events back on

End Sub
Placed in the code module of a sheet (right-click sheet name, select View Code, paste in code into code module).
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
Can you help me to write a Macro for texts (Address field) to be formatted in UPPER case only?
What column is the Address field in? Does that column have a Header cell? If so, did you want it made into upper case also? If not, how many header rows are there?
 

Oh!Calcutta

New Member
Joined
Jun 15, 2011
Messages
23
What column is the Address field in? Does that column have a Header cell? If so, did you want it made into upper case also? If not, how many header rows are there?
Thank you Firefly & Rick, for your prompt replies.
I have 2 address columns (1 for full postal address & the other for ZIP Codes) in the worksheet. I do have a header row which I don't want to capitalize. Hope I've been able to clarify your queries.

Thanks & Regards.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
Thank you Firefly & Rick, for your prompt replies.
I have 2 address columns (1 for full postal address & the other for ZIP Codes) in the worksheet. I do have a header row which I don't want to capitalize. Hope I've been able to clarify your queries.
You did not say which column your addresses were in, so I am assuming Column C (highlighted in red) in my macro code below (change as needed)...
Rich (BB code):
Sub MakeAddressesUpperCase()
  Dim Addr As String
  Addr = "C2:C" & Cells(Rows.Count, "C").End(xlUp).Row
  Range(Addr) = Evaluate("IF(LEN(" & Addr & "),UPPER(" & Addr & "),"""")")
End Sub
 

Oh!Calcutta

New Member
Joined
Jun 15, 2011
Messages
23
Dear Rick,

Thanks very much. Your assumption of Address in Column C is accurate! I omitted to mention the same. Hats off to you!

:eek:
 

Forum statistics

Threads
1,085,419
Messages
5,383,546
Members
401,835
Latest member
Bweston07

Some videos you may like

This Week's Hot Topics

Top