Change Case

[Jayden]

I am just wondering if there is a way to change the font case in excel.

This is want I have. I have created a template for which sorts info supplied form a email. What I do is copy the entire email and past it into a spacific cell in a spreadsheet. Then the formulas that I have set up put the email into a better format. The problem is that not all the e-mails are the same case some have upper case and some are all lower. Is there a way that I can change them automaticly with excel instead of retyping them?


thanks

 

[Greg Truby]

Hiya Ponsy, good catch in that it really should pop the error message when one does that. However, I just altered the line of code that read:

If r1 Is Nothing Then

to read

If r1 Is Nothing Or Intersect(r1, Selection) Is Nothing Then

and it fixed it. The bit later on:

If Not Intersect(r1, r2) Is Nothing Then

keeps it from expanding like that, so even though the macro didn't pop the error message, it did not alter the text in cells that were not selected. You did hit upon one of the bits that was a challenge to work around - the tendency of the SpecialCells function to want to expand if one only had one cell. Nice to see folks are keepin' me honest.

 

[Ivan F Moala]

Also another option ... this Addin

http://www.xcelfiles.com/VBA_Quick16.html

 

[Legacy 21301]

Also another option ... this Addin

http://www.xcelfiles.com/VBA_Quick16.html

Yes, very nice.

EXCEPT : if only one cell is selected and it happens to contain a formula, the formula gets removed and the cell gets converted to the value only.

 

[Greg Truby]

Hi Ivan,

I like the copy code to clipboard button BTW, very considerate.

In addition to the item Ponsy mentions above, just a couple of thoughts: <ul>[*]You didn't check TypeName(Selection), so if Selection is a TextBox, for example, you get the old "End/Debug" box.[*]If one cell is selected and it's a merged cell, this will select all the cells on the WS that are text constants. While I was writing mine, merged cells were a pain in the neck. They're the reason behind my line If r2.Count > r1.Count Then Set r2 = r1 reading the way it does, instead of "> 1" [/list]I hope this post doesn't come off as rude. I only brought these up, should you ever be "under the hood" on that one and make some adjustments. In spite of a couple of flaws it's still a nice little utility (though I think I like mine a bit better 'cause I have it tied to a toolbar button and occasionally I use it on shapes and charts too).

 

[Ivan F Moala]

Guys ..... g8 thanks for Feedback.

Greg ... the Addin actually does handle the merged cells.
The code on the site needs to be updated to the Addin code, thanks
It doesn't handle Textboxs etc by design, hence the OnError
just to handle these situ.

Ponsnob ..... thanks I'll amend didn't look @ single cell formulas as I would have summised a user would select a Text cell .... but this can happen.

thanks again