Results 1 to 5 of 5

Thread: Smart Tags

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to programmatically control the options in the Smart Tags.

    Example: Converting a text value to a number. I can click the smart tage and select the option to convert the text value to a number, but is there a way to have Excel do this using VBA code?


    Thanks
    Richard

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Richard,

    Since this smart tag shows up when you paste an item, presumably in code, use the PasteSpecial method instead.

    Now if you're talking about having code execute when you manually paste something in to the sheet this could be done via a Worksheet_Change event but I think you would find it problematic trying to figure out when it should and when it shouldn't convert the value.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have discovered that if I "Activitate" the cell, then press Enter or Tab, that converts the data in the cell tp a number.


    Thanks
    Richard

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    For a VBA solution that I have used many times try the following:

    Sub Converter()
    Dim Cell As Range
    For Each Cell In Selection
    Cell.Formula = Cell.Formula
    Next
    End Sub

    Befor execution of the Macro select the range you want to convert. The selection can contain Text, Dates, Formulas, ect. the Macro should only change numbers as text to regular numbers. One warning, do not include any cells with array formulas, the Macro will covert the array formulas to standard formulas.

    Regards,

    Tom Duthie

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You shouldn't really need a loop:

    Code:
    With Selection
       .Formula = .Formula
    End With
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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