Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
When you are editing a cell in Excel, you have access to several of the formatting toolbar buttons that can make formatting the cell a bit easier. For instance, you can use the Bold or Italics tools to change these two attributes for any text selected in a cell. At some point you may want to create other toolbar buttons to handle other formatting, such as applying superscript or subscript.
Excel, however, doesn't allow you to create your own formatting tools and have them accessible while editing a cell. This is because Excel "deactivates" all user-defined macros while you are doing the editing. You are left with formatting the cell contents via Format | Cells, or by pressing Ctrl+1 to display the Format Cells dialog box directly.
There is a sneaky way you can use to create your own formatting tools, however. This involves the use of user forms and VBA to create your own formatting "dialog box." (I know—this is not really a dialog box, but a form.) Creating your own user form is not terribly difficult, but it isn't for the faint-of-heart when it comes to macros. Follow these steps to create your own form:
That's it; you've created your user form, and you are ready to associate macro code with the controls you just placed. With the user form selected, press F7 to display the Code window for the form. The window may contain a line or two of automatically generated code. Replace this with the following code:
Private Sub UserForm_Activate() TextBox1.Text = ActiveCell.Formula End Sub
Private Sub btnSuper_Click() Dim intStart As Integer Dim intLength As Integer intLength = TextBox1.SelLength If intLength > 0 Then intStart = TextBox1.SelStart + 1 ActiveCell.Characters(intStart, intLength).Font.Superscript = True End If End Sub
Private Sub btnSub_Click() Dim intStart As Integer Dim intLength As Integer intLength = TextBox1.SelLength If intLength > 0 Then intStart = TextBox1.SelStart + 1 ActiveCell.Characters(intStart, intLength).Font.Subscript = True End If End Sub Private Sub btnExit_Click() Unload UserForm1 End Sub
Private Sub btnNormal_Click() Dim intStart As Integer Dim intLength As Integer intLength = TextBox1.SelLength If intLength > 0 Then intStart = TextBox1.SelStart + 1 ActiveCell.Characters(intStart, intLength).Font.Superscript = False ActiveCell.Characters(intStart, intLength).Font.Subscript = False End If End Sub
Close the Code window for the user form, and close the form window itself. You now need to create a very short macro that will display the actual user form. This macro is created the same as any other Excel macro, and should look like this:
Sub DoForm() UserForm1.Show End Sub
You can now close the VBA Editor window. In order to use the macro, select the cell you want to edit, and then run the DoForm macro. Excel displays your user form, which contains the text in the selected cell. You can then select text within the user form and use the buttons (Superscript, Subscript, and Normal) to change the formatting of the actual cell contents. The macro affects the contents of the cell, not the contents of the user form. Thus, it is helpful to be able to see both the selected cell and the user form on the screen at the same time.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1945) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!