Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Angry Excel 2010: VBA problem toggling subscripts

    I have strange behaviour with a Macro to toggle a Subscript in an Excel 2010 Cell.

    My Macro will toggle the values show in Row 1 to have the final character of the string in the selected cell converted to a Subscript as shown in Row 3.

    20140411 Sheet capture.JPG

    However, trying to reverse the process works sometimes, but rarely.

    Here is my VBA code for the Macro:

    Code:
    Sub Subscript()
    
    Dim sFontName As String
    Dim sFontStyle As String
    Dim sFontSize As Long
    Dim sStringLength As Long
    Dim sSubscript As Boolean
    
    Dim Msg, Button, Title, Response As String
    
    Button = vbExclamation
    
    ActiveCell.Select
    sStringLength = Len(ActiveCell)
    
    sFontName = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Name
    sFontStyle = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.FontStyle
    sFontSize = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Size
    sSubscript = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript
            Title = "Test for Subscript 0 ..."
            Msg = "Font Name:      " & sFontName & vbCrLf & _
                    "Font Style:     " & sFontStyle & vbCrLf & _
                    "Font Size:      " & sFontSize & vbCrLf & _
                    "String Length:  " & sStringLength & vbCrLf & _
                    "00 Subscript:      " & sSubscript
            Response = MsgBox(Msg, Button, Title)
    
    If sSubscript Then
        sSubscript = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript
            Title = "Test for Subscript Initially True 1.0..."
            Msg = "Before Subscript Toggle:      " & sSubscript
            Response = MsgBox(Msg, Button, Title)
        ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript = False
        sSubscript = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript
            Title = "Test for Subscript Initially True 1.1..."
            Msg = "After Subscript Toggle:      " & sSubscript
            Response = MsgBox(Msg, Button, Title)
    Else
        sSubscript = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript
            Title = "Test for Subscript Initially False 2.0 ..."
            Msg = "Before Subscript Toggle:      " & sSubscript
            Response = MsgBox(Msg, Button, Title)
        ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript = True
        sSubscript = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript
            Title = "Test for Subscript Initially False 2.1 ..."
            Msg = "After Subscript Toggle:      " & sSubscript
            Response = MsgBox(Msg, Button, Title)
    End If
    
    End Sub
    The code has numerous messages in it to display the status of the action, hence it looks long and complicated - it's not really.

    Any bright ideas on why this code is not working correctly?

    Thanks in anticipation for all assistance.

    Trevor

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Trevor,

    This seems to work consistently.
    Code:
    Option Explicit
    
    Sub Subscript()
    
       Dim sStringLength As Long
       Dim sSubscript    As Boolean
    
       sStringLength = Len(ActiveCell)
       sSubscript = ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript
    
       If sSubscript Then
         ActiveCell.Value = ActiveCell.Value
       Else
         ActiveCell.Characters(Start:=sStringLength, Length:=1).Font.Subscript = True
       End If
    
    End Sub   'SubScript()
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2014-04-11)

  5. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,894
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Since your thread title mentions toggling:
    Code:
    Sub ToggleSubscript()
    With ActiveCell
      On Error Resume Next
      .Characters(.Characters.Count).Font.Subscript = Not _
      .Characters(.Characters.Count).Font.Subscript
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. The Following User Says Thank You to macropod For This Useful Post:

    BygAuldByrd (2014-04-11)

  7. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi RetiredGeek,

    Thanks, yes that does exactly what I desire - it toggles the Subscript on and off depending on how the last character in the string is set.

    Cheers

    Trevor

Posting Permissions

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