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

Question

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.

A.S.H · Accepted Answer · 2017-04-21 21:57:21Z

1

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.

answered Apr 21, 2017 at 21:57

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jane Over a year ago

I tried your suggestion, but unfortunately, I still get the same '424' error.

A.S.H Over a year ago

@Jane are you using Option Explicit? If not, I am sure we will have better info about the error if you set it.

Variatus · Accepted Answer · 2017-04-22 03:46:46Z

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.

Ferry · Accepted Answer · 2023-12-12 17:08:56Z

0

Call Forms(« myForm »).mySub

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

answered Dec 12, 2023 at 17:08

Ferry

1

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related