Catch Name of Closed UserForm

[anderb16]

There I was... knee deep in open Chrome tabs with more VBA code than I could handle... I'd been lost out there for hours with no solution in sight... nothing but hours old coffee to quench my thirst... I tell myself "it's okay to ask for help... someone probably knows how to do this." "Never! Don't give in," I say! "I can find the answer! The internet is full of answers, I just have to find the right one!"

Alright, after nearly falling asleep at my computer last night having only gone backward on my project, I decide to give in and see if I can get a little help... Scenario:

I'm here temporarily with this organization, and the first task they have for me is to transcribe data from fillable pdf forms over to a spreadsheet. There're a lot of them and there're a lot of fields in each with a lot of data that could easily be mistyped. I tell myself, "Hey, I'm positive I can get excel to do this for me." I could increase productivity here significantly with this. That was the easy part. The hard part is that I'm only here for a short time and I need to make this tool easy for everyone else in my workplace to use.

Okay, there're a few fields that the transcriber has to reference in each form when transcribing the data in case they need to add a note, so I set up a Sub that opens each pdf in new UserForm with a WebBrowser. That works fine. They are opened modelessly so the transcriber can reference them during the data import routine and add notes as necessary.

What I'm not finding:

Since the UserForms are created at runtime and they are opened modelessly, I can't add Subs to the UserForms, otherwise they close as soon as they're opened. Now when the transcriber uses the tool it'll create dozens of new forms fine, but they're never removed. If I leave it this way, there'll be thousands of UserForms left in the project in no time. I found one solution that will allow me to insert a Sub into the UserForm using Me.Show Me.Hide Me.Show, but that's not elegant at all. I thought I could use QueryClose to solve this, but once again, I can't insert it into the UserForm.

How I think I can solve this:

Is there a way I can catch when UserForms are closed and remove them from the project? I'm open to other ideas as well.

Included spreadsheet (Warning: May need to lower trust settings to make it work.):
https://nextcloud.anderbak.net/index.php/s/cSsWtmD5rPJWH5E

To use, click the "Button" on the right. Use "Browse" to set the directory containing a pdf. Select a pdf from the ListBox and hit "Preview"

The project is far from finished.

Thanks, I'll be checking this later today.

 

[Yongle]

an alternative approach...
- create an array "Retain" of the UserForms that should be retained
- loop through all UserForms deleting all UserForms not included in Retain
- call the macro either when workbook is saved or opened

The VBA below loops through all userforms and deletes every one except UserForm1 and UserForm3
- (during testing) results are printed to the immediate window and userform deletion is disabled (by being commented out)
- after successful testing remove the apostrophe
(To view the immediate window, use shortcut {CTRL} G when in VBA window)

Place code in standard module

Option Explicit
[COLOR=#ff0000][I]'set a reference to the VBA 5.3 Extensibility Library (go to VBA Tools menu, choose References, and check "Microsoft Visual Basic For Applications Extensibility Library 5.3")[/I][/COLOR]
Sub DeleteForm()
    Dim VBComps As VBIDE.VBComponents, VBComp As VBIDE.VBComponent
    Dim Retain() As Variant, itm As Variant
    Dim a As Long
    Set VBComps = ThisWorkbook.VBProject.VBComponents
    Retain = Array("UserForm1", "UserForm3")
    For Each VBComp In VBComps
        If VBComp.Type = 3 Then
            a = -1
            For Each itm In Retain
                a = a + 1
                If VBComp.Name = itm Then
                    Debug.Print "retain..", VBComp.Name
                    Exit For
                Else
                    If a = UBound(Retain) Then
                        Debug.Print "delete..", VBComp.Name
                        [COLOR=#000080]'VBComps.Remove VBComp[/COLOR]
                    End If
                 End If
            Next itm
         End If
    Next VBComp
End Sub

 

[Yongle]

Rather than use an array for all retained userforms, you could instead give them a common feature (eg common non-default border colour) and delete all forms that do not have that feature.

You can then use this:

Sub DeleteForm2()

    Dim VBComps As VBIDE.VBComponents, VBComp As VBIDE.VBComponent
    Set VBComps = ThisWorkbook.VBProject.VBComponents
    For Each VBComp In VBComps
        If VBComp.Type = 3 Then
            If VBComp.Properties(3).Value <> [COLOR=#ff0000]16711680[/COLOR] Then VBComps.Remove VBComp
        End If
    Next VBComp

End Sub

Change the colour of the border and determine colour value with:

        For Each VBComp In VBComps
            If VBComp.Type = 3 Then Debug.Print VBComp.Name, VBComp.Properties(3).Value
        Next VBComp

 

[anderb16]

Thanks Yongle! I'm going with Option 2, changing the border colors simply because of the ease of implementation. If I have issues, I'll bring it up here.