Adding forms to a workbook programatically

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176
I have a series of spreadsheets used by workers to record what they've done on a particular day. Each one uses a form to validate their entries.

Every so often though, I need to update the form, and as the number of worker's spreadsheets is steadily increasing this gets more tedious and lengthy every time. :confused:

I'd like to create a macro that, given a list of filenames and locations will go through the list, open each file, insert a new userform, save and then close.

I can manage most of this but getting Excel to insert a fresh form is causing problems. I found a MS KB article (219905) telling you how to do this from VB, but unsure how to acheive it from VBA. Any thoughts?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

seems like you are only asking to copy a userform from one book to another: everything else you can handle yourself

try this
assuming the "recieving" workbook is open
I didn't search for a way to do it when the workbook stays closed, which could be possible ..?
Code:
Option Explicit

Sub CopyUserForm_to_otherWB()
'Erik Van Geit
'061013

'set reference:
'Microsoft Visual Basic for Applications Extensibility
'via Tools-References

Dim FileNm As String
Dim TempPath As String
Dim SourceWB As Workbook
Dim otherWB As Workbook
Dim msg As String

Set SourceWB = ThisWorkbook
Set otherWB = Workbooks("Book2")

Const UserFormName As String = "UserForm1"

On Error GoTo errHandler

    TempPath = SourceWB.Path & "\nameyouwontevergivetoafolder"
    MkDir TempPath
    FileNm = TempPath & "\" & UserFormName
    SourceWB.VBProject.VBComponents(UserFormName).Export FileNm
    
    otherWB.VBProject.VBComponents.Import FileNm
    'delete folder you just created
    DeleteFolder TempPath

Exit Sub

errHandler:
msg = Err.Description & vbLf & "Continue ?"
    If MsgBox(msg, 36, "ERROR " & Err.Number) = vbYes Then
    Err.Clear
    Resume Next
    End If

End Sub

Public Sub DeleteFolder(FolderNm As String)
'set reference:
'Microsoft Scripting Runtime
'via Tools-References

Dim scrFSO As Scripting.FileSystemObject
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File

Set scrFSO = New Scripting.FileSystemObject
Set scrFolder = scrFSO.GetFolder(FolderNm)

    For Each scrFile In scrFolder.Files
    scrFile.Delete
    Next scrFile
    
    RmDir FolderNm

End Sub

kind regards,
Erik
 

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176
If I'd known I'd get a reply that quick I would have checked back sooner...

Cracked this problem over lunch; I now have a simple routine that will go to a specified folder and open a set of (predictable) filenames, add a form, close, and save.

My main difficulty now is removing the form already present in the workbook. I couldn't find anything about this in VBA help, and although there's a nice piece on the j-walk blog which does something similar, I can't make it work for me.

This is my line:

Code:
Application.Workbooks(TargetFileName).VBProject.VbComponents.Remove VbComponent=Userform1

My difficulty is the syntax after Remove. I want to remove userform1. This isn't doing it. Any answers?
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,791
HTH. Dave
Code:
'add userform
Set Uf = ThisWorkbook.VBProject.VBComponents.Add(3)
'Remove the userform
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=Uf
 

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176

ADVERTISEMENT

With a little tweaking, works perfectly. Thank you!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Cracked this problem over lunch; I now have a simple routine that will go to a specified folder and open a set of (predictable) filenames, add a form, close, and save.
please post your code
it's kinda strange: you've put a request & recieved code
you tell us you solved it another way: FINE! but share it as I did
 

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176

ADVERTISEMENT

Okay...

I'm never too keen on posting code as
a: I'm self-taught
b: It's not particularly good code
c: I haven't moved on much since I first learned to program on the ZX Spectrum...
d: The routine I've got is unfinished. It will open three specific files (test1.xls, test2.xls & test3.xls) in a specified directory, copy a new userform into them from c:\, save and close.

Right now I'm reading up on the error handling this will require before it get used in a non-trivial environment. Which looks like being more hassle than getting the form insertion to work.


Code:
Sub testinsert1()
Dim TargetDir As String
Dim TargetFileName As String
Dim FormFile As String
Dim n As Integer
Dim Uf As Object

MacroFile = "c:\userform1.frm"
TargetDir = "c:\macro insertion test\"
n = 1

Do Until n = 4
    TargetFileName = "test" & n & ".xls"
    
    Workbooks.Open Filename:=TargetDir & TargetFileName
   
    With Workbooks(TargetFileName)
        Set Uf = .VBProject.VBComponents("Userform1")
        .VBProject.VBComponents.Remove VBComponent:=Uf
        .VBProject.VBComponents.Import (FormFile)
        .Close (True)
    End With

    n = n + 1

Loop
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Kevlarhead

There's nothing I can see wrong with that code, and nothing I can see that needs changed.

By the way have you actually tried it in the 'non-trivial' environment?

I assume by that you mean some sort of work environment.

If that's the case you may want to take a look into the security issues of using code to write to the VBE, which is basically what you are doing.

Have you not considered using a template which contains the userform?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Kevlarhead,

thank you for sharing what you're using!
I see my interpretation of your question was somehow different from what you meant. Norie is talking about a template: that's what I was doing: take the form from a workbook.

best regards,
Erik
 

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176
Quick clarification; for my purposes a 'non-trivial' environment means 'one where all required files may not be in one location and where unexpected things may happen (e.g. directories or files may be missing or opened by other users).'

And I had been considering the security implications of trusting macros to write to the VBE. If I was to digitally sign my macros would it then be possible to trust only those items with my sig, rather than trust absolutely everything? (which is obviously asking for trouble)

Problem with issuing a new template is that the engineer's work data is held on their own spreadsheet. Issuing a new template would mean a new file, which mean copying across the old data, which is the sort of thing I was hoping to avoid, as it's tedious with 20 workbooks (right now), and stupid with 70 (the future). Hence the interest in changing macros centrally.

(It occured to me just now that having a single central .frm file loaded into each engineer's spreadsheet when it's opened might work: as long as importing the .frm file didn't tie it up for too long and deny other users the opportunity to use it... I'll look into this)
 

Forum statistics

Threads
1,136,878
Messages
5,678,320
Members
419,755
Latest member
eshnider21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top