CODEWITHSUNDEEP
vbams-wordclipboardcopy-paste
NewOrleanTaints
NewOrleanTaints

Reputation: 5

Copying the content of multiple UserForm textboxes to clipboard

I built a Microsoft Word template with a userform containing textboxes. I want to copy the data of two textboxes into the clipboard separately upon submitting the completed userform. Just to clarify I want each textbox to have its own entry into the clipboard for easy access in the future.

Below is the code I'm using but when I repeat the procedure it replaces the first textbox in the clipboard with the second. Any help would be greatly appreciated.

Sub ComandButton_click()
  Dim clipboard As MSForms.DataObject
  Set clipboard = New MSForms.DataObject

  clipboard.SetText Me.TextBox1.value
  clipboard.PutInClipboard
End Sub

EDIT (additional background):

For accessing the information put on the Clipboard I created a custom QAT. One of the buttons on the toolbar opens the clipboard so they can easily begin to paste the name they need at any one time. Most reports involve two people so I want to send the value of two textboxes into the clipboard as if you individually selected and copied each name. My first idea was to assign a keyboard shortcut to the value of the two textboxes but I settled on utilizing the clipboard tab.

Upvotes: 0

Views: 1117

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25693

Given what you've told us, I'm guessing you're calling up the Office Clipboard. This is different from the Windows Clipboard, which is what your code is using. The Office Clipboard is not exposed to the developer (for internal, MS use only). There is a way to put information on it, but I'm uncertain how reliable it is; YMMV (your mileage may vary).

The code for your UserForm to call a procedure responsible for writing the information:

PutContentOnOfficeClipboard Me.[textBoxName].Value
PutContentOnOfficeClipboard Me.[textBoxName].Value

The procedure writes the text to the end of the document, copies it, then deletes again. In my tests, this all shows up on the Office Clipboard:

Sub PutContentOnOfficeClipboard(s As String)
  Dim rng As word.Range

  Set rng = ActiveDocument.Content
  rng.Collapse wdCollapseEnd
  rng.Text = s
  rng.Copy
  rng.Delete
End Sub

If it turns out to be unreliable, then you need a different approach. Here are some thoughts on alternative methods...

A taskpane is optimal as it makes the information visibly available users. Unfortunately, task panes aren't available to the VBA Developer. They can be used from a VSTO solution or an Office-JS solution, but that may be more effort than you're willing/able to put into the project (steep learning curve, enterprise deployment requirement, etc.)

Besides keyboard shortcuts (which aren't a bad idea to have in addition to any visible interface):

  • A dropdown list on the QAT or the Ribbon
  • Two buttons on the QAT or the Ribbon
  • Modify the Right-click (context) menu with two controls to which you can write the names as needed. (Requires call-back macros to make the dynamic update)
  • Save the entries as AutoText/Building Blocks, possibly to use in conjunction with a BuildingBlock content control (which can be filtered to show only your entries; AutoText can also be used selectively from the keyboard).
  • Make a small, non-modal UserForm with buttons. You could optionally use the Windows API to make it "always on-top".

Upvotes: 0

Related Questions