Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Test if a specific userform is loaded

  1. #1
    Join Date
    24th April 2011
    Posts
    3

    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.

    VB:
    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:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    5th March 2011
    Location
    Australia
    Posts
    105

    Re: Test if a specific userform is loaded

    Try this bit of code to see if the form exists in the UserForm collection:
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th April 2011
    Posts
    3

    Re: Test if a specific userform is loaded

    Hey!! It works!
    Thanks a lot Gijsmo

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd April 2013
    Posts
    1

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    5th March 2011
    Location
    Australia
    Posts
    105

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th April 2011
    Posts
    3

    Re: Test if a specific userform is loaded

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    20th February 2012
    Posts
    2

    Re: Test if a specific userform is loaded

    Quote 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:
    VB:
     '===
     ' 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
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    25th October 2013
    Posts
    1

    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?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 4 users browsing this thread. (0 members and 4 guests)

Possible Answers

  1. Test if specific values exist in two cells
    By erickiml in forum EXCEL HELP
    Replies: 4
    Last Post: January 6th, 2011, 20:50
  2. Macro to Test if Windows Application Is Loaded
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: April 27th, 2008, 21:13
  3. Check If Userform Loaded In Memory
    By postman2000 in forum EXCEL HELP
    Replies: 1
    Last Post: February 17th, 2007, 05:08
  4. Determine if a userform is loaded
    By JJacob in forum EXCEL HELP
    Replies: 2
    Last Post: April 24th, 2006, 17:30

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno