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

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:

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.

Copy/Paste toolbar

I’ll explain how the code works in a future post.

39 thoughts on “Using Copy/Paste in userforms

  1. 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

  2. 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.

  3. 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…

  4. >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 :-).

  5. > 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 !

  6. 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

  7. 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. 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.

  9. 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

  10. Great comment, Harald. “Forensic pessimist” is a wonderfully descriptive phrase. (Maybe you should trademark it.)

  11. 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.

    –

  12. 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.

    –

  13. 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.

  14. Nice approach Jamie ! and I’d like to see the code too, so, I think Dick’s idea is great for that.

  15. “If anyone is interested in the full code, post here”

    Cripes, no one was supposed to actually be interested :-o

    I’ll send a workbook to Dick today.

    Jamie.

    –

  16. Very appreciable tool! It seems doesn’t work for textboxs on worksheets, but I’m too novice in VBA controls. Anyone can help?

  17. Hi gyus,

    And What about Control: Select All, Cut, Delete?
    Could by somebody make code for all action in TB, please?

    THAX

  18. “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.

    –

  19. 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!

  20. Sorry for bothering you with such a stoopid question.
    I found the answer:

    Me.MultiPage1.Pages(Me.MultiPage1.Value).ActiveControl.Name

    Long live google!
    (and wish me success learning to write code ;))

  21. 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.

  22. Hi Bill,

    I cannot replicate your problem either on Excel 2003 or Excel 2007. What version are you using?

  23. 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.

  24. Hi there,
    could 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

  25. 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.

  26. I recently found this forum. I know its old and i dont even know if you guys are still actively reading this but im about to go insane. Im working with excel 2003. Im currently writing a program to simply one of our very common routines in my office. Im using excel because i can build the program in the VBA editor and send it to everyone without having to go through the rigamorale (sp?) of corporate security to add a new program. I have completed an intermedite level multipage userform. and at first i was willing to dismiss right click….with such helpful words for my coworkers like “if cant ctrl+c and ctrl+v you shouldnt have a job using computers in the first place” however now that ive started messing around with and the helpful parts of this forum have given me some right click functionality, it has become my obsession.

    In short. I really need the complete code to add Right click copy/paste commands to a multipage user form.

    I tried using the main code then upon reading further the portion about not working with a multipage. i attempted to add the exception but must be incompetent about where to put or the several hundred lines of code i already have is causing a problem. although it shouldnt since VBA seems to mostly work isolated subroutines …..anyway

    if anyones out there and can help jeremyweck@gmail.com

    HACK THE PLANET

  27. I have followed original instructions with one class added as specified and it works so great thanks to Juan Pablo Gonzalez. However, when I right click I have only “Copy” and “Paste” options/buttons. How and where can I modify Juan’s original code to include “Cut” and “Delete”? Thank you.

  28. This all looks as though it works well until the box pops up and the two options are greyed out. The code seems to work, I don’t get any errors, I just can’t use the options as they are greyed out and you aren’t able to select them.
    Am I missing something?

  29. Catherine: I reformatted the code above to remove some illegal characters put in there by the blog software. Try copying and pasting again and see if that takes care of the problem.

  30. Sorry if this is a stupid question for most of you, but how would I have to change the code to use it on the items of a listbox? Is it even possible?

  31. xlnap: The problem is that listboxes don’t have a Copy method. So everywhere in the code where you see ActiveControl.Copy it would error for a listbox. I tried to modify the code to read whatever was selected in the listbox and put it into the clipboard manually, but it didn’t work and I couldn’t figure out why.

    Perhaps you could have a hidden textbox that you update when the listbox changes and copy from that.

  32. Thanks Dick. I will take another look. The copy function isn’t actually what I want. I would like to add the pop up menu to my listbox as a context menu to do several things (not necessarily a copy function) yet I don’t get the code to work.

  33. Hi all,
    trust you are well.
    I am currently trying to get the right mouse click menu to work in a userform textbox. Your code works a charme (I’ve used the revised version on a different page) but I am still hitting a wall when it comes to the following scenario:
    Workbook with userform is opened, userform shows, right mouse click works fine.
    But for my userform users are required to open a second workbook and from there copy some cells and paste them into the userform. Now whenever I am doing this I hit an error msg “Method ‘ShowPopup’ of object ‘CommandBar’ failed. I’ve tried different things e.g. workbook_open, workbook_activate, userform_activate. etc but I always end up with the error.
    I would be grateful for any suggestions on how to solve this! Thx ever so much.

Leave a Reply

Your email address will not be published. Required fields are marked *