How to close a form that belongs to another file RRS feed

  • Question

  • Hi everybody,

    I have 2 excel file open. Both contain a userform with a button to close the form(s). When I press on that button on one of the two forms, I want both forms to close/hide. 

    When I do this:

    Private Sub CommandButton1_Click()
    Dim Obj As Object
            For Each Obj In VBA.UserForms
            Next Obj
    End Sub

    ...only one form is closed. I guess because the other form belongs to the other file and therefore not in the Userform collection my code is looking at.

    I wonder if there is a way to make this work? Your help is very appreciated!

    • Edited by Arjan Blacktec Monday, October 8, 2012 8:34 PM typo's removed
    Monday, October 8, 2012 8:05 PM

All replies

  • You need to call a routine in the other book to close that book's form. Try something like this

    ' Book1 code
    '    Book1 normal module code
    Public gbFormRunning As Boolean
    Sub ShowForm()
         UserForm1.Show vbModeless
    End Sub
    Sub CloseForm()
         If gbFormRunning Then
                 Unload UserForm1
         End If
    End Sub
    ' Book1 Userform code
    Private Sub UserForm_Initialize()
         Me.Caption = ThisWorkbook.Name
         gbFormRunning = True
    End Sub
    Private Sub UserForm_Terminate()
         gbFormRunning = False
    End Sub
    '' Book2 code
    ' this could be in the button code of a form in Book2
    Application.Run "Book1.xlsm!module1.CloseForm"

    Change Book1.xlsm and to the name of the workbook with the form you want to close, and module1 to the name of the module that contains the CloseForm macro. (Explicitly including the module name is not always necessary)

    Peter Thornton

    Monday, October 8, 2012 9:01 PM
  • Mmmm, I tried to simplify my question and perhaps I forgot to mention one important restriction by doing so: I can only change VBA code in one of the 2 files.

    The whole story:

    I am developing an Excel-VBA-file on the go. Its key feature is a non modal form full of buttons that the user can use to edit the worksheet. That form is opened when the file is opened and can not be closed by the users. Since it is still under developement, there are several versions, used by several users. But when you open the latest version and an older versions, you get 2 forms on your screen and you can use the newest form (with the newest functions) to edit the worksheet in the older file. The question I get from the users if they can have a something to close the older form(s) since they don't use it.

    The challenge is that I don't want to add or change any code to all these old version copies. I only want to add some code to the newest version that can close the forms that are older. I did change the name and caption of the newest form to distinguish the forms, but that will not help as long as I cannot "detect" the older forms.

    Tuesday, October 9, 2012 7:43 AM
  • The only way to "cleanly" unload a userform is with the Unload method called within the project that owns the form (though in turn can be called externally with the Run method or via a reference).

    There are one or two clunky ways that in effect are like pressing the Reset button, definitely not for distribution.

    One way would be to write a routine (ie write code with code) into the other project to close any forms it might be running. The process of "inserting" the routine would cause that project to "Reset" and close any forms. But at least next time you could call the new routine (with the Run method) to cleanly close any forms.

    If you don't want to do that (programmatically write code into the other project), you could refer to the other project, do stuff to it to "reset" it, in turn uncleanly closing any forms and clearing any globals.

    Peter Thornton

    Tuesday, October 9, 2012 10:38 AM
  • Hi Peter,

    I tried to implement the code you suggested in your first answer, but for some reason it does not work.

    The intention of the code I wrote is that the only the form from the currently active workbook remains open and all the others close.

    This is my code:

    Function CloseOperatorPanels() Dim Book As Workbook Dim ThisBookName As String ThisBookName = ActiveWorkbook.Name For Each Book In Excel.Workbooks 'If statement to exclude the form in the active workbook If Book.Name <> ThisBookName Then Bookname = Book.Name Dim MacroInOtherWorkbook As String MacroInOtherWorkbook = "'[" & Bookname & "]!Controll_Options.CloseFrmOperatorPanel'" 'Run the macro in the other workbook that closes its FrmOperatorPanel Excel.Run Macro End If Next Book End Function Public Function CloseFrmOperatorPanel() Unload FrmOperatorPanel End Function

    To test this, I just opened the workbook and a copied version of the same workbook (with obviously a different filename). When I run this function, it does not give any runtime error or whatsoever, but it does not close the form either???

    I hope you can help me one more time...

    Thanks, Arjan

    Saturday, October 13, 2012 8:42 PM
  • Arjan,
    I'm surprised you're not getting any error as square brackets shouldn't
    exist in the Run string. I don't quite follow all of your scenario but maybe
    you might find the following useful.
    As written GetFormCloseProc will return the run string (sWbModProc) of a
    Function named "Function CloseForms()" in the target workbook. If the
    Function is not found it will be inserted in the first normal module of the
    project. In so doing it will 'break' the project thereby resetting any
    variables and closing any running forms (normally you wouldn't want to do
    that). However if found the run string will be returned with which you can
    call Run etc and close any running forms.
    Sub CloseAllOtherProjectsForms()
    Dim bProcExisted As Boolean
    Dim n As Long
    Dim sWbModProc As String
    Dim wb As Workbook
        Set wb = Workbooks("some workbook name.xls")
       GetFormCloseProc wb, sWbModProc, bProcExisted
       If bProcExisted = False And Len(sWbModProc) Then
           n = Application.Run(sWbModProc)
           MsgBox n & " form(s) were running & cleanly unloaded"
       End If
    Debug.Print sWbModProc
    End Sub
    Sub GetFormCloseProc(wb As Workbook, sWbModProc As String, _
                                          bAdded As Boolean)
    Dim bFound As Boolean
    Dim a1 As Long, a2 As Long, a3 As Long, a4 As Long
    Dim nextLine As Long, sCode As String
    Dim sMod As String
    Dim cmp As VBComponent, cmpMod As CodeModule
    Dim vbp As VBProject
        Set vbp = wb.VBProject
       For Each cmp In vbp.VBComponents
           If cmp.Type = vbext_ct_StdModule Then
               If Len(sMod) = 0 Then
                   sMod = cmp.Name
               End If
               bFound = cmp.CodeModule.Find("Function CloseForms()", _
                           a1, a2, a3, a4, False, False)
               If bFound Then
                   sMod = cmp.Name
                   Exit For
               End If
           End If
       If Not bFound And Len(sMod) Then
           Set cmpMod = vbp.VBComponents(sMod).CodeModule
           nextLine = cmpMod.CountOfLines + 1
           sCode = "Function CloseForms() As Long" & vbCr
           sCode = sCode & "Dim i As Long, cnt As Long" & vbCr
           sCode = sCode & "  For i = UserForms.Count To 1 Step -1" & vbCr
           sCode = sCode & "    cnt = cnt + 1" & vbCr
           sCode = sCode & "    Unload UserForms(i - 1)" & vbCr
           sCode = sCode & "  Next" & vbCr
           sCode = sCode & "  CloseForms = cnt" & vbCr
           sCode = sCode & "End Function"
           cmpMod.InsertLines nextLine, sCode
           bAdded = True
       End If
       sWbModProc = "'" & wb.Name & "'!" & sMod & ".CloseForms"
    End Sub
    The inserted procedure should look like this
    Function CloseForms() As Long
    Dim i As Long, cnt As Long
     For i = UserForms.Count To 1 Step -1
       cnt = cnt + 1
       Unload UserForms(i - 1)
     CloseForms = cnt
    End Function
    This is a little different to what I posted previously as it will close all
    running forms, if any (note the index of the first running Userform is 0).
    Security settings will need to Trust access to VBAProject.
    As written GetFormCloseProc will need the reference to Extensibility, if you
    can't be bothered with that change all of As VBComponent, CodeModule,
    VBProject to "As Object"
    For testing run a modeless form in the target workbook, both before first
    time as the code is programmatically inserted and again after. Try stepping
    Whether you insert CloseForms manually or programmatically the eventual Run
    string should look like this -
    The apostrophes are only required depending on certain characters in the
    workbook name, but won't do any harm. Note too the ! and the .
    Peter Thornton
    Sunday, October 14, 2012 11:18 AM
  • In CloseAllOtherProjectsForms the variable bProcExisted is confusingly
    named. I changed the logic of things at the last moment and forgot to rename
    that variable to say bProcInserted (in effect inferring the opposite).
    Peter Thornton
    Sunday, October 14, 2012 3:18 PM
  • Wow! I will need some time to study this...

    However, I do believe my problem is only the method that does not do anything.

    When the macro does not exist (f.e. due to wrong macroname formation) , I get a runtime error 1004. That makes sense.  But when the macro does exist in the other workbook it does not give any error (which sounds good), but does not run that macro either??? Just nothing happens! Looks like I am overlooking something really simple. Can it be Excel 2007 specific (I do  this in excel 97-2003 xls files in excel 2007)?

    I just tried something really simple with 2 files and even this does not work:

    I created Book1.xls and Book2.xls and both contain following code

    Sub Remote()
    ' Keyboard Shortcut: Ctrl+a
        Range("B1").Value = "Text"
    End Sub
    Sub CallOtherFile()
    ' Keyboard Shortcut: Ctrl+b
    Application.Run "'Book1.xls!Module1.Remote'"
    End Sub

    Note that in Book1.xls, I have changed it in  Application.Run "'Book2.xls!Module1.Remote'"

    When I press Ctrl-B, the value in B1 is not changed into "Text" in the other file...? Why not?

    Monday, October 15, 2012 7:22 PM
  • You haven't indicated how you set up the Ctrl+b shortcut. Try either with
    Alt-F8 or better still put the cursor within the CallOtherFile routine and
    step through by repeatedly pressing F8.
    As written though it won't work because you have embraced the entire run
    command with apostrophes. With a name like "Book1.xls" you don't need any
    apostrophes, but if you are going to use them they should only surround only
    the workbook name, eg 'Book1.xls'
    Embracing apostrophes are typically required when names have spaces, dashes,
    and certain other characters. When not sure of the name always include them,
    they do no harm even if not needed but they must be in the right place.
    When used in formulas (eg cells or charts) apostrophes may need to embrace
    the Worksheet name in some situations.
    Peter Thornton
    Tuesday, October 16, 2012 7:53 AM
  • Hi Peter,

    This really helps! 

    I tried some stuff and this is what I learned from your last answer:

    • Using Ctrl-B in both files does not work, it just pickes the same one everytime, regardless of which workbook is active. Running them via Alt-F8 makes it predictable
    • You are right about the apostrophes, I was using them in the wrong way and they you don't get an error, but that does not mean it works.
    • The way I wrote it, the Marco is applied to the active workbook and not the workbook that contains the marco. That also happens in my real application, I predict it will close only the form I want to keep. I will need to change this and that will require some study for me.

    Thank you,


    Tuesday, October 16, 2012 8:42 AM
  • It sounds like you are on the right track. Try the CloseForms() routine I
    suggested previously, either paste it in manually or with the routine that
    writes the code to the project (ie into the project that "owns" the
    potentially running form). Then call CloseForms with (for testing
    step through with F8).
    Peter Thornton
    Tuesday, October 16, 2012 10:56 AM