Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 97 form (Xl 97 )

    The following code is in a form

    Private strUN As String

    Private Sub cmdOk_Click()
    strUN = txtUserName.Text
    Me.Hide
    End Sub


    Public Property Get UserName() As String
    UserName = strUN
    End Property

    --------------------------------
    From a module I declare
    dim m_frmLogin as new frmLogin

    Sub SomeSub()
    m_frmLogin.show
    end sub

    Sub OtherSub()
    debug.print frmLogin.UserName
    'displays nothing, empty string
    end sub
    ------------------

    Can I conclude that The form is not truely hidden, but destroyed?

    Thanks.

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

    Re: Excel 97 form (Xl 97 )

    You use m_frmLogin to show the form, so you must use that to display the value too: Debug.Print m_frmLogin.UserName.

    Or don't use m_frmLogin at all:

    Sub SomeSub()
    frmLogin.show
    End Sub

    Sub OtherSub()
    Debug.Print frmLogin.UserName
    End Sub

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

    Re: Excel 97 form (Xl 97 )

    I would prefer to program this as follows:

    dim m_frmLogin as frmLogin 'Note i removed the New keyword

    Sub SomeSub()
    If m_frmLogin is nothing then
    Set m_frmLogin = New frmLogin
    End If
    m_frmLogin.Show
    End Sub

    Sub OtherSub()
    msgbox m_frmLogin.Username
    End Sub

    Sub GetRidOfForm()
    Unload m_frmLogin
    Set m_frmLogin=Noting
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 form (Xl 97 )

    There was a typo in my post. Originally simply used frmLogin, but my property never returned a value. I then changed the code to use m_frmLogin.

    The bottom line is, the propery is not available. In VB I can hide a form and continue to access it. Apparently in VBA the form is destroyed and not hidden

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 form (Xl 97 )

    Thanks for the reply.

    However, I do not want to destroy the form, I simply want to hide it. I want to treat it like a class and give it some properties, and contiue to access the properties. I will destry it when the spreadsheet closes.

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

    Re: Excel 97 form (Xl 97 )

    In the attached zipped workbook, the property is available after the userform has been hidden - at least, it is in Excel 2002; I don't have Excel 97 to test with at the moment.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 form (Xl 97 )

    Thanks. The sample you sent does work in Excel 97 as well.

    However the following does not

    Sub Test()
    frmLogin.Show
    frmLogin.hide
    End Sub

    Sub SecondRoutine()
    MsgBox "The Login Name is '" & frmLogin.UserName & "'"
    End Sub

    I will move forward by creating a class - calling the form from the class. The class will not destroy itself, as long as I program in such a way that it does not go out of scope. When I am done with it, I can then set it to nothing.

    Thanks for your help

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

    Re: Excel 97 form (Xl 97 )

    I don't understand what is happening. See modified zip file.

    In this example, you can click the first button to prompt for a user name. You can click the second button later to retrieve it. If you click the second button again, the user name will be empty, because the form has been unloaded. If you remove "Unload frmLogin" from the Test2 macro, you can click the second button repeatedly to see the user name.

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

    Re: Excel 97 form (Xl 97 )

    This code:

    Option Explicit

    Sub Test()
    frmLogin.Show
    End Sub
    Sub test2()
    MsgBox "The Login Name is '" & frmLogin.UserName & "'"
    End Sub

    Works fine in XL2000, does not work in XL97.

    However this code:

    Option Explicit

    Sub Test()
    frmLogin.Show
    End Sub
    Sub test2()
    Test
    MsgBox "The Login Name is '" & frmLogin.UserName & "'"
    End Sub

    Works in both XL97 and XL2000.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 form (Xl 97 )

    Very interesting.

    Jan Karel Pieterse noted that one method works in 2000 but not 97. I just verified on XP. My process must work on both, therefore I was developing in the older version. Glad I am. I saved hours of frustration finding it early.

    Thanks again.

Posting Permissions

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