I’ve heard that you generally don’t need to either select or activate a range to use it in VBA. The following code pastes the contents of the clipboard to the Worksheet named “Clipboard” in three places: 1) in column A, 2) as a Comment on Cell A1, and 3) into the yellow TextBox 1. This code works at intended; however, I cannot find a way to do it without using Select or ActiveSheet. If anyone knows of a better way to do this without using select/activesheet, I’m interested in learning.

‘*********************
Sub paste5()
'
' paste5 Macro

Sheets("Clipboard").Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
Sheets("Clipboard").Shapes.Range(Array("TextBox 1")).TextFrame2.TextRange.Paste

ActiveCell.AddComment
ActiveCell.Comment.Text Text:=Sheets("Clipboard").Shapes.Range(Array("TextBox 1")).TextFrame2.TextRange.Characters.Text
End Sub
‘*************************
Also, I will be copying the information from a plain text field in another program and from there it pastes correctly in the Excel textbox as a column of companies. Sheet2 has a list of companies that if you first paste into Notepad, then copy that list and then hit the Paste button in the Excel workbook you can see how it functions. However, if you just copy the list from Sheet2 and then use the macro, it loses the hard returns – it becomes one long line. Same thing happens if you copy and then right click in the text box and paste manually – it loses the returns. Any ideas why? This isn’t critical because it works the way I need it, I just can’t figure out why and I’m curious.

Thanks for your time.

(P.S. - sorry my antivirus software (Kaspersky) is having a fit whenever I click the Manage Attachments button below - it won't let the page open for me to attache the file. All of the VBA code is in the above message)