Excel 97 form (Xl 97 )

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote