Code - Loop through userforms and upgrade.

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Guys,

I have about 75 copies of a workbook out in the field. I have a fix that needs to be applied to these. The fix involves replacing 20 user forms with new ones.

So, I have created a new workbook that contains the new ones, along with a dialogue box for the users to select the book to upgrade. (str_PWB)

What I now need to do is loop thru each of the 20 userforms in the original file (str_PWB) , delete them, then copy the new form out of the upgrade workbook (str_UPWB)

There are other userforms that I dont want to touch, I only need to upgrade the defined list of 20 forms.

Thanks
Bob
 

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.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Bob,

How about:
Code:
Sub Test()
    Dim varUFArray As Variant, lCnt As Long
    Dim wbkData As Workbook
    
    Set wbkData = Workbooks("deletemetoo.xls")
    varUFArray = Array("UserForm1", "UserForm3", "UserForm6", "UserForm7")
    
    For lCnt = LBound(varUFArray) To UBound(varUFArray)
        Call DeleteComp(wbkData, varUFArray(lCnt))
    Next lCnt
    
End Sub

Sub DeleteComp(wbk As Workbook, ByVal strCompName As String)
    Dim VBComp As VBComponent
    'set a reference to the VBA Ext lib
    
    On Error Resume Next
    Set VBComp = wbk.VBProject.VBComponents(strCompName)
    On Error GoTo 0
    If Not VBComp Is Nothing Then
        wbk.VBProject.VBComponents.Remove VBComp
    End If
    
End Sub
Just amend the names in the array to suit.

HTH
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Thanks Richie,

Doesn't that just delete the the userfrms ? It doesn't acually copy the new versions back from the current workbook.

I've got this far . . (EDIT - I've decided to just delete ALL forms, then copy them all back in)

Code:
Public Sub upgrade_forms(PWB As String, UGWB As Workbook)
Application.ScreenUpdating = False
Windows(PWB).Activate
Set oVBComps = ActiveWorkbook.VBProject.VBComponents

For Each oVBComp In oVBComps
    If oVBComp.Type = 3 Then
    oVBComps.Remove oVBComp
    End If
Next oVBComp

MsgBox ("All old forms deleted")

That wipes out all the userforms from PWB, I then need to copy all of the forms from the current workbook back into PWB. . .

This is where I'm strugglin . . this code is total scribble at the moment, and I'm getting more confused.

Code:
Dim FName As String
'Dim VBComp As VBIDE.VBComponent

UGWB.Activate
    FName = "C:\code.txt"
    If Dir(FName) <> "" Then
        Kill FName
    End If
    For Each VBComp In .
        If VBComps.Type <> 3 Then
           VBComps.Export FName
           Workbooks(PWB).VBProject.VBComponents.Import FName
           Kill FName
        End If
    Next VBComps
End With

Thanks
Bob
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Bob,

Yes, it does just delete the userforms - I thought that was the part that you wanted help with. ;)

OK, extending on the original I'd go with something like this:
Code:
Sub Test()
    Dim varUFArray As Variant, lCnt As Long
    Dim wbkData As Workbook
    
    Set wbkData = Workbooks("deletemetoo.xls")
    varUFArray = Array("UserForm1", "UserForm3", "UserForm6", "UserForm7")
    
    For lCnt = LBound(varUFArray) To UBound(varUFArray)
        Call DeleteComp(wbkData, varUFArray(lCnt))
        Call ExportImport(ThisWorkbook, wbkData, varUFArray(lCnt))
    Next lCnt
    
End Sub

Sub DeleteComp(wbk As Workbook, ByVal strCompName As String)
    Dim VBComp As VBComponent
    'set a reference to the VBA Ext lib
    
    On Error Resume Next
    Set VBComp = wbk.VBProject.VBComponents(strCompName)
    On Error GoTo 0
    If Not VBComp Is Nothing Then
        wbk.VBProject.VBComponents.Remove VBComp
    End If
    
End Sub

Sub ExportImport(wbkSource As Workbook, wbkTarget As Workbook, ByVal strCompName As String)
    Dim VBComp As VBComponent
    'set a reference to the VBA Ext lib
    Const strFName As String = "C:\tempcomp"
    
    On Error Resume Next
    Set VBComp = wbkSource.VBProject.VBComponents(strCompName)
    On Error GoTo 0
    If Not VBComp Is Nothing Then
        VBComp.Export FileName:=strFName
        wbkTarget.VBProject.VBComponents.Import FileName:=strFName
        Kill PathName:=strFName
    End If

End Sub
I like the modular approach, especially when it comes to debugging, but you can combine it into one routine if you wish. Note that the same array of names is used for each routine ie I am assuming that you replace UserForm1 with UserForm1. Amend to suit.

HTH
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079

ADVERTISEMENT

Richie,

Thanks for the response again. I also prefer the modular approach, but this is really bugging me and I can't understand why my method isn't working !

I'm getting runtime error - Object doesn't support prop or method - on the line marked with ***

Any ideas ?

Code:
Public Sub upgrade_forms(PWB As String, UGWB As Workbook)
Application.ScreenUpdating = False
Windows(PWB).Activate
Set oVBComps = ActiveWorkbook.VBProject.VBComponents

For Each oVBComp In oVBComps
    If oVBComp.Type = 3 Then
    oVBComps.Remove oVBComp
    End If
Next oVBComp

MsgBox ("All old forms deleted")

UGWB.Activate
Set oVBComps = ActiveWorkbook.VBProject.VBComponents
Const strFName As String = "C:\tempcomp"
For Each oVBComp In oVBComps
        *** oVBComps.Export Filename:=strFName
        Workbooks(PWB).VBProject.VBComponents.Import Filename:=strFName
        Kill PathName:=strFName
Next oVBComp
Application.ScreenUpdating = True

End Sub
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Bob,

Looks like a typo - you are using oVBComps instead of oVBComp (no 's').

HTH

PS: Just noticed one of your earlier queries also posted at VBAX. If you are going to cross-post your questions please provide links to the other sites used. Thanks.
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Richie(UK) said:
PS: Just noticed one of your earlier queries also posted at VBAX. If you are going to cross-post your questions please provide links to the other sites used. Thanks.

Good point, well put. I should know that by now.

Thanks
bob
 

Forum statistics

Threads
1,148,273
Messages
5,745,794
Members
423,973
Latest member
man_this_is_hard

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