MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is there a way to simplify the subscript/superscript functions?


Posted by Mike Florio on August 17, 2001 3:37 PM

I was wondering if there is a way to simplify the subscript/superscript functions in Excel. Do you know of any way to add a button in the toolbar like that of Word? Thanks.


Posted by Ivan F Moala on August 18, 2001 2:07 AM


Hi Mike
Couldn't find any buttons ??!! so created this routine to place a Super/Sub script button similar
to Words in the Format commandbar + one other
button to return Text to Normal.

How to use:
> Just select the range or cell with Text
> and click which ever button you need to
> format the selection as Super / Sub or Normal

Probably best if you save as an Addin ie.
SaveAs | select .xla .....up to you.

Add this routine to the Thisworkbook object
Avail via
1) Alt F11
2) Ctrl R
3) Double click on Thisworkbook

---Start----------------------------------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Delete_SupSub_Btns
End Sub

Private Sub Workbook_Open()
Create_SupSub_Btns
End Sub

---Fin-----------------------------------------

Then in a Std Module place this code;

Sub SuperScpt()
On Error Resume Next
With Selection.Font
.Superscript = True
End With
If Err Then MsgBox "Can't do this command here!"
End Sub

Sub SubScpt()
On Error Resume Next
With Selection.Font
.Subscript = True
End With
If Err Then MsgBox "Can't do this command here!"
End Sub

Sub Normal()
On Error Resume Next
With Selection.Font
.Subscript = False
.Superscript = False
End With
If Err Then MsgBox "Can't do this command here!"

End Sub

Sub Create_SupSub_Btns()
Dim Contrl_Super ' As CommandBarControl
Dim Contrl_Sub 'As CommandBarControl
Dim Contrl_Norm

'1st delete JIC
Delete_SupSub_Btns

Set Contrl_Super = Application.CommandBars("Formatting").Controls
Set Contrl_Super = Contrl_Super.Add(Type:=msoControlButton, ID:=2950, Before:=10)
With Contrl_Super
.FaceId = 57
.Style = msoButtonIconAndCaption
.Caption = "Superscript"
.OnAction = "SuperScpt"

End With

Set Contrl_Sub = Application.CommandBars("Formatting").Controls
Set Contrl_Sub = Contrl_Sub.Add(Type:=msoControlButton, ID:=2950, Before:=10)
With Contrl_Sub
.FaceId = 58
.Style = msoButtonIconAndCaption
.Caption = "Subscript"
.OnAction = "SubScpt"
End With

Set Contrl_Norm = Application.CommandBars("Formatting").Controls
Set Contrl_Norm = Contrl_Norm.Add(Type:=msoControlButton, ID:=2950, Before:=10)
With Contrl_Norm
.FaceId = 80
.Style = msoButtonIconAndCaption
.Caption = "Normal"
.OnAction = "Normal"
End With


Application.CommandBars("Formatting").Visible = True
End Sub

Sub Delete_SupSub_Btns()

On Error Resume Next
Application.CommandBars("Formatting").Controls("Superscript").Delete
Application.CommandBars("Formatting").Controls("Subscript").Delete
Application.CommandBars("Formatting").Controls("Normal").Delete

End Sub


HTH


Ivan

Posted by Barry on August 18, 2001 11:34 PM

HTH Ivan
Is it possible to change a single letter such as 2 in H2O to subscript rather than the entire cell?