Force text to Uppercase in active cells

**Spellbound** · 01-28-2009, 12:45 PM

Hi all

To ensure correct data entry, I need to ensure that the filled cells in a column are always in Uppercase for example, irrespective of whether they are typed in upper or lower case.

I have found the following code, which seems to work quite well, even though I have the impression that it slowed my workbook down slightly.

Please Login or Register  to view this content.

Firstly, I would like to know if the code could be amended so that it only works on the cells in a column from the top of the column (either D1 or D2 down to the last occupied or active cell in that column.

Secondly, is it possible to have a combined code that also creates a capitalised word for each word in a column but for different specified colums.

In other words, if you have an address list where columns A, B, C & D are used for the main parts of the address; these would need the 1st letter to be capitalised and then Column E would contain the postcode, which needs to all in uppercase.

This code would need to be used in various worksheets within one workbook on different columns and I fopund the above code was very easy to customise by changing the column reference.

TIA ...spellbound

**MickG** · 01-28-2009, 01:07 PM

Hi ,Try this for columns "A to D" (In column "D" the entire string is Capitalised.)
You can change the data range and "All captals column" to suit.

Please Login or Register  to view this content.

Regards Mick

**royUK** · 01-28-2009, 01:11 PM

Have you tried

Please Login or Register  to view this content.

**Leith Ross** · 01-28-2009, 01:26 PM

Hello Spellbound,

This should slow you down. It will convert columns "A:D" to proper case (First letter of each word is capitalized and the rest lower case) while column "E" is converted to uppercase. Change the StartCell to the cell address the range starts at. It is set at "D2".

Please Login or Register  to view this content.

**Spellbound** · 01-30-2009, 10:43 AM

Hi Mick

Thanks for your suggestion but I could not get your code to work.

I clicked on the worksheet tab, then 'view code' and pasted it in as 'General' code, when that did not work, I also tried it as 'Worksheet Change' but that did not work either.

So not sure what else to do as my knowledge of VBA is zilch.

spellbound

**Spellbound** · 01-30-2009, 10:46 AM

Hi Roy

Thanks for your suggestion, which I tried out and this works on its own but as my knowledge of VBA is zero, so I could not figure out how to combine both statements in one piece of code.

The solution from Leith works with the combination that I need.

Always open to suggestions though!

spellbound

**Spellbound** · 01-30-2009, 10:58 AM

Hi Leith

Thanks for your piece of coding although I was very puzzled as to your initial comment in that you said "This should slow you down".

I was also quite puzzled as to why the range should start from D2 in your example, as from your description I would expect the start of this range to be A2.

So I have made the following adjustments, which seem to work but correct me if I have done them wrong.

For my particular worksheet, I need Columns B, C, D, E, F & G to be in Proper Case and Column H to be in Upper Case.

Please Login or Register  to view this content.

However, I also need to include a second range in the same worksheet, where Columns K, L, M, & N needs to be in Proper Case and Column O needs to be in Upper Case.

I assume that I could run a second piece of code as above but altered to accomodate the second range but is it possible to combine the two ranges within one piece of code.

TIA ...spellbound

**Leith Ross** · 01-30-2009, 12:49 PM

Hello SpellBound,

Here is the expanded macro code. I meant to type "shouldn't slow you down". The "D2" was to bring your attention to the variable in the code. Had I used "B2" you may not have paid attention to the variable StartCell.

Please Login or Register  to view this content.

**Spellbound** · 02-01-2009, 12:10 PM

Hi Leith

Thanks for that last bit of code, it works fine.

I also incorporated it in another worksheet as follows, which also works. As you will see I combined 2 ranges (forgot to split them up) which are not consecutive columns.

Please Login or Register  to view this content.

My first question is whether combining the ranges as above is likely to cause any problems. I know it only saves a few lines of code but just curious.

The other question that I have is if I were to this code on say 4 or 5 worksheets, would it be more efficient to have one piece of code referencing the different worksheets and ranges within the code.

If you think that this would be preferable, would it be possible to post a piece of revised code showing how this could be done.

TIA ...spellbound

**Leith Ross** · 02-01-2009, 02:33 PM

Hello Spellbound,

My first question is whether combining the ranges as above is likely to cause any problems.

The important rule to remember when using Select Case is the order of the Case statements is important. Once a Case tests True, the other Case statements are skipped.

The other question that I have is if I were to this code on say 4 or 5 worksheets, would it be more efficient to have one piece of code referencing the different worksheets and ranges within the code.

Since the macro is executing within the worksheet's Change() event procedure, little efficiency in speed would be lost by using a single macro in the Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
. As you can see, this allows you to see which range has changed on which sheet. This event is run after the Worksheet_Change() event.

In terms of coding efficiency, it is better. You have only one code routine and it is located in a single module ThisWorkbook. This makes maintenance easier in the long run. I'll post back with an example for you on how to code this.

**Leith Ross** · 02-01-2009, 02:53 PM

Hello Spellbound,

Here is the single macro for 2 sheets using the Workbook_SheetChange event. You can add more sheet names and ranges as needed. Add this code to the ThisWorkbook module.

Please Login or Register  to view this content.

**Spellbound** · 02-01-2009, 03:06 PM

Hi Leith

Thanks for quick response.

I was intrigued to see that the combined code does not make use of a StartCell and range for each worksheet, seems much simpler.

I will try out tomorrow and let you know how it goes.

Thanks ...spellbound

**Spellbound** · 02-07-2009, 12:30 PM

Hi Leith

The code you supplied works fine if you are just typing in data normally.

However, if you paste in data, it throws up the following error:

Run Time error code 13
Type: Mismatch

and the only way out of it is to 'step out' of the code, then close the file and re-open it in order for the code to carry on working again.

Any ideas ....spellbound

**Spellbound** · 02-08-2009, 04:54 PM

Leith

Any joy with this problem?

The same thing happens if you try to insert a new column in one of the specified columns in the code.

TIA ...spellbound

**Leith Ross** · 02-08-2009, 05:06 PM

Hello Spellbound,

I'll investigate why that's happening today and post the corrections. Thanks for the bump. The past few days have busy. My birthday was Friday and my family has kept me pretty busy.

**Leith Ross** · 02-08-2009, 05:19 PM

Hello SpellBound,

Found the problem. The type mismatch happened because the code was trying to assign the entire Column to a cell. This code fixes the problem...

Please Login or Register  to view this content.

**Spellbound** · 02-09-2009, 02:51 PM

Hi Leith

Tried out the new code but still having major problems with it, as shown in Sheet1 of the attached workbook.

To be on the safe side, I created a new workbook, which just included your piece of code, so that I could isolate the problem. Sheets 2 &3 are just backups of Sheet1 to monitor changes. I amended the code, so that it came out the same in all 3 worksheets.

Typed input works fine but you can see the results of copying text and inserting columns, which I have left as they ended up, together with my notes on how these things happened.

Hope you can resolve this ...spellbound

**Spellbound** · 02-10-2009, 06:39 PM

Hi Leith

Did you manage to have a look at the problem with the code.

TIA ...spellbound

**Spellbound** · 02-12-2009, 08:49 PM

Hi Leith

<< bump >>

any chance of fixing this code?

spellbound

**shg** · 02-12-2009, 09:11 PM

Please Login or Register  to view this content.

**Spellbound** · 02-12-2009, 10:15 PM

Hi shg

I pasted that code into "This Workbook" which works fine with typing in text plus copying/pasting worked as well. Minor problem in that when you insert a column, the mouse pointer gets the shakes and then returns to normal after a while, allowing you to continue editing.

FYI:- In one of the worksheets, I have to delete a column and then insert an updated column in the same place, so that the relevent columns still end up in the same place in relation to this code.

I also need to make this code worksheet specific, see earlier code from Leith, which allows me to customise the worksheet name and column numbers. I tried amalgamating the worksheet name part of Leiths code with your change case code but could not get it to work.

Would you be able to put this together for me.

Thanks ...spellbound

**shg** · 02-12-2009, 11:59 PM

I decline to read back through 21 posts in this thread, thanks.

If you state your current requirements succinctly vis a vis what I posted, I'll look at it.

**Spellbound** · 02-13-2009, 11:28 AM

Hi shg

Your code as it stands converts the case of the text in all of the applicable columns in every worksheet.

Lets say that these columns only apply to Sheet1 and in Sheet2, I need columns 1, 4, 5 to be in ProperCase and columns 3 and 6 to be in UpperCase and say in Sheet3, columns 5 and 10 need to be in UpperCase.

So, I am looking for a single piece of code that references each Sheet name and individual columns for that sheet, which I can then adjust to suit a particular workbook.

Hope this clarifies things …spellbound

**shg** · 02-13-2009, 02:49 PM

Please Login or Register  to view this content.

Note the code uses the CodeNames of sheets (what you see in the Project Explorer window to the left of the sheet name), not the sheet names.

**Spellbound** · 02-14-2009, 06:43 PM

Hi shg

Thanks for latest piece of code. It worked fine until I filled in the changes for the 3rd worksheet and then it came up with a couple of "Compile Errors".

I think that I solved the problem by adding in an extra "Next Cell" and "End Select" at the end (see below).

Please Login or Register  to view this content.

If I have done this correctly, please mark the thread as solved, if not, can you let me know where I have gone wrong.

Thanks again ...spellbound

**shg** · 02-14-2009, 06:52 PM

If it's doing what you want it to do, mon frere, you have done it correctly.

Good job.

Force text to Uppercase in active cells

LinkBack

Thread Tools

Rate This Thread

Display