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:
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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
36,675
Office Version
  1. 2010
Platform
  1. 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
36,675
Office Version
  1. 2010
Platform
  1. 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:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top