Visual Basic for Applications/Clipboard VBA
< Visual Basic for ApplicationsSummary
Textboxes and Comboboxes can make use of both the VBA COPY and PASTE methods. These allow access to the computer's clipboard. This is useful when we intend to work with text boxes anyway, but is less convenient when we want to paste the clipboard's contents into a VBA variable or to copy a variable's contents to it.
A simple workaround in these circumstances is to use a single textbox in a userform as a temporary transfer point. For example, to copy something to the clipboard, just pass it to the textbox and then use the textbox's code to copy it to the clipboard. There is no need to ever see such a textbox or userform, since it need only be loaded, not shown. Similarly, pasting into an invisible textbox, followed by further transfer of the contents to some other variable, can complete the paste function. These are the simplest methods for clipboard-variable transfer that I have found.
The first code module, a userform module, gives the code textbox-clipboard transfers, with code to track the active box, and the second section gives the standard module code for transfer into and out of a VBA variable.
In and Out of Textboxes
With User Mouse Selections
The code module below provides the VBA code for a form module, (shown here as UserForm1). In it there are command button click routines for textbox Copy and Paste. To use the copy procedure the user simply selects some text then presses the button on the user form. To paste the contents of the clipboard into a textbox, the user must first place the insertion point somewhere within a textbox before pressing the requisite button.
In order to clarify which textbox is active, there is a mouse-up event for each, where a number is loaded into a module-level variable whenever a mouse is used in the box. Although this code is made for three textboxes, it can easily be extended to any number.
The code assumes that there is a user form UserForm1, with TextBox1, TextBox2, TextBox3, CommandButton1 and CommandButton2 in it. In addition, note that there is a module level variable in the code. Since the VBA code is fairly generic it applies to most MS Office applications.
Option Explicit
Dim nActTxtBx As Integer
Private Sub CommandButton1_Click()
'this is the "Paste at Cursor" button
'pastes clipboard active textbox's insertion point
'ie; the textbox last clicked with mouse
Dim oTxt1 As Control, oTxt2 As Control, oTxt3 As Control
Dim oFrm As UserForm, oTxt As Control, s As Long
Set oFrm = UserForm1
Set oTxt1 = oFrm.TextBox1
Set oTxt2 = oFrm.TextBox2
Set oTxt3 = oFrm.TextBox3
'get the textbox with the focus
Select Case nActTxtBx
Case 0
MsgBox "Please place the insertion point."
Exit Sub
Case 1
Set oTxt = oTxt1
Case 2
Set oTxt = oTxt2
Case 3
Set oTxt = oTxt3
Case Else
Exit Sub
End Select
s = oTxt.SelStart
With oTxt
.Paste
.SetFocus
.SelStart = s
End With
Set oFrm = Nothing: Set oTxt = Nothing
Set oTxt1 = Nothing: Set oTxt2 = Nothing
Set oTxt3 = Nothing
End Sub
Private Sub CommandButton2_Click()
'this is the "Copy Selected Text" button
'copies selected text from textbox to clipboard
'ie; the textbox last clicked with mouse
Dim oTxt1 As Control, oTxt2 As Control, oTxt3 As Control
Dim oFrm As UserForm, oTxt As Control
Set oFrm = UserForm1
Set oTxt1 = oFrm.TextBox1
Set oTxt2 = oFrm.TextBox2
Set oTxt3 = oFrm.TextBox3
'get reference to active textbox
Select Case nActTxtBx
Case 0
MsgBox "Please make a selection."
Exit Sub
Case 1
Set oTxt = oTxt1
Case 2
Set oTxt = oTxt2
Case 3
Set oTxt = oTxt3
Case Else
Exit Sub
End Select
'check that a selection was made
'MsgBox oTxt.SelLength
If oTxt.SelLength = 0 Then
MsgBox "No selection found."
Exit Sub
End If
With oTxt
.Copy
.SetFocus
.SelStart = 0
End With
Set oFrm = Nothing: Set oTxt = Nothing
Set oTxt1 = Nothing: Set oTxt2 = Nothing
Set oTxt3 = Nothing
End Sub
Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
'loads an integer to denote active textbox when mouse makes selection
nActTxtBx = 1
End Sub
Private Sub TextBox2_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
'loads an integer to denote active textbox when mouse makes selection
nActTxtBx = 2
End Sub
Private Sub TextBox3_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
'loads an integer to denote active textbox when mouse makes selection
nActTxtBx = 3
End Sub
In and Out of VBA Variables
This code should be placed in a standard module. The project needs a user form called Temp, with a single TextBox1 set with MultiLine=true. TextBox contents are always text, so this limits the scope of these procedures.
Option Explicit
Sub TestClipboardProcs()
'run this
CopyToClipboard "The string" & vbCrLf & _
"to copy..."
MsgBox GetClipboard
End Sub
Function GetClipboard() As String
'PASTES clipboard into function name as a text string
'project needs userform named Temp
'with TextBox1 in it set with property Multiline=true
Dim oTxt1 As Control, oFrm As UserForm
Dim s As Long
'load the temporary form
Load Temp
Set oFrm = Temp
Set oTxt1 = oFrm.TextBox1
s = oTxt1.SelStart
With oTxt1
.Paste
.SetFocus
.SelStart = s
End With
GetClipboard = oTxt1.Value
Set oTxt1 = Nothing
Set oFrm = Nothing
Unload Temp
End Function
Function CopyToClipboard(sStr As String) As Boolean
'COPIES parameter variable text string value to clipboard
'project needs userform named Temp
'with TextBox1 in it set with property Multiline=true
Dim oTxt1 As Control, oFrm As UserForm
If sStr = "" Then
MsgBox "Clipboard cannot hold an empty string."
Exit Function
End If
'load the temporary form
Load Temp
Set oFrm = Temp
Set oTxt1 = oFrm.TextBox1
oTxt1.Value = sStr
'copy textbox value to clipboard
With oTxt1
.SelStart = 0 'set up the selection
.SelLength = .TextLength
.Copy
.SetFocus
.SelStart = 0
End With
Set oTxt1 = Nothing
Set oFrm = Nothing
Unload Temp
CopyToClipboard = True
End Function