Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    116
    Thanks
    26
    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,495
    Thanks
    212
    Thanked 852 Times in 784 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


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

    BygAuldByrd (2014-04-11)

  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,979
    Thanks
    0
    Thanked 208 Times in 189 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]

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

    BygAuldByrd (2014-04-11)

  6. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    116
    Thanks
    26
    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
  •