.:++ Me+MySelf & I ++:.

dari dalam diri seorang aku..

Excel : Using Excel Macro & VB Change Case November 25, 2007

Filed under: Office — asriey @ 1:03 am

Just to share, I have minor problem with a document that all letters are in upper case. I want it to be in proper case. So do I need to type it all again? No!. I’m using VB Function to change the case.

In Excel, go to Tools->Macros->Visual Basic Editor or press ALT+F11. Insert new module, Insert->Module. Then paste the code below.

Sub ChangeCase()
Dim Rng As Range
On Error Resume Next
Application.EnableEvents = False
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
If Err.Number = 0 Then
‘ Rng.Value = StrConv(Rng.Text, vbUpperCase)
‘ Rng.Value = StrConv(Rng.Text, vbLowerCase)
‘ Rng.Value = StrConv(Rng.Text, vbProperCase)
End If
Next Rng
Application.EnableEvents = True
End Sub

Uncomment on the Rng.Value for whatever purpose you will use it for. Close VB Editor, to use it, on the document, press ALT+F8, a popup menu will appear, just choose ChangeCase in the function, select Run. Tadaaaaa. Hope it works for you as it works for me.

Reference: http://www.cpearson.com/excel/topic.aspx

Powered by ScribeFire.