Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Original post thread. Started another thread here..

    http://www.mrexcel.com/board/viewtop...orum=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/viewtop...ic=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

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:


    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


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

    If you want to have "H2O" 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 ]

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark!! It works!!

    One question, where do I get the character pipe?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  10. #10
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It screws formatting like this:

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •