Procedure call (new) (VBA)

Topic

New Reply

Is it possible to call a procedure in a different user form? I and selecting a button in one form and would like to run a procedure in another form.

Thanks,
Nick

Reply | Quote

Replies

The procedure in the other userform must have been declared as public, not as private. Say that you have a userform frmTest, with a public procedure MyProc. You can call it from another userform as

frmTest.MyProc

or

Call frmTest.MyProc

Note: MyProc can be an event procedure, such as the On Click procedure of a control. Event procedures are Private by default, but you can change them to Public manually.

Reply | Quote

i did as you said, but i am getting an error. it says object required. here is my code:
Private Sub btnInc_Click()
frmCalendar.lblWK38_Click
End Sub

thanks

Reply | Quote

And i did make the procedure public. i forgot to mention that

Reply | Quote

a) Is the other form named frmCalendar ? (Spelling must be exact)
Is the label’s name lblWK38 ? (Idem)
c) Have you made lblWK38_Click public?

Reply | Quote

1. Yes
2. Yes
3. Yes

Reply | Quote

Should work then… In which Office application are you doing this (Word, Excel, …), and which version (97, 2000, …)?

Reply | Quote

It is in Access 2003. I will try to explain the problem better.

I have a main form with procedures on it. When one of the individual forms is up, I have a button the move to the next form. Then next form is also accessible by an on click event of the main form. I want when I hit the next button on the individual forms to move to the next on click procedure.

Thanks again for your help.

Reply | Quote

It’s a pity that you didn’t make it clear that you were using Access at the outset. Forms in Access are quite different from userforms in Word, Excel etc.

I’m afraid I don’t understand your description. What do you mean by “a main form with procedures on it”? Which “individual forms” do you mean? What is “the next on click procedure”?

Reply | Quote

I apologize for not making that clear.
I have a timesheet database that has a yearly calendar as the startup form. Each week has an on_click event to load the correct form. When a user clicks on a week number, a different form is pulled up for that particular week for users to input there weekly hours. If a user wants to go to a different week, they can either return to the calendar form to select a different week number or select the next button on the weekly form. When the next button is selected, I want to call the procedure on the yearly calendar form which pulls up the next weeks form. In this case it will be the next weeks form to input their hours to.
I hope this clears things up.
Thanks

Reply | Quote

Do you mean that you have created a separate form for each week of the year?

Reply | Quote

Yes, I know this was a poor way to design this database, but it was in this shape when i took over working on it.

Reply | Quote

Yuck! One form should be enough. I think it would be worthwhile to redesign the database, it’ll probably save you time in the end. With a single form, it would be easy to move to the next or any other week.

Reply | Quote

I am posting a example of what I am wanting to do and maybe this will help to clarify my problem. When the database loads, you will see two labels on the frmMain. The top label “Test1” will pull up another form, “frmOne”. On frmOne there is a button that when I push it, I want to execute the on_click procedure for the second label on the frmMain. I hope this will help.

P.S. I redesigned the database to use one form instead of 52. The size of the database decreased by almost 75%. Thanks for the advice.

Reply | Quote

Since you now have only one form that is opened, it’s probably easier to write code for the command button on frmOne to move to the next week, instead of trying to call the On Click event procedure for the second label.

Reply | Quote

Would it be possible for me to call code that is wrote in a module? When the button is pushed, it calls a procedure in a module.

Reply | Quote

You can call code from a form module if it is declared as Public, for example

Public Sub lbl2_Click()

instead of

Private Sub lbl2_Click()

You would call it from another module like this:

Call Form_frmMain.lbl2_Click

To call a Public procedure from a standard module (the kind you create by clicking New in the Modules section of the database window), you don’t have to specify the name of the module, just the name of the procedure:

Call MyProc

Reply | Quote

That did the trick. Thanks again!

Reply | Quote

Hi,
Is the called userform in the same workbook as the calling userform?

Reply | Quote