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

[Cosmos75]

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

 

[Mark O'Brien]

OK, this is sort of a mad posting, but here is something that I'd like to suggest. Put this code on your worksheet object, you can probably put it on "ThisWorkbook" at some point, but I just used the sheet object for testing:<pre>
Private Const CHAR_SUP As String = "^"
Private Const CHAR_SUB As String = "|"

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) Then Exit Sub

'Look for SUPERSCRIPT character
If InStr(1, Target.Value, CHAR_SUP) > 1 Then
SuperScript Target, InStr(1, Target.Value, CHAR_SUP)
End If

'Look for SUBSCRIPT character
If InStr(1, Target.Value, CHAR_SUB) > 1 Then
SubScript Target, InStr(1, Target.Value, CHAR_SUB)
End If

End Sub
Private Function LeftString(ByVal sText As String, ByVal sSeparator As String) As String
LeftString = Left(sText, InStr(1, sText, sSeparator) - 1)
End Function
Private Function RightString(ByVal sText As String, ByVal sSeparator As String) As String
RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))
End Function
Private Sub SuperScript(ByVal Target As Range, ByVal iPosition As Integer)
Target.Value = LeftString(Target.Value, CHAR_SUP) & RightString(Target.Value, CHAR_SUP)
Target.Characters(Start:=iPosition, Length:=1).Font.SuperScript = True
End Sub
Private Sub SubScript(ByVal Target As Range, ByVal iPosition As Integer)
Target.Value = LeftString(Target.Value, CHAR_SUB) & RightString(Target.Value, CHAR_SUB)
Target.Characters(Start:=iPosition, Length:=1).Font.SubScript = True
End Sub</pre>

How to use to. Suppose you want, as in your example "m³" to show: simply type in "m^3".

If you want to have "H₂O" then type in "H|2O".

I have used carat "^" and pipe "|" to denote superscript and subscript respectively.

The limitation to this is that it will only superscript or subscript the next character after ^ or |.

Test text:

The volume is m^3
H|2O
H|2^+SO|4^2^-
432 m^3
=2^2

Let me know what you think of this one.

I saw the j-walk example posted by Jay on the other thread, that looks interesting.
This message was edited by Mark O'Brien on 2002-04-22 11:12

 

[Mark O'Brien]

Actually, I just tried editting some of the text I put into my cells, the code screws up the formatting. This is easily fixed but, again, let me kow if you like this idea and I can fix this tonight.

 

[Cosmos75]

Mark!! It works!!

One question, where do I get the character pipe?

 

[Cosmos75]

Haven't tried any cells with formatting but I love it!! What kind of formating does it screw up??
This message was edited by Cosmos75 on 2002-04-22 11:20

 

[Cosmos75]

Found the pipe key

Tried this

"H|2O is Water and X^2 is X multiplied by X"

Only did H|2O correctly and didn't format X^2.

Edit:
This works - "X^2 and H|2o"
This doesn't - "H|2O and X^2"

I guess if a pipe (|) comes before an ^ it stops looking?

Just tried this, See what this returns.

H|4^5|4333|4333|4333^5

It took the |4 after the ^4 and superscripted it instead of subscripting it.
This message was edited by Cosmos75 on 2002-04-22 11:29

 

[Mark O'Brien]

No, those don't work because of the same limitation when going back to edit the cells, in the case of:
"H|2O and X^2"

What happens is this:

1. It finds "^", then changes it to superscript.
2. Then it find | and changes to subscript without preserving the formatting from stage 1.

I can fix this, but not now since I'm in work. Are you suitably impressed enough for me to continue on this line later tonight?

 

[Cosmos75]

Tried the j-walk add-in. It works in EXCEL 2000.

Do you think it is doing somethings like what I had suggested? With multiple points where you can sub- or super-script and with a preview?

 

[Cosmos75]

Yes, I am VERY impressed with this!!! I appreciate your taking on my "mad posting"!

Do you know if my suggestion for a way for it to work is possible? Do you think that's how the j-walk add-in works?

 

[Mark O'Brien]

It screws formatting like this:

Type in "H|2O" then go back and make the "O" superscript. The "2" will now be normal case.