Excel Macro to change case. Working but not perfect.

I am creating a Macro to change text to Upper Case, then if you run the macro again to Lower Case, then if you run the macro again to Title Case. These functions does not exist in the Excel 2003 interface. So I am making a Macro that eventually I want to turn into a button and after that a plug-in but I'll deal with that when I get there. The idea is to change case of text easily and it be so simple that my not tech savvy co-workers can use it too.

The below macro works just fine EXCEPT when you don't have more than one cell selected. When you run the macro with only one cell selected, the macro turns the whole worksheet to Upper Case! How do I fix that?

Please Login or Register  to view this content.

Hi and welcome to the forum.

When you post you need to surround your code using code tags to make it easier for everyone to read. Have a look at the forum rules to see how.

When you're using SpecialCells you have to be careful about a couple of things:
(1) How many cells the parent range has; and
(2) [Pre Excel 2010] How many non-contiguous cells it might have to return.

These items and the results are documented in post #2 here.

Using your current methodology as a basis, what you'll need to do is check if Selection is only one cell and, if it is, you don't use SpecialCells - you just check whether or not it contains a constant string.

Overall, I think you end up with something like this:

Please Login or Register  to view this content.

Hi,

The next step for me is to make this into a plug -in for Excel 2003 so I don't have to open the excel file every time I use the macro. Any advice on this? I have never made a plug-in.

If this was just for your own use then you could put the code in your Personal.xls, attach it to a commandbarbutton and be done: quite straightforward.

However, in your first post you mentioned that you want your colleagues to be able to use it too. Rather than having everyone set up their own personal.xls, you're probably going to want to create an xla add-in. This is a little bit tricky - particularly when it comes to updating - so will be a learning curve for you.

As a starting point, have a read though these articles:

http://www.ozgrid.com/VBA/excel-add-in-create.htm
If you all have a common server then you'll probably look to save a copy of the add-in on it and then everyone can reference the one file. Provided there isn't a copy on each person's C:\, if you update the network stored add-in then everyone will automatically get the update when they next re-open Excel.

http://www.excelguru.ca/node/45

Originally Posted by Colin Legg

http://www.excelguru.ca/node/45

I am currently trying to get the Deploy Add In code to work. but for some reason the code catches an Error 53 File not found at the setAttribute to normal line(SetAttr strAddinPublicPath & .Name, vbNormal).

I tried placing this macro in the development excel sheet and in it's own excel sheet too, neither worked. Any help please.

Darryl

Please Login or Register  to view this content.

Hi Darryl,

See if this improves it:

Please Login or Register  to view this content.