Uppercase macro

mrpaulwass

Board Regular
Joined
Jul 9, 2002
Messages
60
Hello,

How can I write an uppercase or lowercase macro for the selected cells.

(UCASE & LCASE)

Thanks,

P
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
Here's the uppercase version:

<pre>
Public Sub main()
Dim oRange As Range
Dim oCell As Range

Set oRange = Selection

For Each oCell In oRange
oCell.Value = UCase(oCell.Value)
Next
End Sub</pre>

HTH
 

mrpaulwass

Board Regular
Joined
Jul 9, 2002
Messages
60
How can I apply this so that this script would be available to every spreadsheet that I open?

Thanks again!

Paul
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Hi try this link to any comtrol button and run will do two thinsg I HOPE

1 convert ALL cells with text to all upper
2 If cells selection = text willconvert ONLY These leaving the rest AS WAS

Is this waht you had in mind

PS the code JackintheUK is standard for me and sorry but i had to it ANNOYS some readers who choose to abuse me as iv e not idea of VBA, YES this code is PERFECT for you and written by Jack

Dedicate this code to all those who choose to abuse me and send nasty threatening & abuse posts and email to me, cheers guys you just make me post more PERFECT VBA !!!
This one is a touch of Jacks guru magic!!!



Sub Jacks_Upper_Test()
Dim Rng As Range
Dim Jack_in_the_UK As Range
Set Rng = Selection.SpecialCells(xlCellTypeConstants, 2)
For Each Jack_in_the_UK In Rng
Jack_in_the_UK.Value = UCase(Jack_in_the_UK.Value)
Next Jack_in_the_UK


End Sub
_________________<embed width="200" height="50" src="http://homepage.a5.com/~kristy/misc/jack.swf">
This message was edited by Jack in the UK on 2002-10-29 16:54
 

mrpaulwass

Board Regular
Joined
Jul 9, 2002
Messages
60

ADVERTISEMENT

Yes, this is what I had in mind, but I would still like to know how to have this apply to all spreadsheets. Not just the one where I created the macro.

Thanks,

P
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Yes, this is what I had in mind,
>> Good news im pleased

but I would still like to know how to have this apply to all spreadsheets.
OK If you want in all your sheet i suggest adding to personal XLS this will make the script i have written available to all wkbks and sheets.

Or if distributed then your find that the code will be in the wkbk you send by email [how ever]

or i could convert my code into a piece of software called XLA [that’s communally known as an add in] but you have to make sure all the guys with this xls have that add in els it wont work and they will need to add the add in the normal fashion.

Its a case of if you don’t have it - its wont work, as in no standard, you have to ADD IT somehow, these are a few easier ideas of how to achieve this

Not just the one where I created the macro.
>> As above hope thtas all OK


Thanks,

P
[/quote]
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

You will need to save the code as an add-in. After entering the code:

Close (X) the Visual Basic Editor. File/SaveAs and in the File Name box enter "whatever" (without quotes) and under Save as type choose Microsoft Excel AddIn (*.xla) - the final item on the drop-down list. The default file location (in Windows XP) is C:Documents and SettingsDefaultApplication DataMicrosoftAddIns. Close Excel.

Open Excel again. Tools/Add-Ins...

whatever should appear in the list of Add-Ins available; if not click Browse to locate the file. Tick whatever in the list of Add-Ins available and click OK.

whatever will be present in every workbook unless you unload the Add-In.
This message was edited by VoG™ on 2002-10-29 18:08
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
On 2002-10-29 15:48, Mark O'Brien wrote:
Here's the uppercase version:<pre>
Public Sub main()
Dim oRange As Range
Dim oCell As Range

Set oRange = Selection

For Each oCell In oRange
oCell.Value = UCase(oCell.Value)
Next
End Sub</pre>

HTH

Mark, disappointed... after your brief introduction to VBA you should know that working with a variant array would be MUCH better ! :p

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-10-29 18:33
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
JPG

Hi re your comments to Mark, do you have code in mind, can you pos it???

Kind regards
Jack
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Jack, I was just messing with Mark, but, it IS much better tho. Look at this. I tested it with 1000 cells (A1:A1000 and B1:B1000 for the second one). Compare the times.<pre>Sub TestUCase()
Dim Ar As Variant
Dim i As Long
Dim Rng As Range
Dim Cll As Range
Dim T As Long
Application.ScreenUpdating = False
'Testing For each Cll in Rng
Set Rng = Range("A1:A1000")
T = Timer
For Each Cll In Rng
Cll.Value = UCase(Cll.Value)
Next Cll
Debug.Print Timer - T

'Now, using the Array
Ar = Range("B1:B1000").Value
T = Timer
For i = LBound(Ar, 1) To UBound(Ar, 1)
Ar(i, 1) = UCase(Ar(i, 1))
Next i
Range("B1:B1000").Value = Ar
Debug.Print Timer - T
Application.ScreenUpdating = True
End Sub</pre>
 

Forum statistics

Threads
1,144,116
Messages
5,722,568
Members
422,447
Latest member
srclife

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