PDA

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



ABKane
08-07-2020, 06:08 AM
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 :)

Aussiebear
08-08-2020, 01:14 AM
You could try formatting the cell with the resulting text as bold

ABKane
08-10-2020, 04:38 AM
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?

p45cal
08-10-2020, 11:59 PM
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

ABKane
08-11-2020, 02:26 AM
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?

p45cal
08-11-2020, 04:42 AM
see https://www.rondebruin.nl/win/s1/outlook/mail.htm

ABKane
08-11-2020, 05:03 AM
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!

p45cal
08-11-2020, 06:06 AM
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-copy-strings-to-clipboard-using-excel-vba.html
VBA-Excel: Putting Text In The Windows Clipboard – Excel-Macro
https://excel-macro.tutorialhorizon.com/vba-excel-putting-text-in-the-windows-clipboard/

ABKane
08-11-2020, 07:19 AM
I get the objData.PutInClipboard code, but it doesn't copy and paste the middle text bold.

p45cal
08-14-2020, 01:46 AM
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?

ABKane
08-14-2020, 03:23 AM
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

p45cal
08-15-2020, 06:06 AM
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.

Paul_Hossler
08-15-2020, 07:56 AM
FWIW ...

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

https://docs.microsoft.com/en-us/windows/win32/dataxchg/clipboard-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/mygenesis-home/portal-email-verification/


Is that it?

ABKane
09-21-2020, 05:08 AM
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 :)