VBA Code to force UpperCase in cells.

[StuartUK]

Morning all
Hopefully a nice easy one.....

When a user enters letters into a cell, i want it so that if they forget to type in caps, it automatically changes the text to UPPERCASE
specifically between this range: B5:BK16

I had the code which i found looking through forums, but deleted the code and cant remember it! doh!
any help appreciated

 

[thunder_anger]

Nice Work guys
i was looking for it before and i used codes that all came with errors
i wish i code like that

 

[Peter_SSs]

You could also use Data Validation to try to force upper case entry. For example in A1, Custom Data Validation formula of:

=EXACT(A1,UPPER(A1))

Neither the Data Validation method nor the vba method is foolproof.

Data Validation can be 'defeated' if the user pastes values since the paste also removes the existing Data Validation.

The vba is 'defeated' if the user does not enable macros.

 

[thunder_anger]

Neither the Data Validation method nor the vba method is foolproof.

is there a solution for that like converting an XLS file to an EXE or what??

 

[SuperFerret]

I did consider using Data Validation, but unfortunately people here seem to like cutting and pasting far too much but I've managed to get them to enable macro's whenever using this Workbook so I shouldn't have that problem.

is there a solution for that like converting an XLS file to an EXE or what??

Using a workaround for stopping all functionality if macro's aren't enabled works for me

 

[thunder_anger]

Using a workaround for stopping all functionality if macro's aren't enabled works for me

How ??

 

[Peter_SSs]

is there a solution for that like converting an XLS file to an EXE or what??

I'm not sure about converting to an exe, that is not an area of expertise of mine.

It is not all that straight-forward, but it is possible to force users to enable macros. Search this forum and you should find several threads where this has been discussed. You would find many other suggestions/discussions about this at various other locations. For example, here are two:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

http://answers.microsoft.com/en-us/office/forum/office_2007-customize/excel-guru-force-enable-macro-solution-seriously/cdf9885f-f4e3-4424-8fab-7adc541d1c6f

 

[SuperFerret]

If you search the forum there are a few threads (as an example):
http://www.mrexcel.com/forum/showthread.php?t=109769
http://www.mrexcel.com/forum/showthread.php?t=255533

There are some issues with these (saving: do you want to or not) and I only use them for people working inside the same office as me, some businesses won't allow use of macro's so ensure you're not breaking any rules

 

[DavidWF]

I have a similar situation to the OP except that the data I need in upper case is obtained by vlookup formulae on the worksheet. The source data is in mixed case.

The coding given by Peter converts my data to upper case OK but it overwrites the vlookup formula with the upper case value. That effectively makes the worksheet a single use sheet as the vlookup formula is no longer there.

Can the coding be amended so that the underlying vlookup formula remains in place but the output is capitalized - or am I asking the impossible?

 

[Rick Rothstein]

I have a similar situation to the OP except that the data I need in upper case is obtained by vlookup formulae on the worksheet. The source data is in mixed case.

The coding given by Peter converts my data to upper case OK but it overwrites the vlookup formula with the upper case value. That effectively makes the worksheet a single use sheet as the vlookup formula is no longer there.

Can the coding be amended so that the underlying vlookup formula remains in place but the output is capitalized - or am I asking the impossible?

Can't you just wrap your formula with the UPPER function?

If you have this...

=YourFormula

then change it to this...

=UPPER(YourFormula)

 

[DavidWF]

OMG - it's that simple!!

Thank you so much Rick - that's another little boost to my Excel understanding.