Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Is UserForm Loaded? (Office 2K)

    Is it possible to tell if a UserForm has been loaded? I have a modeless userform of information about a sheet. Whenever the user leaves this sheet, I hide it. Whenever the user returns, I unhide it, but the first time the user visits the sheet, I have some initialization to do. I use a global variable now, but wondered if it was possible to use a property of the userform instead. Something like:
    <pre>If frmInfo.IsLoaded then
    frmInfo.Show
    Else
    frmInfo.Show
    frmInfo.Left = 300
    End If</pre>


    TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is UserForm Loaded? (Office 2K)

    You can use the UserForm_Initialize routine to handle ... initialization. This event fires when the UserForm is loaded, but not when it's unhidden after having been hidden.

    Otherwise, you might use the Tag property - leave it empty in design mode, set it to something the first time you open the form, later check if its non-empty.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Is UserForm Loaded? (Office 2K)

    You might be able to use the ActiveControl property with a function as follows : <pre> Function IsFormLoaded(frm As UserForm) As Boolean
    If frm.ActiveControl Is Nothing Then
    IsFormLoaded = False
    Else
    IsFormLoaded = True
    End If
    End Function</pre>

    This assumes that would always be an active control on the form.

    Andrew C

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is UserForm Loaded? (Office 2K)

    I concur with Hans, use the Initialize event to setup the form.

    If you use a custom property for the form, it will load the form to retrieve the value of the custom property if the form is not open. Which will fire the initialize event anyway, so either way, the initialize event is probably your best bet, unless you have a specific reason not to, which you haven't told us <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is UserForm Loaded? (Office 2K)

    Just trying to avoid another code module. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> The only thing that I wanted to initialize was the startup position and it turns out that the Show method resets the position back to your design, so I had to save the Top & Left which made the init trivial: if they are zero, then I do the init. Thanks for the ideas, just wanted to make sure I wasn't missing something. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is UserForm Loaded? (Office 2K)

    <P ID="edit" class=small>(Edited by carbonnb on 15-Jan-03 16:00. FInally posted after 3 crashed of IE!!)</P>Sam,

    If you set the StartUpPosition of the form to 0 - Manual the form will unhide in the same position that it was in when you hid it.

    As well, if you set the Top and Left properties of the form, you can open the form where you want with no code.

    An added benefit is that if the user move the form out of their way, it will unhide where they left it.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is UserForm Loaded? (Office 2K)

    Thanks, Bryan!

    An unrelated question: Am I correct in saying that you can tell if a VBA project is password protected in VBA, but you cannot protect/unprotect the project in code?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Is UserForm Loaded? (Office 2K)

    Sam,

    Yes, that's correct.

    Gary

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is UserForm Loaded? (Office 2K)

    Just to generalise, since ou already found a solution to your problem:

    You can determine whether a project has any loaded userforms by testing for
    Userforms.Count=0
    But this only works for the "Thisworkbook" object (in XL), so only for the project the code resides in.

    To determine whether any project has any loaded forms, I use this code:

    ' counts the number of loaded userforms in oWorkbook
    Function FindUFs(oWorkbook As Workbook)
    Dim vbc As VBComponent
    Dim lUFCount As Long
    Dim oTemp As Object
    On Error GoTo Handler
    If oWorkbook.VBProject.Protection = vbext_pp_none Then
    For Each vbc In oWorkbook.VBProject.VBComponents
    If vbc.Type = vbext_ct_MSForm Then '3
    Set oTemp = vbc.Designer
    If oTemp Is Nothing Then
    lUFCount = lUFCount + 1
    End If
    End If
    Next vbc
    Else
    lUFCount = Application.Run("'" & oWorkbook.FullName & "'!CountUFs")
    End If
    On Error GoTo 0
    FindUFs = lUFCount
    Exit Function
    Handler:
    FindUFs = 1
    End Function

    But when a VBAProject is password protected, one cannot access the "Designer" object in it, so I used a trick:

    I include this function in that project:

    Function CountUFs()
    CountUFs = UserForms.Count
    End Function

    And use a call to that function to get the number of loaded forms.

    Of course the FindUFs function needs a reference to the VBA extensibility library.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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