Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

[DocAElstein]

Notes in support of this Forum Question:
https://www.eileenslounge.com/viewto...p?f=18&t=33834

Trying to figure out how Excel Clipboard ( or maybe Office clipboard ) is interacting with the Windows clipboard when trying to paste into Excel a text string,….

I have a text string. I add to it, manipulate it a bit, then put it in "the clipboard", ( probably the windows clipboard ) , then do an Excel Worksheets Paste …

Simplified example…
I have this already in a string,
"A" & vbCr & vbLf & "C"
Seen in another way:
A_vbCr_vbLf_B
The first representation is in a typical code line convention, the second an attempt at a more "real" view
There are 4 characters there. The middle two are examples of types that are often referred to as "invisible" characters. (These two typically instruct systems to go to a new line)
Most software that visibly gives you some sort of text to see , would usually interpret that as two lines of text. For example, Excel would usually interpret that such as to display you something like this, if you somehow "put it in" the start ( top left ) of a spreadsheet:

Row\Col	A
1	A
2	C

I want to put an extra cell with split line text in it, simplified like this:

Row\Col	A
1	A
2	X Y
3	C

Sometime or other I have learnt that Excel recognises a single vbLf to split up lines of text within a cell. But it turns out to be bit more complicated than that if you want the text string in the Windows Clipboard to come out as you want it in Excel.

The second part of this macro seems to usually achieve the second screenshot above

Sub TestvbLf_1()
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Dim StringBack As String
' Fill two rows , in 1 column
 ActiveSheet.Cells.Clear ' This is important to remove any formatting that might distort results
 objDataObject.SetText "A" & vbCr & vbLf & "C": objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
 objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
 Call WtchaGot(StringBack) '         "A" & vbCr & vbLf & "C"
' Put an extra cell with split line text in it
 ActiveSheet.Cells.Clear '
 objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C": objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
 objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
 Call WtchaGot(StringBack) '          "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
 
End Sub

( The function, WtchaGot( ) can be found here: http://www.excelfox.com/forum/showth...ll=1#post10946 )

---

Here are some further sample code snippets and discussions

This code snippet suggest to me that the Windows clipboard is being used, as .Clear does not empty "the clipboard" , as one more typically annoyingly experiences in Excel work when copying things manually or with VBA in Excel

Sub TestvbLf_2()
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Dim StringBack As String

' Put an extra cell with split line text in it
 ActiveSheet.Cells.Clear '
 objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C": objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
 objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
 Call WtchaGot(StringBack) '   ---    "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
 
 ActiveSheet.Cells.Clear ' This does not clear the clipboard, so next line gives the same results, which...
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1") '   suggests that the Windows  clipboard is being used
 Call WtchaGot(StringBack) '   ---    "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
'
End Sub

Finally we see this, the orange of the clipboard icon indicating something is in "the clipboard", presumably the windows clipboard, since , as said, .Clear does not clear that icon, and also we see that the office clipboard is empty…
TestvbLf_2.JPG : https://imgur.com/dEbsaPE
Attachment 2565

( Using Excel 2007 32 bit )

A	19 Dez 2019 Lenf is 11	A "X Y" C
X Y	1 A	65
C	2	13
	3	10
	4 "	34
	5 X	88
	6	10
	7 Y	89
	8 "	34
	9	13
	10	10
	11 C	67

Worksheet: WotchaGotInString
The second two columns are produced by function WtchaGot( ) , and give a breakdown of the 11 characters in the string:
"A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
A vbCr vbLf " X vbLf Y " vbCr vbLf C

Further investigation in next post