Creating Superscript and Subscript Buttons

by Allen Wyatt
(last updated December 20, 2016)

6

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:

  1. Press Ctrl+F11 to display the VBA Editor.
  2. In the VBA Editor, choose User Form from the Insert menu. A new, blank user form displays, along with the form toolbox.
  3. Using the controls in the form toolbox, add three CommandButton controls across the top of the form.
  4. Change the properties for the left CommandButton control so its Name is btnSuper and its Caption is Superscript.
  5. Change the properties for the center CommandButton control so its Name is btnSub and its Caption is Subscript.
  6. Change the properties for the right CommandButton control so its Name is btnNormal and its Caption is Normal.
  7. Just under the three buttons, add a TextBox control. You don't need to change any properties for this control.
  8. Just under the TextBox control, add a fourth CommandButton control.
  9. Change the properties for this last CommandButton control so its Name is btnExit and its Caption is Exit.

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.

Author Bio

Allen Wyatt

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. ...

MORE FROM ALLEN

Specifying a Font in WordArt

Do you use WordArt to place graphics in your Excel workbooks? If so, you may want to modify the font used by the program to ...

Discover More

Rounding to Two Significant Digits

Excel provides a variety of functions you can use to round values in any number of ways. It does not, however, provide a way ...

Discover More

Printing a Draft Watermark

Adding a watermark to the background of a printout can be challenging. This tip explains the different ways you can implement ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Defining and Using Custom Colors

Want to spice up your worksheets with your own custom colors? Here's how to define them easily.

Discover More

Easily Deploying Customizations

When you create a whole set of customizations for Excel, you may want to share them with others in your office or workgroup. ...

Discover More

Default Worksheet when Opening

When opening a workbook, you may want to make sure that a particular worksheet is always displayed first. The only way to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 8?

2017-05-13 04:26:29

Radu

what about a command in the text box to show the actual text in the box a little bit bigger ?


2015-09-28 21:04:13

Layla

What I can't figure out is how to make the text in the textbox formatted like the text in the cell (i.e. the sub/superscript changes you make show up in both places - not just in the cell).

Any ideas? Is this possible?


2015-09-28 20:54:30

Layla

One edit to the macro code... put the userform1.show last, like this:

UserForm1.TextBox1.Enabled = True
Dim strText As String
strText = ActiveCell.Value
UserForm1.TextBox1.Text = strText
UserForm1.Show

Sorry about that... I am new to VBA!


2015-09-28 20:29:10

Layla

Thank you for this! I am a science major and this is incredibly helpful. However, I had to make two edits to make it functional.

In the form code:

line 1: Private Sub UserForm1_Activate()

In the macro code:

UserForm1.Show
Dim strText As String
strText = ActiveCell.Value
UserForm1.TextBox1.Text = strText
UserForm1.TextBox1.Enabled = True


2015-02-27 20:14:05

Katie Collins

Step 1: if Ctrl+F11 doesn't work try Alt+F11


2013-04-24 09:28:19

PanuWorld

Missing superscript/subscript toolbar buttons were also a problem for me until I found a way to create them using a COM add-in DLL.

You do not need any VBA code or custom forms because the toolbar/ribbon buttons generated by the add-in work also during in-cell editing.

I have been so frustrated about this missing feature of Excel that I published my add-in free to help all Excel users. I hope that saves lot of time globally.

If you are allowed to install software on your PC, feel free to try the add-in:
http://www.panuworld.net/utils/excel
The add-in works at least on Excel 2003...2010, excluding 64-bit Excel (not widely used).


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.