VBA for open comment by clicking cell, formatting, paste and clear all from clipboard

[vicwootn]

I've been googling this for awhile. Sometimes I think I get pieces of the code but seem to ulimately fall short. Anything you can do to help would mean so much to me. Here is what I'm looking for.

When I have exactly four items in my Office Clipboard I want to Paste All into the comment of the selected cell then Clear All inside the clipboard. I would prefer never to have to open the comment myself.

I have some specific conditions for the paste. Before pasting, if there is already text inside the comment I want it all deleted then to be cursored at the top of the blank comment, then Paste All. If there is no existing comment I want to insert one, delete the comment heading, then cursor me at the top of the blank comment, then Paste All.

The Paste All needs to be bold text.

Each item is a decimal number to the tenth or hundredth. The last item will always be exactly two numbers to the tenth or hundredth. Here is an example of what the clipboard will look like :

Click an item to paste :
7.24
7.7
9.65
8.4 8.21

When I Paste All, I would like to swap the last two numbers and add a Return space between them, formatted like :

7.24
7.7
9.65
8.21

8.4

Again, any help would be deeply appreciated even if it's pieces.
Thank you!!!

 

[Krayons]

Reading material.

1. Microsoft MSDN: Retrieve Information from the Clipboard


2. http://www.mrexcel.com/forum/excel-questions/662938-how-paste-then-nth-item-office-clipboard.html


3. Paste clipboard contents using VBA


4. MSDN: Comment Object


5. How to Add and Remove comments from cells

 

[vicwootn]

Taking a look now. Thank you!

 

[Krayons]

I have some specific conditions for the paste. Before pasting, if there is already text inside the comment I want it all deleted then to be cursored at the top of the blank comment, then Paste All. If there is no existing comment I want to insert one, delete the comment heading, then cursor me at the top of the blank comment, then Paste All.

Accessing the comment object through VBA in excel does not operate in such a way where you would be required to move your cursor or remove any heading. I believe that heading you are referring to is only created if you insert a comment through excel's menus.

Sub commentHandler()


    Dim rng As Range
    Dim myData As String
    
    Set rng = Range("A1")
    
    myData = "lorem ipsum"


    'if no comment exists
    If rng.Comment Is Nothing Then
    
        'create one
        rng.AddComment
        
        'and insert your data
        rng.Comment.Text myData
        
    'if a comment exists and contains data
    ElseIf rng.Comment.Text <> "" Then
    
        'clear it
        rng.clearComments
        
    End If


End Sub

 

[vicwootn]

Reading material.

1. Microsoft MSDN: Retrieve Information from the Clipboard


2. http://www.mrexcel.com/forum/excel-questions/662938-how-paste-then-nth-item-office-clipboard.html


3. Paste clipboard contents using VBA


4. MSDN: Comment Object


5. How to Add and Remove comments from cells

Unfortunately these are links I've looked through in the past three days working on this problem. I appreciate you taking the time to forward them anyway.

 

[Krayons]

Virtually everything you need to build the foundational code are in those links. Formatting your data is easy and should worry about that after you've fleshed out the basic functions.

'Clearing the Office Clipboard


Dim oData   As New DataObject 'object to use the clipboard


oData.SetText text:=Empty 'Clear
oData.PutInClipboard 'take in the clipboard to empty it

 

[vicwootn]

I can't seem to flesh much out. I've been at it for days but I'll continue trying. Thank you

 

[Krayons]

I've been digging into this some more. From what i've been reading, the office clipboard is not exposed to access by code. The only exception has been Office 2000 where you were able to access it in a limited manner through the commbar bar that used to be present. Now days, you can only use VBA to access the Windows Clipboard.

Alternatively, use a macro to check the clipboard every second and copy any new entries into a worksheet. Then manipulate the data from that worksheet as you need. This is likely your only option.

Here are some example workbooks to look at. Workbook Example #1 copies the last data from the clipboard and pastes it into a cell's comment. If no comment exists, it inserts one. If one already exists, it clears it and then pastes the clipboard data.

Workbook Example #2 will check the windows clipboard every second. If the last entry in the clipboard is different than the last item copied to the worksheet, it adds it.

Workbook Example #1: https://1drv.ms/x/s!AnpELGec0aCqlbo-qtnZOP-2uXWoaQ
Workbook Example #2: https://1drv.ms/x/s!AnpELGec0aCqlbpS7tju4GvgRmTGNQ

 

[vicwootn]

Thank you this is really helpful for me to sift through and learn from! I really appreciate it.