Excel - Excel VBA - Using same code to apply to multiple textboxes on Userform

Hi Excel VBA'ers,

Via Google I found some great code (see below) that enables MouseDown
to paste what is in clipboard. I want this code to apply to ALL of the
textboxes in my Userform. Instead of repeating this code multiple
times for every textbox, I was wondering how/if I can specify multiple
textboxes using the one instance of the code please.

(Hmm, if I am on the right track, perhaps I have to learn about Class
Modules to do this??)

Here is the code that I found:

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
Set doclip = New DataObject
doclip.GetFromClipboard
scode = doclip.GetText
scode = Replace$(scode, Chr$(145), Chr$(39))
scode = Replace$(scode, Chr$(147), Chr$(34))
scode = Replace$(scode, Chr$(148), Chr$(34))
doclip.SetText scode
doclip.PutInClipboard
scode = doclip.GetText
TextBox1 = scode
End Sub

Couple of alternatives, depending on exactly what you are after:

There is a userform mouse_down event and the x and y values give you
the position of the cursor on the userform.  Run this and see what happens...
'---
Private Sub UserForm_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
MsgBox "Button = " & Button & Chr$(13) & _
End Sub
'---

'Or you could just have one DataObject code sub and
'call it from each textbox mouse down event:
'---
'Code similar to this for each textbox...
Private Sub TextBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim strReturn As String
Call GetMeTheData(strReturn)
TextBox1.Value = strReturn
End Sub

'Just one instance of this code...
Private Sub GetMeTheData(ByRef sCode As String)
Dim doclip As DataObject
Set doclip = New DataObject
doclip.GetFromClipboard
sCode = doclip.GetText
sCode = Replace$(sCode, Chr$(145), Chr$(39))
sCode = Replace$(sCode, Chr$(147), Chr$(34))
sCode = Replace$(sCode, Chr$(148), Chr$(34))
doclip.SetText sCode
doclip.PutInClipboard
sCode = doclip.GetText
Set doclip = Nothing
End Sub
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in:  compares, matches, counts, lists, finds, deletes...)

ens...
...)
glegroups.com...

Thank you Jim. I will give the 2nd option a go & let you know.

Give the following a try. Copy/Paste the following code into the code window
for the UserForm...

'*************************START USERFORM CODE*************************
Dim TextBoxes() As New Class1

Private Sub UserForm_Initialize()
Dim Counter As Integer, Obj As Control
For Each Obj In Me.Controls
If TypeOf Obj Is MSForms.TextBox Then
Counter = Counter + 1
ReDim Preserve TextBoxes(1 To Counter)
Set TextBoxes(Counter).TextBoxEvents = Obj
End If
Next
Set Obj = Nothing
End Sub
'*************************START USERFORM CODE*************************

Next, add a Class Module to your project (click Insert/Class Module on the
menu bar), then Copy/Paste the following code into the Class Module you just
added...

'*************************START CLASS MODULE CODE*************************
Public WithEvents TextBoxEvents As MSForms.TextBox

Private Sub TextBoxEvents_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim doclip As DataObject, scode As String
Set doclip = New DataObject
doclip.GetFromClipboard
scode = doclip.GetText
scode = Replace$(scode, Chr$(145), Chr$(39))
scode = Replace$(scode, Chr$(147), Chr$(34))
scode = Replace$(scode, Chr$(148), Chr$(34))
doclip.SetText scode
doclip.PutInClipboard
scode = doclip.GetText
UserForm1.ActiveControl.Value = scode
End Sub
'*************************START CLASS MODULE CODE*************************

Note the at code inside the MouseDown event in the Class Module is the code
you posted with a Dim statement added to declare the variables and with the
last line changed to assign the modified text to the active control on the
UserForm (which I assumed still has the default name of UserForm1)... this
active control will be the one you left click in. Every TextBox on the
UserForm will be using this one MouseDown event. If you need to know which
TextBox was clicked into, just ask the ActiveControl property of the
UserForm...

MsgBox "Clicked TextBox name = " & UserForm1.ActiveControl.Name & "."

So give it a try. Copy something to the Clipboard that contains the
characters ??? ??? ???... here is a sentence you can use...

This is a ???test??? to see what???s what.

and then click in any TextBox to place the modified text into that TextBox.

Rick Rothstein (MVP - Excel)

dow
e
ust
*
Y As Single)
*
de
he
e
s
h
& "."
x.

Thanks Rick. Ooh a "class module" suggestion..my ..VBA world is
growing ;-)

I will also give your suggestion a go & get back here. it is Saturday
in Oz now so will do this on Monday at work. Hope you both have a g8
weekend.

indow
the
just
***
l Y As Single)
***
code
the
the
his
ich
me & "."
Box.

Hi, I have tried all ideas & decided that I would like to use Rick's
suggestion because I have 10 textboxes, therefore it is the least code
required. However, I am experiencing a problem as follows:

-  Both left & right mouse clicks pastes in the data.  I only want
the right mouse click to paste. I tried researching how to specify
right mouse click only but could not find anything.

If not possible to specify right mouse click only I will use Jim's
coding.

If you run the first piece of test code I provided you will see the "Button" value
displayed when you left and right click on the userform...
'---
Private Sub UserForm_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

MsgBox "Button = " & Button & Chr$(13) & _
End Sub
'---

So, use something like this to wrap around Rick's or my code...
If Button = xlSecondaryButton Then ' 2
'fill text boxes
End If
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel add-in:  convenience built-in)






Hi, I have tried all ideas & decided that I would like to use Rick's
suggestion because I have 10 textboxes, therefore it is the least code
required. However, I am experiencing a problem as follows:

-  Both left & right mouse clicks pastes in the data.  I only want
the right mouse click to paste. I tried researching how to specify
right mouse click only but could not find anything.

If not possible to specify right mouse click only I will use Jim's
coding.

You never told us you only wanted the right button to initiate this. The
simplest fix is to just put this line of code...

If Button <> xlSecondaryButton Then Exit Sub

as the first line of code in the Class Module's MouseDown event
(xlSecondaryButton is a predefined constant in VB for the right mouse
button). Here is the complete Class code if you just want to copy/paste it
over the existing code there...

'************************START CLASS MODULE CODE************************
Public WithEvents TextBoxEvents As msforms.TextBox

Private Sub TextBoxEvents_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim doclip As DataObject, scode As String
If Button <> xlSecondaryButton Then Exit Sub
Set doclip = New DataObject
doclip.GetFromClipboard
scode = doclip.GetText
scode = Replace$(scode, Chr$(145), Chr$(39))
scode = Replace$(scode, Chr$(147), Chr$(34))
scode = Replace$(scode, Chr$(148), Chr$(34))
doclip.SetText scode
doclip.PutInClipboard
scode = doclip.GetText
UserForm1.ActiveControl.Value = scode
End Sub
'*************************END CLASS MODULE CODE*************************

Rick Rothstein (MVP - Excel)

t
Y As Single)

You're right, I neglected to say right click only, I am sorry.  Your
code works great, thank you.

I then wanted to use that code on a multipage form. After a run time
err 13 "type mismatch" I learnt that had to specify multipage
activecontrol & just so you know you put me on the right track I did
this which works:

frmDOHVSRTemplate.MultiPage1.Pages(frmDOHVSRTemplate.MultiPage1.Value).Acti=
veControl.Value
=3D scode

e
it
*
l Y As Single)
*
ti=ADveControl.Value

Oops....for those learning like me:  frmDOHVSRTemplate =3D Userform1