Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Unanswered: Copying userforms in Excel

    Hi all

    I'm in the midst of a project that involves a series of userforms. They have rather a lot of controls on them (105 in some cases), and the types and configurations of the controls will be the same.

    Does anyone know of a means of copying userforms within the VBA editor? I've tried exporting the form to a .frm file, changing the name and importing it back, but then it fails some form of validation.

    TIA!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    The first option you mention - which is exporting the userform and then importing the .frm file - should be fine. If the import fails then a log file should be created which details the issues. I expect the reason it failed is that you can't have two modules with the same name in the same project. After you've exported the userform, rename the one in the VBE, then try importing the .frm. If you have any problems then show us the log details and hopefully we should be able to help.

    The second option you mention is copying userforms within the VBA editor. I'm not aware of any built-in functionality to do this, so it would be a case of adding a new userform and then copying everything over. MZ Tools, which is an add-in I highly recommend, happens to have a "Copy Control with Code" option which would make this process slightly quicker.

    The third option (which you don't mention) is to just have a single userform in your project and to create multiple instances of that userform at runtime. Given the information in the thread such as the fact that each userform in the series has the same controls, this is probably what I'd be doing here. Here's a generic VBA example to give you the general idea:
    Code:
        Dim ufmA As UserForm1, ufmB As UserForm1
        
        Set ufmA = New UserForm1
        Set ufmB = New UserForm1
        
        ufmA.Caption = "First Userform"
        ufmB.Caption = "Second Userform"
        
        ufmA.Show vbModeless
        ufmB.Show vbModeless
        
        'etc...
    Hope that helps...

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Thanks!

    I shall give those a try and report back.

    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Right. Having tried one of the suggestions, I realise that I wasn't completely clear in my initial description of the problem! While the controls are the same type, they need to have different names, as the names of the controls drive the end functionality.

    Exporting a userform to a .frm file also produces a .frx file. I can open them and edit them both in the text editor, but this changes some of the characteristics of the .frx file, generating the error message:
    Line 2: Property OleObjectBlob in frmRgtBgtG could not be set.

    I think, faced with having to rename objects on the form, and then again in the code sheet, I'll just create new forms from scratch each time. I found a shortcut to my end result, so it's not as much work as I originally thought.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •