MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

accessing user form data from other modules

  Asked By: Joel    Date: Jan 18    Category: MS Office    Views: 1663

I just started playing with user forms, and I can't figure out how to
get data entered into my form back out to other code modules in my
project. For some reason, functions and module level variables I
declare as public in my user form's code module are not visible to
the regular code modules. To be specific, I'm trying to build a
generic "Column Selection" user form that can be pulled up by any
macros I write that require a user to select a column. Although I
know how to write the three lines of code that will display the form,
I have no idea how to return the address the user enters to the
calling program. Any suggestions?



7 Answers Found

Answer #1    Answered By: Alberta Miller     Answered On: Jan 18

You need to explore the ByVal and the ByRef methods of passing
information to your Form. Check out this page for help with Arguments
in your functions.


Answer #2    Answered By: Debbie Reyes     Answered On: Jan 18

I think I already have a pretty firm grasp of ByVal and ByRef, but I
checked out the article anyway. I don't think it addresses my

In short, here's what happens:
Let's say I write  a Public function in the code  module of my user
form that returns the value of a text box in that form. If I write a
function in a different module  in the Modules folder of the project
and then try to call that text box return  function, it says that the
function is undefined. No mention of trouble with arguments.

Any more ideas?

Answer #3    Answered By: Leroy Schmidt     Answered On: Jan 18

I usually pass values in Access, but Excel should be very similar:

How I call the Function:

'goes and gets the Attorney Signature, based on the Short Case Caption
strFSAttorneySig = FSAttorneySig(strtxtShortCaseCaption)

'My function which will return  a value to my calling location for me to
Public Function FSAttorneySig(strShortCaseCaption As String) As String

.... Code steps - loops through my record set gathering all pertinent
attorney signature information ....

'passes the value back  to the calling location
FSAttorneySig = strFSAttorneySig
End Function

In my example, I am using the strtxtShortCaseCaption to narrow the
results of my call so, I am passing information into my Public Function
as well as passing information back out of my function to the original

Answer #4    Answered By: Edwin Chavez     Answered On: Jan 18

That makes perfect sense to me. The problem is, when I try to do
something like that, I get an error to the effect that FSAttorneySig
is not defined, as if the form's code  module is not accessible for
some reason. Maybe there's a setting I need to change somewhere.

Answer #5    Answered By: Burk Martin     Answered On: Jan 18

Try changing your module  to include "Option Explicit" as the very first
line of code. This forces early binding to help you ensure everything
is appropriately declared.

Then run Debug.

I suspect that what is not declared is the argument you are passing.
For instance strtxtShortCaseCaption is dimmed as a string in the
originating procedure. I also have strFSAttorneySig dimmed as a string
in the secondary module.

If that still does not work for you, why don't you pass your code  into
your email so we can all take a look at what you are doing? Your
problem may then become apparent.

Answer #6    Answered By: Hubert Taylor     Answered On: Jan 18

I'll put some code  below. I always use Option Explicit,
and since I'm just trying to get a basic setup, I'm not passing
arguments yet. Anyway, I cut everything out of my code for the sake
of trouble shooting--here's my code and the error it generates:

In the Forms folder I have a form  titled ColumnSelection. Here is
its code module:
Option Explicit

Public Sub Hope()
MsgBox "Sweet, you rule!", vbOKOnly
End Sub

Then, in the modules  folder I have a module  titled InputFunctions:
Option Explicit

Public Sub Test()
Call Hope
End Sub

When I run Test, the line that reads "Call Hope" is highlighted and
the error reads:
Complie error:
Sub or Function not defined

As an experiment, I created a new module, Module1, in the modules
folder and copied the code for Hope into it. Then running Test
produces the desired message box. So for some reason, it appears
that code in the Modules folder doesn't have access to code in the
Forms folder.

Answer #7    Answered By: Lurlene Fischer     Answered On: Jan 18

I missed something so obvious:
I didn't realize that code  in the Modules folder could access the
properties of the user  form directly. For example, I thought
ColumnSelection.CommandButton1.Caption would be undefined in a
Modules folder program.

Didn't find what you were looking for? Find more on accessing user form data from other modules Or get search suggestion and latest updates.