PDA

View Full Version : Copy Multiple TextBoxes to Clipboard



JKyleO
02-19-2015, 11:55 AM
Hi, new here. I've had the best luck finding solutions on this site, but I can't seem to find one for this. I'm working a userform in VBA and need to create a button that copies multiple text boxes from a user form into clipboard. I have an example here:

Sub CommandButton1_Click()

Dim MyData As New DataObject
MyData.SetText TextBox2.Text
MyData.PutInClipboard

End Sub

I know it's something easy I'm missing, can anyone help?!

JKyleO
02-19-2015, 02:44 PM
I tried changing the command a little to included textbox.copy and just do a copy/paste without explicitly moving the text to the clipboard. But that just ended with more errors, and at one point just crashed everything. Lol, any ideas?

JKyleO
02-19-2015, 06:53 PM
I tried adding a bit more to my textbox. I guess I'm trying to combine my original with:
Private Sub TextBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


Dim myClipbd As New DataObject


If TextBox2.Text <> "" Then
With myClipbd
.SetText TextBox2.Text
.PutInClipboard
End With

Else: MsgBox "There is no text to copy. To enter text,click this textbox once."


End If


End Sub
I'm not sure if I actually have to add a loop, something to scan all the textboxs and add them to a single clipboard.

mancubus
02-20-2015, 02:15 AM
thanks for opening a new thread as i posted in:
http://www.vbaexpress.com/forum/showthread.php?51827-Copy-Multiple-TextBoxes-to-Clipboard


visit below to learn more about clipboard-excel:
http://www.cpearson.com/excel/Clipboard.aspx

that said;
what is your ultimate goal?
is it necessary to put the textboxes' contents in clipboard?
cam you post your workbook here? (via Go Advanced, Manage Attachments)

JKyleO
02-20-2015, 10:20 AM
Yeah sure, I'll post the full workbook. I'll have to remove a couple of sheets though.

My end goal, I want to have an application that I can launch, outside of excel. I want a simple interface that matches what I have on here. In the userform I have multiple text boxes, and I want to be able to copy the contents and labels into ITSM (helpdesk ticketing tool). I'm learning everything as I go, but forums like these have been incredibly helpful.

mancubus
02-20-2015, 05:35 PM
i'm not sure why it pastes ?? rather than the text when i hit ctrl+c in excel or another application...


a workaround from http://dailydoseofexcel.com/archives/2008/04/05/copy-paste-to-external-application/

it worked for me in notepad...

i;
opened notepad,
saved a blank document as "test.txt",
activated excel,
loaded the userform,
input a text in TextBox2,
clicked Copy button,
clicked OK in MsgBox,
(notepad activated)
hit F6 key.



insert a module to your project, and paste the below code.


'http://dailydoseofexcel.com/archives/2008/04/05/copy-paste-to-external-application/

Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

'Virtual-Key Codes http://msdn2.microsoft.com/en-us/library/ms645540(VS.85).aspx
Public Const VK_F6 = &H75
Public Const VK_ESC = &H1B

Sub WaitAndSend(SendString As String, ExecuteKey As Long, CancelKey As Long)

Do
DoEvents
If GetAsyncKeyState(CancelKey) <> 0 Then Exit Do
If GetAsyncKeyState(ExecuteKey) <> 0 Then
SendKeys SendString, True
Exit Do
End If
Loop

End Sub


replace existing CommandButton1_Click code with


Sub CommandButton1_Click()

MsgBox "Click OK, when the external application activated, press F6 key on the keyboard"
AppActivate "test.txt - Notepad"
WaitAndSend TextBox2.Text, VK_F6, VK_ESC

End Sub



change "test.txt - Notepad" to your application name as seen on the screen when you hover the mouse pointer on it in window's taskbar.
or you can remove AppActivate line and make user manually activate the non ms application.

JKyleO
02-20-2015, 06:21 PM
I might not have explained exactly what I was shooting for. The worksheets are fine, they work how I want them to. I have to manually paste these textboxes in so that go into the correct field. ITSM is a web based ticketing application. In a perfect world I would take a call, gather all of that necessary information and then hit "Copy Field" (commandbutton) and then paste to the appropriate field on the web app. I was trying to find something that would gather the context in each box, along with the label name. Then I would manually paste, I'm not to keen on just letting it auto paste. Is there a way to write a string that would loop all the info, and copy. It would be an exact copy of the "Phone Template" worksheet, but I would later be able to create a full stand alone application based off this.

mancubus
02-21-2015, 07:38 AM
so you want to populate multiple text boxes in a web application from excel user form.

below video may get you started. watch other videos as well.

https://www.youtube.com/watch?v=GswfT0Mrr5M

you may need to input user id and password etc.
google is your friend. you can find thousands of resources.
it may not be an easy task... so good luck. :)

JKyleO
02-23-2015, 10:51 AM
I don't want to populate anything. I just want to have the option to copy. is there a string command that would pull all the information together so that I can paste as a single copy. Something similar to CONCATENATE?

mancubus
02-23-2015, 02:19 PM
Dim ufStr as String

ufStr = TextBox1.Value & "|" & TextBox2.Value & "|" & TextBox3.Value & "|" & ComboBox1.Value & "|" & Combobox2.Value


| is the delimeter. you can use any special character other than wild card characters.
eg, tab charater (vbTab), line feed (vbLf), carriage return (vbCr), tilde (~), etc...

JKyleO
02-23-2015, 03:30 PM
Thanks, that's awesome. What would I use to copy the contents from ufStr? Would this work if the textbox is empty?

mancubus
02-23-2015, 03:57 PM
it's a single string.
you cannot copy but write it to a cell.

Worksheets("Sheet1").Range("A1").Value = ufStr


ps: i'm trying to reply your questions one by one, and NOT trying to provide any solution to your case.

JKyleO
02-24-2015, 01:13 PM
No that's perfect. If we go one by one, then I might be able to get my lizard brain to understand what's going on. Is there a way to copy the string to the clipboard? In excel I have the template set so it just copies and I can paste it in whatever I want. I do appreciate all the help you are giving me.

mancubus
02-25-2015, 12:44 AM
missed one point from post#11:
assume all text/combo-boxes are empty. this time ufStr will be "||||"

below will assign all text/combo-boxes' values to ufStr if they are not empty.



Dim ctrl As MSForms.Control
Dim ufStr As String

For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MSForms.TextBox Or TypeOf ctrl Is MSForms.ComboBox Then
If Len(ctrl.Value) > 0 Then ufStr = ufStr & ctrl.Value & "|"
End If
Next ctrl

MsgBox ufStr

If Len(ufStr) > 0 Then ufStr = Left(ufStr, Len(ufStr) - 1) 'to remove the prevailing |
MsgBox ufStr

mancubus
02-25-2015, 12:50 AM
Is there a way to copy the string to the clipboard?

refer back to message 3.
or



Dim myClipbd As New MSForms.DataObject

myClipbd.SetText ufStr
myClipbd.PutInClipboard


or you can refer to post#6 for pasting in non MSOffice applications.



and again....ufStr is single string which holds the all nonblank TB/CB controls of userform with a delimeter |.