CODEWITHSUNDEEP
vbaexcel
Jane
Jane

Reputation: 31

VBA Calling a subroutine in one user form from another user form

I am using Excel VBA. I have two user forms: ClientInfo and ClientSearch

In ClientSearch, I search through an excel worksheet to list all clients with the same last name. From there, I pick the client I want (by highlighting the name in the list and clicking on command button cmdOpenClientInfo) and determine their ClientID (which is also in the worksheet). I then want to pass this ClientID to the form ClientInfo in order to populate all text boxes on this form with the relevant data from the worksheet:

Coded in the ClientSearch form:

Private Sub cmdOpenClientInfo_Click()

 Dim ClientID As Integer

 ClientID = textSrchClientID.value

'user msgbox to check to make sure I get the correct ClientID ... and I do

 msgbox(ClientID)

 Me.Hide

 frmClientInfo.show

Call frmClientInfo.PopulateClientInfo(ClientID)  'this is where it fails

End Sub

Coded in the ClientInfo form:

Sub PopulateClientInfo(ClientID As Integer)

  'this is where I would populate the text field

End Sub

The routine always gets stuck at the CALL frmClientInfo.PopulateClientInfo(ClientID)

In the case above, I get Run-time error '424' Object Required.

I have tried various solutions presented in this forum, but have not found a solution.

Upvotes: 3

Views: 6907

Answers (3)

Ferry
Ferry

Reputation: 1

Call Forms(« myForm »).mySub

« mySub » must be a Public sub in « myForm »

Upvotes: 0

Variatus
Variatus

Reputation: 14373

You can't call a procedure in a form module from outside that module. Try this code (sorry, I didn't).

Private Sub cmdOpenClientInfo_Click()

    Dim ClientID As Integer
    Dim FrmInfo As frmClientInfo

    ClientID = textSrchClientID.Value

    'user msgbox to check to make sure I get the correct ClientID ... and I do

    MsgBox (ClientID)

    Me.Hide

    Set FrmInfo = New frmClientInfo
    With FrmInfo
        .Tag = ClientID
        .Show

        ' make this call in the FrmInfo Activate event procedure
        ' PopulateClientInfo(cint(Me.Tag)
    End With
    Unload FrmInfo
    Set FrmInfo = Nothing
End Sub

I presume that you have a form which is named frmClientInfo. You can create an instance of that form with the command Set FrmInfo = New frmClientInfo. This object will not show until the Show method is invoked but you gain access to all its controls. To pass a variable to that form you can address any of them. Perhaps you have a Tbx which should show the ClientID. You can access that Tbx and set its value. The above code assigns the ClientID to the Tag property of the form itself.

The form's Activate event will occur when the Show method is invoked. That would be the moment to run the PopulateClientInfo procedure (from within the frmClientInfo module, of course), retrieving the ClientId from the Tag property.

Bear in mind that the code will continue running in the cmdOpenClientInfo_Click procedure when the ClientInfo form is closed. So, that is the time to remove that form from memory, on the one hand. On the other, the 'FrmInfo' object still exists and you could pick any information from it that you might want to use in the form which made the call. The syntax is very simple, like, FrmInfo.Textbox1.Value.

Upvotes: 1

A.S.H
A.S.H

Reputation: 29332

Your call to frmClientInfo.show is in Modal mode, so the next statement wont execute until the new form closes. You can try to make the call non-modal:

frmClientInfo.show Modal:=False

But this may be source of other problems. Better keep working in modal mode but pass the ClientID parameter to the form before it shows up.

1- Modify your PopulateClientInfo method of frmClientInfo like this:

Public Sub PopulateClientInfo(ClientID As Integer)
    '....
    'populate The fields, then:
    '....
    Me.Show ' <-- form shows itself after populating its fields
End Sub

2- Remove the call to frmClientInfo.show in the ClientSearch form.

Upvotes: 1

Related Questions