Excel Spreadsheet, Change text case using VBA 4

[Kenny2003]

Hi,

Does anyone know a handy bit of code that I can put in a VBA module for a Excel Spreadsheet that actually changes any text to Uppercase and/or Sentence Case (as opposed to displaying text in a different case).

Any suggestions would be great,

Thank you In advance,

Kenny

 

[lucrahouse]

Here are simple examples of changing case

Sub MakeUpper()
MyValue = ActiveCell.Value
MyNewValue = UCase(MyValue)
ActiveCell.Value = MyNewValue

End Sub
Sub MakeLower()
MyValue = ActiveCell.Value
MyNewValue = LCase(MyValue)
ActiveCell.Value = MyNewValue
End Sub

Changing to sentence case requires reviewing each character.
More later.

 

[dan1967]

I actually have a utility that changes the case of selected MS Excel cells. If you would like, go to my profile and follow the link to my GeoCities site so you can download and install it. Click on the link for the script page. It is an Excel addin file (.XLA).

The basic command for this is:
UpperCase:

Selection.Value = UCase(Selection.Value)

Formal Case (there is no formal case function, so you have to search each cell for a space, then the next character is upper case):

Dim J, K, L, Cha()
K = Len(Selection)
ReDim Cha(K)
For J = 1 To K
     If J = 1 Then
          Cha(J) = UCase(Left(Selection, 1))
     ElseIf L > 1 And Mid(Selection, J - 1, 1) = " " Then
          Cha(J) = UCase(Mid(Selection, J, 1))
     Else
          Cha(J) = LCase(Mid(Selection, J, 1))
     End If
Next J
Selection = ""
For L = 1 To K
     Selection = Selection & Cha(L)
Next L

Basically what this is doing is cycling through each character in a string (your cell) and if it is the first character, or is preceded by a space, it makes it upper case. Other wise, it turns it lower case.

Dan.


Dan.

http://www.geocities.com/datamasterfla

 

[xlbo]

Actually Dan - this is all you need to make a proper case:

Selection.Value = WorksheetFunction.Proper(Selection.Text)

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

Good one Geoff,

I am always forgetting to use the worksheet functions.

Thanks

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[dan1967]

Excellent! Thanks, Geoff.

Dan.

 

[Kenny2003]

To Everyone

I would just like to say a BIG thank you to you all for taking the time to help me out, I am very grateful and my spreadsheet is now exactly how I wanted it.

Thanks again,

Kenny