Consulting

Results 1 to 15 of 15

Thread: Solved: Tab character

  1. #1
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Solved: Tab character

    Hi all,
    Is there a character I can enter in Excel, which converts to a Tab when the cell contents are pasted into Word. I've tried Alt + 9 with no success.
    MD

  2. #2
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hey MD,

    You could use char(9)

    I just put ="a"&CHAR(9)&"b" into a cell, copied it, then in word did Edit / Paste Special / Unformatted Text
    Pasted as "a b"

    Matt

  3. #3
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    thats odd, when I pasted it in (and when I see it in word) there is an actual tab there, although in word its really only one character long (same as normal tabs)

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    The requirement is for use in this ongoing project, where I would like imported text to fit defined tab stops.
    (save the xls file in C:\Amerge)

  5. #5
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Cool program by the way

    Anyways, as an example, I changed B6 to be ="35 South London Road"&CHAR(9)&"Apt 2" and when I clicked on Add1 it shows the tab in the textbox. when i double click it, the space is there between the Road and Apt, and Apt starts at the next tab stop

    I may be missing something though..

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    You're right Matt, I must have been careless with my quotes, the = sign or something.
    Thanks,
    MD

  7. #7
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Not a problem, you may want to have a worksheet_change event in there or something saying something like
    [vba]if not isnumeric(target.text) and if left(target.formula)<>"=" then
    target.formula="=""" & target.text & """"
    end if[/vba]

    that way if you forget to enter text in that manner (which would be annoying to do every time), it does it for you

    Just a thought

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    BTW, the code may have written a couple of values to your registry

    Private Sub SaveData(TBox, Data)
    SaveSetting "TxtSelect", TBox, "Data", Data
    End Sub

  9. #9
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Quote Originally Posted by mdmackillop
    BTW, the code may have written a couple of values tp your registry

    Private Sub SaveData(TBox, Data)
    SaveSetting "TxtSelect", TBox, "Data", Data
    End Sub
    as long as it doesn't slow down my computer i dont really care

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Matt,
    I've updated the Excel file in the zip file to change the word "tab" to Char(9) and add the = sign etc. Seems to work.
    MD

  11. #11
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Thats a smart idea! You may want to change it to #tab or something just in case one of the cells needs to contain the actual word tab.
    Glad you got it straightened out!

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Good idea,
    I've also sorted out a #Var routine that inserts a macrobutton in word to allow for easy insertion of variables

  13. #13
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hey, MD. Is this solved, then?

    ~Anne Troy

  14. #14
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Slap on the wrist!!!
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  15. #15
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    ROFL!!
    ~Anne Troy

Posting Permissions

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