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]

I haven't downloaded the j-walk thing, but I have a fair idea of how it works. It should only take about 15 minutes to perfect my version though, but I wont get those 15 minutes in work today. I promise to do it tonight.

 

[Mark O'Brien]

Right, I've got a solution here. I said 15 minutes, it longer, but the solution ended up being so simple I'm almost embarassed to say it took 30 minutes.<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 Sub SuperScript(ByVal Target As Range, ByVal iPosition As Integer)
Target.Characters(Start:=iPosition + 1, Length:=1).Font.SuperScript = True
Target.Characters(Start:=iPosition, Length:=1).Delete
End Sub
Private Sub SubScript(ByVal Target As Range, ByVal iPosition As Integer)
Target.Characters(Start:=iPosition + 1, Length:=1).Font.SubScript = True
Target.Characters(Start:=iPosition, Length:=1).Delete
End Sub</pre>

EDIT:: I removed the LeftString and RightString functions because we don't need them.
This message was edited by Mark O'Brien on 2002-04-24 08:26

 

[Jay Petrulis]

WOW! Excellent job. This is terrific! Definitely goes into my keeper file.

 

[Mark O'Brien]

Thanks again Jay.

Question for you though. I don't have the energy to think this one out, but it's going to be nagging me, and you might know the answer.

This doesn't use a loop, but it appears to go though each character in the text. Why does it fix strings like:

(H⁺)₂SO^2-₄

?

 

[Cosmos75]

SUPER-GURU MARK O' BRIEN!!
All bow to thee!!

Super! Fantastic! Incredible!

I love it! I have no idea how you thought this up!

I do have one question, was my original idea even possible?
This message was edited by Cosmos75 on 2002-04-23 13:32

 

[Mark O'Brien]

How the hell would I know? Do you think I read the postings of paeons? (Just a joke)

I think your idea would work, but it may be a hassle. I also think that's it's similar, in theory to the j-walk add-in that Jay suggested. (I think the add-in would maybe be a slightly better interface, I've still not downloaded that yet though.)

EDIT:: You can get rid of these bits of code, as we don't use them:<pre>
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</pre>
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-23 14:13

 

[Cosmos75]

pae·on Pronunciation Key (pn, -n)
n.
In quantitative verse, a foot of one long syllable and three short syllables occurring in any order.

--------------------------------------------------------------------------------
[Latin paen, from Greek pain, from pain, pain, paean. See paean.]

Mark O' Brien,

Thanks, at least I know that my idea is possible (even I don't have enough VBA knowledge to write such code).

The add-in works good, although you have to type in your text and then format it character by character. I prefer your code which allows you to sub- or super-script text on the fly!

You should submit this to some excel tips book or maybe to Mr.Excel for an award or recognition.
This message was edited by Cosmos75 on 2002-04-24 14:08

 

[dragonslayer22]

I came across this topic while looking for something like the solution here. Unfortunately, I don't know what to do with Mark O'Brien's code. I know how to get to the Visual Basic window, but I don't where to go from here.

Your help would be appreciated.

 

[Legacy 68668]

dragonslayer22

Can you show me same sample data and the result for it?

 

[dragonslayer22]

I'm not sure what you mean. I don't have any sample data or results because I don't know how to use the program. How I can use the code that was presented here? How do I input it into Excel so that I can use it?