Text Formatting

Oh!Calcutta

New Member
Joined
Jun 15, 2011
Messages
26
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:
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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).
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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
Back
Top