.:++ 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
Err.Clear
Application.EnableEvents = False
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
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.

 

4 Responses to “Excel : Using Excel Macro & VB Change Case”

  1. Ralph Appleby Says:

    When using this code I get a compile error “Can’t Find Project or Library”. Any suggestions? If it is a missing reference I need to know which one and how to restore it. Thanks in advance.

  2. asriey Says:

    did you follow exactly the instruction?

  3. Azam Says:

    guna formula jer tak buleh ke ?

  4. 1kI’ll thingk about it.5r I compleatly disagree with last post . urs
    ламинат купить 0l


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s