Word can change the case of selected text, but that capability is notably lacking in Excel....

Transcribed Image Text:

Word can change the case of selected text, but that capability is notably lacking in Excel. Write an Excel subroutine that enables an Excel user to change the case of text in any selected range. In the instruction, inform the user to first select the cell(s) that contain(s) the text to be manipulated before clicking on the button to run the application. User is allowed to select a range of multiple rows/columns. Use Error handling to notify that the user did not select any text. An example of running the subroutine is shown in the following figure. Your assignment doesn't have to look like it. Hint: You can use Type in a letter Llowercase [Ulppercase Slentense (Title ClapsSmall O VBA functions: "LCase", "UCase" O Excel function: "Proper". OK Cancel Note: 1- the "Proper" function is used to capitalize the first letter in a text string and any other letters in text that follow any character other than a letter. It converts all other letters to lowercase letters. Ref: https://support.microsoft.com/en-us/office/proper-function-52a5a283-e8b2-49be-8506- 62887b889f94 2- To access a range selected in a worksheet, you can use the application selection property Ref: https://docs.microsoft.com/en- us/office/vba/api/excel.application.selection?flurl=%3Fappld%3DDev11/DEF1%26l%3Den- US%26k%3Dk(vbaxl10.chm183107);k(TargetFrameworkMoniker- Office.Version%3Dv16)%26rd%3Dtrue The user must select one of the 5 following cases: Sentence: Capitalize only the first letter in a sentence, the rest converted to lower case letters. Lowercase: All letters in the sentence are converted to lower case letters. Uppercase: All letters in the sentence are converted to uppercase case letters. Titles: Capitalize the first letter of each work in sentence, the rest is converted to lower case letter. Caps small: Capitalize all letters in a sentence, keeping the first letter of each word at it is original font size, reducing the size of the rest of the letter to 0.85 smaller. Example: hello class => HELLO CLASS (after applying Caps small). Word can change the case of selected text, but that capability is notably lacking in Excel. Write an Excel subroutine that enables an Excel user to change the case of text in any selected range. In the instruction, inform the user to first select the cell(s) that contain(s) the text to be manipulated before clicking on the button to run the application. User is allowed to select a range of multiple rows/columns. Use Error handling to notify that the user did not select any text. An example of running the subroutine is shown in the following figure. Your assignment doesn't have to look like it. Hint: You can use Type in a letter Llowercase [Ulppercase Slentense (Title ClapsSmall O VBA functions: "LCase", "UCase" O Excel function: "Proper". OK Cancel Note: 1- the "Proper" function is used to capitalize the first letter in a text string and any other letters in text that follow any character other than a letter. It converts all other letters to lowercase letters. Ref: https://support.microsoft.com/en-us/office/proper-function-52a5a283-e8b2-49be-8506- 62887b889f94 2- To access a range selected in a worksheet, you can use the application selection property Ref: https://docs.microsoft.com/en- us/office/vba/api/excel.application.selection?flurl=%3Fappld%3DDev11/DEF1%26l%3Den- US%26k%3Dk(vbaxl10.chm183107);k(TargetFrameworkMoniker- Office.Version%3Dv16)%26rd%3Dtrue The user must select one of the 5 following cases: Sentence: Capitalize only the first letter in a sentence, the rest converted to lower case letters. Lowercase: All letters in the sentence are converted to lower case letters. Uppercase: All letters in the sentence are converted to uppercase case letters. Titles: Capitalize the first letter of each work in sentence, the rest is converted to lower case letter. Caps small: Capitalize all letters in a sentence, keeping the first letter of each word at it is original font size, reducing the size of the rest of the letter to 0.85 smaller. Example: hello class => HELLO CLASS (after applying Caps small).

Answer rating: 100% (QA)

Sub ChangeCase Dim myRange As Range Dim myCell As Range Check if any cells are selected If SelectionCellsCount 1 And IsEmptySelection Then MsgBox Please select a range with text to change the case of vbExclamation Exit Sub End If Set the range to the selection Set myRange Selection ... View the full answer