Consulting

Results 1 to 14 of 14

Thread: How do I link a cell to 2 non-bold cells and 1 bold cell and keep that formatting?

  1. #1
    VBAX Regular
    Joined
    Aug 2020
    Posts
    7
    Location

    Post How do I link a cell to 2 non-bold cells and 1 bold cell and keep that formatting?

    Hi

    I currently have three cells that I want to link to one cell via the formula "=A1&A3&B4". But cell A3 has bold text in it and I want to be able to preserve the bold text. How do I do that? Is that even possible?

    To give you an idea of what it is I am doing (for possible work around ideas):
    I have created an Excel sheet that makes e-mail writing easier for me with templates that automatically copy to my clipboard. Now the cell that displays the final E-Mail that should be copied to the clipboard is made up a formula that is pulling information out of multiple cells. The e-mail has one word (in the signature) that should be bold. It HAS to be bold (unfortunately)... And I can't figure out how I can do this.
    Does anyone have an idea on how I could do this?

    It would be a massive help!

    Cheers
    Aidan

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    You could try formatting the cell with the resulting text as bold
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Regular
    Joined
    Aug 2020
    Posts
    7
    Location
    This will not do the trick... Let's say I have 3 Words in 3 different cells: Alpha Bravo Charlie. I want to copy all three words to my clipboard via the Clipboard.PutInClipboard VBA text but I want the word Bravo to be bold. How could that work?

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    If cell I7 is where that formula is and you're already using vba, then instead of that formula, use vba to place the value in it and format it, eg.:
    With Range("I7")
      .Value = Range("A1").Value & Range("A3").Value & Range("B4").Value
      .Characters(Start:=Len(Range("A1").Value) + 1, Length:=Len(Range("A3").Value)).Font.FontStyle = "Bold"
    End With
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Aug 2020
    Posts
    7
    Location

    Last issue

    Quote Originally Posted by p45cal View Post
    If cell I7 is where that formula is and you're already using vba, then instead of that formula, use vba to place the value in it and format it, eg.:
    With Range("I7")
      .Value = Range("A1").Value & Range("A3").Value & Range("B4").Value
      .Characters(Start:=Len(Range("A1").Value) + 1, Length:=Len(Range("A3").Value)).Font.FontStyle = "Bold"
    End With
    Fantastic!

    This did do the trick to place it in one cell. Thanks for that!

    Now there is one problem left. I want to copy that cell (the one containing the bold and not bold text) into my clipboard so that I can paste it into a Word document including the bold and non-bold formatting.

    Any help with that?

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Regular
    Joined
    Aug 2020
    Posts
    7
    Location

    Not quite

    Quote Originally Posted by p45cal View Post
    see
    Either I am overseeing something or this is not what I am looking for. I need to be able to copy it to my clipboard. Word was only an example. I need to be able to paste the end-result into the mail program of my company (Not Outlook).

    Btw. Thanks for your help p45cal!

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    see:
    Windows Clipboard
    http://www.cpearson.com/Excel/clipboard.aspx
    How to copy strings to Clipboard using Excel VBA
    https://www.spreadsheet1.com/how-to-...excel-vba.html
    VBA-Excel: Putting Text In The Windows Clipboard – Excel-Macro
    https://excel-macro.tutorialhorizon....ows-clipboard/
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Regular
    Joined
    Aug 2020
    Posts
    7
    Location
    I get the objData.PutInClipboard code, but it doesn't copy and paste the middle text bold.

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Could you show us the code you've been trying - it's difficult to make suggestions blind.
    What is the email app used by your company?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    VBAX Regular
    Joined
    Aug 2020
    Posts
    7
    Location
    The Application is called Genesis.

    This is the Code I have been using so far.

    Sub ClipboardMain()

    If Range("A10").Value = 1 Then

    MsgBox "This message is not available in the selected language."

    Else

    Dim Clipboard As MSForms.DataObject
    Set Clipboard = New MSForms.DataObject

    Clipboard.SetText ActiveSheet.Range("B10")
    Clipboard.PutInClipboard

    End If

    End Sub

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I can't find any reference to a Genesis email app on the interweb. Does it ahave a publicly available API?
    I looked quite hard a for a solution to put formatted text from a cell(s) into the windows clipboard so that it's available to be pasted with Ctrl+v into another application. I can do it with Word, but that's an MS Office App like Excel.
    Does/Can your Genesis app use an HTML body? I feel it might be easier to adapt existing vba code such as Ron de Bruin's if the answer is yes.
    I feel quite 'in the dark' still abouot what you want doing.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    FWIW ...

    1. Formatted text does not seem to be one of the standard formats

    https://docs.microsoft.com/en-us/win...pboard-formats

    so it looks like formatting is lost when you put it on the clipbaord


    2. As p45cal says, I can copy/paste from Excel to Word and keep the formatting. Maybe you can 'tag' the bold in Excel

    "AAAA <b>BOLD GOES HERE</> Back to normal"

    and paste as HTML or run a Genesis macro (if there is such a thing) or do a find and replace to format


    3. Guessing here, but a quick Google returns "Genesis email" but it looks like a web-based email system used by

    https://www.genesishealth.com/mygene...-verification/


    Is that it?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    VBAX Regular
    Joined
    Aug 2020
    Posts
    7
    Location
    Ok. First of all, thanks again to everyone that has answered.

    I have an excel document that pulls information from various cells from within that document to combine those text fragments to become the text of an e-mail. Example:
    Cell A1 = "Dear "
    Cell A2 = "=IF(B2=1;"Mr. ";IF(B2=2;"Ms.";""))
    Cell A3 = "=B3" (B3 equals the name of the client)
    Cell A4 = "Thank you for your message. We will be in contact with you shortly. Best regards"
    Cell A5 = "=B5" (B5 equals the name of the person writing the e-mail)
    Cell A6 = "COMPANY NAME"
    Cell A7 = "COMPANY ADDRESS"
    Cell A8 = "=A1&A2&A3A4&A5&A6&A7"

    So: The cells A1:A7 contain all the information individually. Cell A8 combines them to a usable e-mail message. I want to be able to combine those cells and copy them to my clipboard. Cell A6 is bold and it MUST remain bold. I then want to paste it to GENESYS. An e-mail application that doesn't work with VBA, so I want to be able to "Ctrl.+v" in GENESYS to paste the message after copying it with the click of a button in excel using VBA.
    My issue is: I cannot get excel/vba to copy and paste the COMPANY NAME bold.

    I hope this explanation is more detailed. For any more questions, ask away
    Last edited by ABKane; 09-21-2020 at 06:24 AM.

Tags for this Thread

Posting Permissions

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