Announcement

Collapse
No announcement yet.

Test if a specific userform is loaded

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Test if a specific userform is loaded



    Hello,

    I found nothing helpful on the forum (well, there is this post but I would like to know if an alternative solution can be found) so I start my first post.

    I am coding a function AAA which, at a point of time, needs to call an other function BBB to get an intermediate outcome.

    My BBB function ends up by updating the “RowSource” propriety of a ComboBox belonging to the userform (let’s say Form_BBB) from which it is usually called.

    Code:
    Public Sub BBB()
    
    Dim Range_BBB as Range
    
    <Code>
    
    Form_BBB.ComboBox_BBB.RowSource = Range_BBB
    
    End Sub
    My problem is that when my AAA function is calling my BBB function I get an error because the Form_BBB is not loaded. To solve this problem, I would like to test if Form_BBB is loaded before updating the RowSource of my ComboBox_BBB, but without loading the form if it is not (so in can’t use the Form_BBB proprieties).
    It should be something like:

    Code:
    Public Sub BBB()
    
    Dim Range_BBB as Range
    
    <Code>
    
    If  isLoaded Form_BBB then
       Form_BBB.ComboBox_BBB.RowSource = Range_BBB
    End if
    
    End Sub
    Does anyone can help??

    Thanks

  • #2
    Re: Test if a specific userform is loaded

    Try this bit of code to see if the form exists in the UserForm collection:
    Code:
    Function IsUserFormLoaded(ByVal UFName As String) As Boolean
      Dim UForm As Object
    
      IsUserFormLoaded = False
      For Each UForm In VBA.UserForms
        If UForm.Name = UFName Then
          IsUserFormLoaded = True
          Exit For
        End If
      Next
    End Function 'IsUserFormLoaded
    This will not load the Form that you are trying to check.

    You will need to pass the UserForm Name as a string eg,
    If Not IsUserFormLoaded("Form_BBB") Then Load Form_BBB

    Comment


    • #3
      Re: Test if a specific userform is loaded

      Hey!! It works!
      Thanks a lot Gijsmo

      Comment


      • #4
        Re: Test if a specific userform is loaded

        Almost 2 years after your post, this has helped me out a great deal. Thanks!

        Only change I made was to include .Name after the form name when passing to the function (i.e. If IsUserFormLoaded(frmMyForm.Name) Then ... )

        Comment


        • #5
          Re: Test if a specific userform is loaded

          Some things in Excel are timeless....luckily we have forums like these to keep those answers alive

          Comment


          • #6
            Re: Test if a specific userform is loaded

            Hahaha!! Really happy it could help, thanks for the revival!

            Comment


            • #7
              Re: Test if a specific userform is loaded

              Originally posted by gijsmo View Post
              Try this bit of code to see if the form exists in the UserForm collection
              Thanks for gijsmo it help me.
              Take a dare slightly optimize:
              Code:
              '===
              ' gijsmo April 24th, 2011; http://www.ozgrid.com/forum/showthread.php?t=152892
              Function IsUserFormLoaded(ByVal UFName As String) As Boolean
                  Dim UForm As Object
                  For Each UForm In VBA.UserForms
                      IsUserFormLoaded = UForm.Name = UFName
                      If IsUserFormLoaded Then
                          Exit For
                      End If
                  Next
              End Function    'IsUserFormLoaded

              Comment


              • #8
                Re: Test if a specific userform is loaded

                Thanks for this. Most useful.

                Anyone else think it's odd that if you declare the variable to be tested as a userform the "name" argument cannot be invoked without an error, but when declared as an object it works?

                Comment


                • #9
                  Re: Test if a specific userform is loaded

                  Thank you Gijsmo.

                  Not sure if you're still monitoring this thread, but if you are, I just wanted to say thanks and say that it really helped. Being new to VBA and not yet familiar with all the internals, I just knew there had to be a way to see what forms were loaded.

                  My reason for needing this is because I was having a problem with forms initializing when I did and UNLOAD. Digging and experimenting with the code, I found that when you UNLOAD a form that is NOT loaded, it first INITIALIZES the form (and therefore runs any code in the Form_Initialize section) and only then it proceeds to unload it. This quick piece of code is works great!

                  Thanks again!

                  ♫ ♪ Lightning Flash ♪ ♫

                  Comment


                  • #10


                    Re: Test if a specific userform is loaded

                    I'm not quite as active in this forum as I was when I had more time...but, yes, I'm still here!
                    and good to see the forum is continuing to do the job of helping Excel users

                    Comment

                    Working...
                    X