My Profile
Active Members
TodayLast 7 Days
more...
Awards & Gifts
Online Exams
Fresher Jobs
Our fresher job section is exclusively for fresh graduates! Find jobs for freshers in major Indian
cities including Bangalore, Chennai, Hyderabad, Pune or Kochi
Resources
Find educational articles, blogs, discussion threads and other resources.
Colleges
Find details about any college in India or search for courses.
|
change case macros in xls
Posted Date: 16 May 2008 Resource Type: Articles/Knowledge Sharing Category: Computer & Technology
|
Posted By: sri phani kumari Member Level: Gold
Rating:  Points: 1
|
|
|
Change Case Macros in XLS
As in Word, the change case feature is not available (lower, full caps, title case) in the Excel.
But possible via Macros
Pre-requisite: Security > Medium or Low setting
Steps
Open the XLS
Tools > Macro > Visual Basic Editor (VBE) or Alt + F11
Visual Basic Editor opens
Insert User Form
UserForm1 > In Properties, change the Name and Caption to CaseChangerDialog and Case Changer respectively
Use the Controls in Toolbox and paint the screen for the user interface (UI) with the following elements:
1. Three radio options (Lower Case, Upper Case, Proper Case)
? OptionLower
Set Value as True
? OptionUpper
? OptionProper
2. Two Command Buttons (OK, Cancel) and set Default as True
? OKButton
? CancelButton
Insert Modules
Module 1 > Paste the below code to call the UI
Sub ChangeCase()
CaseChangerDialog.Show
End Sub
Script for Cancel
Double-click the CancelButton and paste the below before End Sub Statement to close the UI
Unload CaseChangerDialog
Script for OK
Double-click the OKButton and paste the below before the End Sub statement to call the respective VB script for the selected case change.
Private Sub OKButton_Click()
Application.ScreenUpdating = False
'If TypeName(Selection) <> “Range” Then Exit Sub
If OptionUpper Then
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = StrConv(cell.Value, vbUpperCase)
End If
Next cell
End If
If OptionLower Then
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = StrConv(cell.Value, vbLowerCase)
End If
Next cell
End If
If OptionProper Then
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = StrConv(cell.Value, vbProperCase)
End If
Next cell
End If
Unload CaseChangerDialog
End Sub
Options
To have this macro onto a toolbar as a button and assign macro to the button, do the following:
? Tools > Customize > Select Macros in Categories > Drag Custom Button onto the toolbar > Right click the button and rename accordingly.
? Click Assign Macro > select ChangeCase and click OK
To assign a shortcut key to the macro, do the following:
? Tools > Macro > Select the macro and click Options > hit the required key e.g. c (CTRL+c)
Transfer
? To copy the macro to another file Open VBE copy the User Form and Modules and paste in the required file.
Or
? Export the User Form and Module from the existing file and Import these into the required file in VBE.
|
Responses
|
| Author: rambabu 20 May 2008 | Member Level: Silver Points : 2 | it is very helpful to the people who are using ms xl
|
|
Watch TV Channels
Watch Asianet TV onlineKairali TV in InternetSurya TV onlineAmritha TV Channel
|