Adding forms to a workbook programatically

[Kevlarhead]

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.

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?

 

[erik.van.geit]

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

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]

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:

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]

HTH. Dave

'add userform
Set Uf = ThisWorkbook.VBProject.VBComponents.Add(3)
'Remove the userform
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=Uf

 

[Kevlarhead]

With a little tweaking, works perfectly. Thank you!

 

[erik.van.geit]

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]

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.

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]

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]

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]

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)