Excel 2010: VBA problem toggling subscripts

Topic

New Reply

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.

36763-20140411-Sheet-capture

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

Reply | Quote

Replies

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote