vba interface class-CodePudding

I have an interface-class and a user-form implements that inferface. The cmdOK() sub works only when there ist the second statement there (me.hide) otherwise it doesn't work. What's wrong? Interface:

Public Property Get Description() As String
End Property

Public Property Let Description(ByVal Description As String)
End Property

Public Property Get DialogResult() As VbMsgBoxResult
End Property

Public Property Get Name() As String
End Property

Public Property Let Name(ByVal Name As String)
End Property

Public Sub Show()
End Sub

User-Form

Option Explicit

Implements IFName

Private me_DialogResult As VbMsgBoxResult

Public Property Get IFName_Description() As String
    IFName_Description = txtDescription
End Property

Public Property Let IFName_Description(ByVal Description As String)
    txtDescription = Description
End Property

Public Property Get IFName_DialogResult() As VbMsgBoxResult
    IFName_DialogResult = me_DialogResult
End Property

Public Property Get IFName_Name() As String
    IFName_Name = txtName
End Property

Public Property Let IFName_Name(ByVal Name As String)
    txtName = Name
End Property

Private Sub IFName_Hide()
    Me.Hide
End Sub

Private Sub IFName_Show()
    Me.Show
End Sub

Private Sub cmdCancel_Click()
    me_DialogResult = vbCancel
    Me.Hide
End Sub

Private Sub cmdOK_Click()
    me_DialogResult = vbOK
    Me.Hide
End Sub

Subroutine consuming the code, when I comment "me.hide" in the user-form, it doesn't work:

Public Sub TestDeclared()

Dim sPrompt As String
Dim frm     As IFName


Set frm = New FName
'Set frm = New FNameCbo

frm.Name = "Name property"
frm.Description = "Description..."
frm.Show

If (frm.DialogResult = vbOK) Then
    sPrompt = "User entered " & Chr(34) & frm.Name & Chr(34) & "."
    MsgBox sPrompt
End If

Unload frm
Set frm = Nothing

End Sub

CodePudding user response：

First thing you need to define if you want the form to be modal or not.

If you use the form modal (as you do in your code), the routine TestDeclared will pause until you close the form (hide it). If you omit the hide in the event-routine of the OK-Button, nothing happens because the form stays open.

If you use the form non-modal, the routine TestDeclared will show the form and immediately continue to run (after form-initialize and activate code is done) until it is finished (and if it was called by any other routine, this routine will continue...). This causes, btw, a problem because you destroy the form instance (via Unload) while it is displayed.

If you don't want to close the form, you should create a routine that you call from the event-handler of the OK-button and that does all the cool stuff you want to do. If the result is somehow written into Excel, you should think about using the form non-modal so that the use can switch to the sheet to have a look. If you display the result in your form, stick to using the form modal.

' Show the form
Public Sub TestDeclared()
    Dim frm   As IFName    
    Set frm = New FName

    frm.Name = "Name property"
    frm.Description = "Description..."
    frm.Show
End Sub

(unload is not needed, VBA will do a garbage collection anyhow)

New routine that does the trick when user clicks OK:

Sub DoTheMagic(frm as IFName)
    sPrompt = "User entered " & Chr(34) & frm.Name & Chr(34) & "."
    MsgBox sPrompt
End If

In Code-Behind of your form:

Private Sub cmdCancel_Click()
    Me.Hide ' We are done, leave the form
End Sub

From the OK-Button, you call the new Subroutine. As this routine needs to interact with the form, pass Me as parameter. Alternative could be to declare frm as global variable.

Private Sub cmdOK_Click()
    DoTheMagic Me
End Sub