Superscript Subscript - part of a cell - using VBA - Part

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
Original post thread. Started another thread here..

http://www.mrexcel.com/board/viewtopic.php?mode=viewtopic&topic=5752&forum=2&start=0

Is there a way to superscript or subscript only part of a cell's text? The forum below provides VBA to superscript an entire cell.

How about just part of a cell?

e.g. Say I type "The volume of the cylinder is 42 m3", the press the arrow key once (to have the cursor before 3, hold down shift, go forward to select 3. NOW, I want to run a macro or script to superscript the selected part of the text, in this case the 3.

http://www.mrexcel.com/board/viewtopic.php?topic=856&forum=2

How about using an inputbox to choose part of the cell?


Or using an inputbox to ask you to choose the cell,
THEN counts number of characters,
THEN asks you where to start and stop the supercript or subscript,
THEN asked if you want to superscript or subscript
THEN asks if you want to superscript/subscript (depending on what was choosen) "selectect text"(have excel store the text part selected for formatting),
THEN carries it out?

Is this possible? I don’t know enough VBA to know.

Here's some code to get people started..

Range("A1").Select
ActiveCell.FormulaR1C1 = "m3"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

'Use inputbox?
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = True
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("D7").Select
End Sub


I know it would probably be better to use CTRL-1 and just click the apporpriate box, but I want to know if this would work. And hopefully learn some VBA along the way.

Probably won't since I don't know much about writing VBA, but I had to ask!

EDIT: Created an article and sample file that explains Mark O'Brien's VBA solution to this - Subscript & Superscript cell formating on the fly
 
Sorry, I didn't realize this was such an old thread. I will make a new thread.

Seiya, I get a run-time error '438'. Object doesn't support this property or method.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
No you don't need to create a new thread, because this is exactly the same as this thread's concept, but different method.

I have edited the code so, try again ?
 
Upvote 0
typos
Rich (BB code):
Sub test()
Dim r As Range, temp As String, sInd As String, ssInd As String
Range("a1:b1").Value = Array("C|2H|4","P^S")
With CreateObject("VBScript.RegExp")
    For Each r In Range("a1:b1")
        .Pattern = "\|"
        temp = r.Text
        Do While .test(temp)
            x = .execute(temp)(0).firstindex + 1
            sInd = sInd & "," & x
            temp = Application.Replace(temp, x,1, "")
        Loop
        .Pattern = "\^"
        Do While .test(temp) 
            x = .execute(temp)(0).firstindex + 1
            ssInd = ssInd & "," & x
            temp = Application.Replace(temp, x,1, "")
        Loop
        r.Value = temp
        If Len(sInd) Then
            For Each e In Split(Mid(sInd, 2))
                r.Characters(e, 1).Font.Subscript = True
            Next
        End If
        If Len(ssInd) Then
            For Each e In Split(Mid(ssInd, 2))
                r.Characters(e, 1).Font.SuperSubscript = True
            Next
        End If
    Next
End With
End Sub
 
Upvote 0
change to
Code:
Sub test()
Dim r As Range, temp As String, sInd As String, ssInd As String, m As Object
Range("a1:b1").Value = Array("C|2H|4","P^S")
With CreateObject("VBScript.RegExp")
    For Each r In Range("a1:b1")
        .Pattern = "[\^\|]"
        temp = r.Text
        Do While .test(temp)
            Set m = .execute(temp)(0)
            Select Case m.Value
                Case "|"
                    sInd = sInd & "," & m.firstindex + 1
                Case "^"
                    ssInd = ssInd & "," & m.firstindex + 1
            End Select
            temp =.Replace(temp,"")
        Loop
        r.Value = temp
        If Len(sInd) Then
            For Each e In Split(Mid(sInd, 2))
                r.Characters(e, 1).Font.Subscript = True
            Next
        End If
        If Len(ssInd) Then
            For Each e In Split(Mid(ssInd, 2))
                r.Characters(e, 1).Font.Superscript = True
            Next
        End If
    Next
End With
End Sub
<!-- / message --><!-- / message -->
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top