none
Alter text in a text box to obtain a date superscript using VBA RRS feed

  • Question

  • I have a title and date wrapped in a textbox so it is highlighted on the page.  If I enter "10th" the "th" does not automatically revert to a superscript as it does when typing in Word. 

    code snippet:

    boxwidth=265   'numeric value will eventually be a function of len(datestring)

    datestring = "Autumn Show September 10th 2010"      
    Set myTB1 = ActiveDocument.Shapes.AddTextbox _
    (msoTextOrientationHorizontal, 72, 100, boxwidth, 30)
    myTB1.TextFrame.TextRange = datestring
    myTB1.TextFrame.TextRange.Font.Name = "algerian"
    myTB1.TextFrame.TextRange.Font.Size = 14

    I can subsequently alter any text before this text box to change 2 characters to superscript using for example:
     ActiveDocument.Range(Start:=60, End:=62).Font.Superscript = True

    but it fails when I enter start/end nos for text in the texttbox (error "out of range")

    Anybody have an alternative method to get a superscript to appear in a textbox please?

    Sunday, November 30, 2014 2:10 AM

Answers

  • Try:

    Sub Demo()
    Dim datestring As String, myTB1 As Shape
    datestring = "Autumn Show September 10th 2010"
    Set myTB1 = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 72, 100, 720, 30)
    With myTB1.TextFrame
      .WordWrap = False
      With .TextRange
        .Text = datestring
        With .Font
          .Size = 14
          .Name = "algerian"
        End With
        With .Find
          .ClearFormatting
          .Replacement.ClearFormatting
          .Format = False
          .Forward = True
          .Wrap = wdFindStop
          .Text = "<[0-9]@[nrst][tdh]>"
          .MatchWildcards = True
          .Replacement.Text = ""
          .Execute
        End With
        Do While .Find.Found
          .Start = .End - 2
          .Font.Superscript = True
          .Collapse wdCollapseEnd
          .Find.Execute
        Loop
      End With
    End With
    End Sub

    Note: Using "boxwidth=265" or a "numeric value will eventually be a function of len(datestring)" is futile with a variable-width font and, as the above code shows, unnecessary for getting the textbox to resize to fit the text.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Derek59nsw Sunday, November 30, 2014 10:53 PM
    Sunday, November 30, 2014 4:09 AM

All replies

  • Try:

    Sub Demo()
    Dim datestring As String, myTB1 As Shape
    datestring = "Autumn Show September 10th 2010"
    Set myTB1 = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 72, 100, 720, 30)
    With myTB1.TextFrame
      .WordWrap = False
      With .TextRange
        .Text = datestring
        With .Font
          .Size = 14
          .Name = "algerian"
        End With
        With .Find
          .ClearFormatting
          .Replacement.ClearFormatting
          .Format = False
          .Forward = True
          .Wrap = wdFindStop
          .Text = "<[0-9]@[nrst][tdh]>"
          .MatchWildcards = True
          .Replacement.Text = ""
          .Execute
        End With
        Do While .Find.Found
          .Start = .End - 2
          .Font.Superscript = True
          .Collapse wdCollapseEnd
          .Find.Execute
        Loop
      End With
    End With
    End Sub

    Note: Using "boxwidth=265" or a "numeric value will eventually be a function of len(datestring)" is futile with a variable-width font and, as the above code shows, unnecessary for getting the textbox to resize to fit the text.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Derek59nsw Sunday, November 30, 2014 10:53 PM
    Sunday, November 30, 2014 4:09 AM
  • Thank you for your solution which I look forward to implementing.

    I posted this question in another forum to which you have also posted this answer

    I didn't realise how closely linked these forums were and apologies for the unnecessary duplication of effort. Previously I had been working on Joomla where the forums seem disconnected so I commonly posted a question in different places.

    I appreciate your time and expertise. VBA is very new to me and its proving an interesting and challenging exercise.

    Many thanks

    Derek Sharp
    Sunday, November 30, 2014 10:53 PM
  • Thanks once again for your answer.  I am picking my way through the code and its becoming understandable.

    I have difficulty in completely understanding this line:

      .Text = "<[0-9]@[nrst][tdh]>"

    It is obviously looking for digits 0-9 together with "st" or "th". but I don't understand the syntax and I cannot find anything online that helps.

    Could you please explain the syntax used here, or better still point me to a reference.

    Many thanks

    Derek

    Monday, December 1, 2014 12:20 AM
  • The "<[0-9]@[nrst][tdh]>" is a Word wildcard Find expression.
    The < says the following expression must be the start of a word.
    The [0-9]@ says to find a string of digits.
    The [nrst][tdh] says to find any of n,r,s,t followed by any of t,d,h, the idea being to minimise the risk of false matches.
    The > says the preceding expression must be the end of a word.

    For more information, see: https://support.office.com/en-us/article/Find-and-replace-text-by-using-regular-expressions-Advanced-eeaa03b0-e9f3-4921-b1e8-85b0ad1c427f?ui=en-US&rs=en-US&ad=US


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Monday, December 1, 2014 12:29 AM
    Monday, December 1, 2014 12:27 AM
  • That's most helpful. Thank you, Derek
    Monday, December 1, 2014 7:37 AM