Using Copy/Paste in userforms
Ok, first post over here... I hope I can add some useful stuff, it's getting hard with all these tremendous authors !
When using a userform with some textbox, it is usually useful to allow the user to right-click on the control to allow access to some "standard" commands, like Copy and Paste, just like other Windows application provide. Unfortunately, there is not a direct way to do this, you have to create the CommandBar, and control the actions that happen after that, as well as including the code for each textbox that you want to control.
I wrote this class module to help in this process. Simply add the class module to your project, add three lines of code to your userform module and you're done !
Here's how to do it. First, insert a new class module and name it 'clsBar'. Now, copy and paste this code in there
'Popup objects
Private cmdBar As CommandBar
Private WithEvents cmdCopyButton As CommandBarButton
Private WithEvents cmdPasteButton As CommandBarButton
'Useform to use
Private fmUserform As Object
'Control array of textbox
Private colControls As Collection
'Textbox control
Private WithEvents tbControl As MSForms.TextBox
'Adds all the textbox in the userform to use the popup bar
Sub Initialize(ByVal UF As Object)
Dim Ctl As MSForms.Control
Dim cBar As clsBar
For Each Ctl In UF.Controls
If TypeName(Ctl) = "TextBox" Then
'Check if we have initialized the control array
If colControls Is Nothing Then
Set colControls = New Collection
Set fmUserform = UF
'Create the popup
CreateBar
End If
'Create a new instance of this class for each textbox
Set cBar = New clsBar
cBar.AssignControl Ctl, cmdBar
'Add it to the control array
colControls.Add cBar
End If
Next Ctl
End Sub
Private Sub Class_Terminate()
'Delete the commandbar when the class is destroyed
On Error Resume Next
cmdBar.Delete
End Sub
'Click event of the copy button
Private Sub cmdCopyButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
fmUserform.ActiveControl.Copy
CancelDefault = True
End Sub
'Click event of the paste button
Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
fmUserform.ActiveControl.Paste
CancelDefault = True
End Sub
'Right click event of each textbox
Private Sub tbControl_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
If Button = 2 And Shift = 0 Then
'Display the popup
cmdBar.ShowPopup
End If
End Sub
Private Sub CreateBar()
Set cmdBar = Application.CommandBars.Add(, msoBarPopup, False, True)
'We'll use the builtin Copy and Paste controls
Set cmdCopyButton = cmdBar.Controls.Add(ID:=19)
Set cmdPasteButton = cmdBar.Controls.Add(ID:=22)
End Sub
'Assigns the Textbox and the CommandBar to this instance of the class
Sub AssignControl(TB As MSForms.TextBox, Bar As CommandBar)
Set tbControl = TB
Set cmdBar = Bar
End Sub
Now, to see how this works, create a new userform, and add a couple of textbox controls to it. Switch to the code pane and enter this code there:
Dim cBar As clsBar
Private Sub UserForm_Initialize()
Set cBar = New clsBar
cBar.Initialize Me
End Sub
Now run the userform. If you right click either of the controls, you'll see a popup bar that allows you to copy and paste the contents of the control.
I'll explain how the code works in a future post.
Harald Staff:
Very nice, Juan Pablo.
7 December 2004, 8:49 amClever and useful.
Frank Kabel:
Hi Juan
very nice indeed!
Frank
7 December 2004, 10:14 amStephen Bullen:
Note that this doesn't work if the text box is contained in a frame or multipage control, as fmUserform.ActiveControl returns the frame/multipage, not the text box. This can be corrected by using the following function instead of fmUserform.ActiveControl in the Copy and Paste procedures:
'Click event of the copy button
Private Sub cmdCopyButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
ActiveControl.Copy
CancelDefault = True
End Sub
'Click event of the paste button
Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
ActiveControl.Paste
CancelDefault = True
End Sub
Function ActiveControl(Optional ctlContainer As MSForms.Control) As MSForms.Control
If ctlContainer Is Nothing Then
'Get the active control on the form
Set ActiveControl = fmUserform.ActiveControl
Else
'Get the active control inside the container
Set ActiveControl = ctlContainer.ActiveControl
End If
'Have we got a text box yet?
If TypeOf ActiveControl Is MSForms.TextBox Then
Exit Function
Else
'No, so recurse through the container controls
Set ActiveControl = ActiveControl(ActiveControl)
End If
End Function
Regards
Stephen Bullen
7 December 2004, 10:25 amStephen Bullen:
Great technique, by the way. It can also obviously be used to provide right-click popups for anything on the form - such as sorting a list box. The only problem is one of educating users - few would even think to right-click a userform control.
7 December 2004, 11:13 amJuan Pablo González:
Thanks for the feedback guys !
And thanks for the correction Stephen, I forgot about trying with a frame.
The one thing that I really like about this technique is the "recursive" use of the class, the same class holding all its children classes...
7 December 2004, 11:32 amStephen Bullen:
>The one thing that I really like about this technique is the “recursive” use of the class, the same class holding all its children classes…
Sorry Juan Pablo, but I disagree. While it's kind-of nice that it's self-contained, I think it should be two classes:
clsBarHandler
- Private WithEvents cmdCopyButton As CommandBarButton
- Private WithEvents cmdPasteButton As CommandBarButton
- Private fmUserform As Object
- Private colControls As Collection
- Initialize()
- Class_Terminate()
- cmdCopyButton_Click()
- cmdPasteButton_Click()
clsTextBoxHandler
- Private cmdBar As CommandBar
- Private WithEvents tbControl As MSForms.TextBox
- AssignControl()
- tbControl_MouseUp()
This is mainly because in the 'parent' instance, you're not setting the cmdBar or tbControl module-level variables, while in the 'child' instance, those are the only two variables you're setting.
That said, I accept that the difference is rather more philosophical than practical :-).
7 December 2004, 11:46 amJuan Pablo González:
> That said, I accept that the difference is rather more philosophical than practical
Yep... I agree completely. I think its easier for users to just copy *one* entire class module and forget about it than have to use 2, 3 or more... you know, errors come from human sources
I guess the word that I was looking for earlier is self-contained. That is what I really like !
7 December 2004, 11:55 amJohan:
I'm not an Excel-VBA-guru, so maybe this comment is irrelevant. Where is the colControls-Collection cleaned up or destroyed? Or is colControls automatically destroyed?
If it is not automatically destroyed, you need to put the following code in Class_Terminate()
Set colControls = Nothing
or maybe this code:
' collection are reindexed -> remove first member on each iteration
Dim i As Long
For i = 1 To colControls.Count
colControls.Remove 1
Next i
Set colControls = Nothing
8 December 2004, 5:29 amThe Contrarian:
OK, someone needs to keep this in perspective -- and keep it simple.
The technique in this post is a great conceptual idea and has tremendous potential value for specialized forms management requirements.
So, not to take anything away from it...
But, for copy and paste, CTRL c and CTRL v do the job.
8 December 2004, 7:35 amHarald Staff:
Yes, someone needs to keep this in perspective. As long as you make applications for your own personal use, Ctrl C will definitely do.
But you should be amazed by the amount of people not familiar with that keyboard operation -or with rightclicks, for that sake (oh, there's a button over there too ?)
This is about making user friendly aplications for users with unknown experience and habits. Some expect rightclick menus everywhere -provide it. Some expect Ctrl V to work -provide it. Some expect Copy - Paste buttons -provide it. Some expect an Edit menu -provide it. "Simple" is not simple to build, it's simple to use. Really simple, intuitive applications requires miles of clever code and a forsenic pessimist of a developer.
8 December 2004, 11:32 amJon Peltier:
Harald -
>Really simple, intuitive applications requires
>miles of clever code and a forsenic pessimist of a
>developer.
I knew I liked you!
I had one setup for a fussy user, which had an array of textboxes on a form. He wanted to be able to switch to Word or his email (something awful called Notes) or Excel, copy some data from a table or range of cells or just an email pseudotable, then paste so it filled the array of textboxes. All with CTRL-V. So I had to make the code able to tell where the data came from, convert it to an array in VBA, and load the VBA array into the textboxes. A PITA to build, but it was pretty slick for the user.
Is that what you were talking about?
- Jon
8 December 2004, 5:32 pmDoug Glancy:
Great comment, Harald. "Forensic pessimist" is a wonderfully descriptive phrase. (Maybe you should trademark it.)
8 December 2004, 5:41 pmHarald Staff:
Yes.
8 December 2004, 5:46 pmHarald Staff:
Oops. The "Yes" was a reply to Jon's posting.
8 December 2004, 5:49 pmJamie Collins:
I too like this code and I intend to start using it today! However, I don't like the fact the two button functions - in this case, Copy and Paste - are hardcoded in the class. If I wanted to add a third function - say, Open Database Table - I'd have to add code to the class. I'd end up having a different class for each userform and the advantage of using a class would be lost.
I'd expect the code behind the button's Click event to be in the userform code module e.g. perhaps something more like this:
Option Explicit
Private cBar As clsBar
Private WithEvents ButtonCopy As clsButton
Private WithEvents ButtonPaste As clsButton
Private Sub UserForm_Initialize()
Set cBar = New clsBar
cBar.Initialize Me
Set ButtonCopy = cBar.Buttons.Add
Set ButtonCut = cBar.Buttons.Add
End Sub
Private Sub ButtonCopy_Click()
ActiveControl.Copy
End Sub
Private Sub ButtonPaste_Click()
ActiveControl.Paste
End Sub
Also, I'd want an option to specify the controls that would cause the popup to show e.g. I only want Open Database Table to be an option from the schema treeviiew an not, say, the connection string textbox.
I could modify the code myself but I'll give the author first refusal.
Jamie.
--
9 December 2004, 3:42 amJamie Collins:
Ok so I tackled this one anyway and had lots of fun. I ended up with eight classes:
CPopup (parent class)
> CPopupButtons (collection class)
> CPopupButton (holds WithEvents reference to a CommandBarButton and raises Click event to the client)
> CPopupControls (collection class)
> CPopupControl (interface)
> CPopupControlMSForms (Implements CPopupControl)
> CPopupControlMSComctlLib (Implements CPopupControl)
> CPopupMessenger (sends message from child control to tell the parent to show the popup)
I realized that I may have some userforms that do not use the MSComctlLib controls so I put have any reference to MSComctlLib in a separate class that could be removed without ill effect, hence the interface.
So my userform code actually looks like this:
Option Explicit
Private m_Popup As CPopup
Private WithEvents m_ButtonCopy As CPopupButton
Private WithEvents m_ButtonPaste As CPopupButton
Private Sub UserForm_Initialize()
Set m_Popup = New CPopup
With m_Popup
' Add buttons
Set m_ButtonCopy = .AddButton("Copy", 19)
Set m_ButtonPaste = .AddButton("Paste", 22)
' Associate with textboxes (MSForms library)
Dim oControlMSForms As CPopupControlMSForms
Set oControlMSForms = New CPopupControlMSForms
oControlMSForms.Init TextBox1
.Controls.Add oControlMSForms
Set oControlMSForms = New CPopupControlMSForms
oControlMSForms.Init TextBox2
.Controls.Add oControlMSForms
' Associate with treeview control (MSComctlLib library)
Dim oControlMSComctlLib As CPopupControlMSComctlLib
Set oControlMSComctlLib = New CPopupControlMSComctlLib
oControlMSComctlLib.Init TreeView1
.Controls.Add oControlMSComctlLib
End With
End Sub
Private Sub m_ButtonCopy_Click()
' Event hander code here
End Sub
Private Sub m_ButtonPaste_Click()
' Event hander code here
End Sub
If anyone is interested in the full code, post here.
Jamie.
--
9 December 2004, 7:24 amFrank Kabel:
Hi Jamie
nice approach. Could you email me the code (frank[dot]kabel[at]mummert[dot]de)
Thanks
9 December 2004, 11:26 amFrank
Dick:
Jamie: If you want to make a workbook available for download that has these classes in it, send it to me and I will post it up.
9 December 2004, 11:50 amJuan Pablo González:
Nice approach Jamie ! and I'd like to see the code too, so, I think Dick's idea is great for that.
9 December 2004, 11:58 amJamie Collins:
"If anyone is interested in the full code, post here"
Cripes, no one was supposed to actually be interested
I'll send a workbook to Dick today.
Jamie.
--
10 December 2004, 2:24 amMaurice:
Very appreciable tool! It seems doesn't work for textboxs on worksheets, but I'm too novice in VBA controls. Anyone can help?
19 January 2005, 3:24 ammick:
Hi gyus,
And What about Control: Select All, Cut, Delete?
Could by somebody make code for all action in TB, please?
THAX
2 February 2005, 7:48 amJamie Collins:
"What about Control: Select All, Cut, ...?"
This was the point I was making earlier: implementation code should not be in the class, otherwise one gets customers asking for further implementation code to be written for them.
If you are interested, take a look at my variation on this theme:
http://www.dicks-blog.com/archives/2005/01/04/update-to-popupmenu/
My attempt allows multiple custom buttons to be added to the popup and for each provides a Click event to accommodate the client code on the client-side.
This is merely a different approach and takes nothing away from JPG's code, which is self contained by design.
"It seems doesn't work for textboxs on worksheets"
Because mine is more general, it can be applied to controls on a worksheet in exactly the same way for those on a userform.
Jamie.
--
2 February 2005, 9:38 amVasile:
I tried this function on a mutipage and it doesn't work, because multipages do not support "activecontrol" property(Excel2003).
Anyone knows a workaround?
Function ActiveControl(Optional ctlContainer As MSForms.Control) As MSForms.Control
If ctlContainer Is Nothing Then
‘Get the active control on the form
Set ActiveControl = fmUserform.ActiveControl
Else
‘Get the active control inside the container
Set ActiveControl = ctlContainer.ActiveControl
End If
‘Have we got a text box yet?
If TypeOf ActiveControl Is MSForms.TextBox Then
Exit Function
Else
‘No, so recurse through the container controls
Set ActiveControl = ActiveControl(ActiveControl)
End If
End Function
by Stephen Bullen
Many thanks!
14 February 2005, 11:45 amVasile:
Sorry for bothering you with such a stoopid question.
I found the answer:
Me.MultiPage1.Pages(Me.MultiPage1.Value).ActiveControl.Name
Long live google!
14 February 2005, 11:53 am(and wish me success learning to write code ;))
Bill:
Complete noobie question here...
I just found this blog, so bear with me. I have copied the code in the original post above, works great EXCEPT:
I have the exact code as above in an open spreadsheet. If I then open another Excel spreadsheet, highlight some cells, and then hit Edit/Copy, I immediately get an error popup "Object variable or With block variable not set" -- it is choking on the cmdCopyButton_Click subroutine. Only happens the first time I try to use the Copy command; if I cancel the operation, then try the Copy command again, no error message.
Is there a fix to prevent this?
TIA.
7 February 2008, 11:59 amJuan Pablo Gonzalez:
Hi Bill,
I cannot replicate your problem either on Excel 2003 or Excel 2007. What version are you using?
8 February 2008, 10:52 amBill:
Hi Juan.
I actually programmed it in Excel 2000. But I just tested it in Excel 2003, and I get the same error. I'm now using your original code along with Stephen Bullen's edits shown in his initial post. In his ActiveControl function, it gets to the line "Set ActiveControl = fmUserform.ActiveControl", then chokes on "If TypeOf ActiveControl Is MSForms.TextBox". Hovering over ActiveControl at that point gives me "ActiveContol = Nothing", so apparently that Set command isn't picking up anything from fmUserform.ActiveControl.
A slight modification to exactly what happens: I open another spreadsheet and highlight some cells in it, hit Edit/Copy, and I get the error message I mentioned earlier. It's pretty harmless actually because I can simply cancel out of the error message, the cells are still highlighted and are on the clipboard. Still an annoyance though.
8 February 2008, 12:34 pmAlex:
Hi there,
5 May 2008, 2:23 amcould you please tell me, what exactly MSForms.Control is, or why my Excel/VBA does not compile code with it (it says this type is not defined...)?
Thx,
Alex
Dick Kusleika:
MSForms is a library that contains a Control class. Other libraries also contain a Control class, so if you want a specific one, it's best to be explicit. You can add MSForms manually (look for Microsoft Forms 2.0 Library under Tools References) or it is added for you if you have a userform in your project.
13 May 2008, 4:22 pmjinpol:
hi,
will this work even if my textbox is in excel sheet? tnx
17 June 2008, 6:03 am