MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with calling Sub from UserForm

  Asked By: Craig    Date: Sep 21    Category: MS Office    Views: 1953

What i need to do is have a userform where the end user can choose a
number of options from comboboxes, these are then assigned to global
variables and used as filters in a subsequent section of the code.

I've designed and written the code for the userform and when i step
through it everything works as planned; the comboboxes are populate
depending on various previous choices, the global variable get
filled and things are all hunky-dory.Similarly, the other project
code that does all the filtering and transformations works fine when
i step through it.

When i try and link the two together things go a bit wrong and when
it comes to populating the comboboxes i'm getting the Run Time Error
1004 : Method 'Range' of object '_Global' failed (this was one of
the issues that was sorted in earlier posts, so i don't understand
why it should suddenly become a problem simply by calling the Sub
from a form rather than running direct from the code). Seeing as
both sets of code work when stand-alone, what is it that i've
overlooked when trying to get them running sequentially using the
following code:

Sub DisplayFormThenProcess
Call ProcessData
End Sub

If it's any help, the Global variables, form code and data tramsform
code are all in different modules.

Looking a bit further ahead, the aim to to get all this code into an
add-in that can be distributed to a number of users. What sort of
things should i be looking to do in order to make this as painless
as possible?



3 Answers Found

Answer #1    Answered By: Abney Martin     Answered On: Sep 21

From the little bit  of code  you've shown, I'd suspect that you have a modal

When you do the Show, processing of the subroutine will stall at that point
until the form  is closed again.

If your call to ProcessData needs the form to be open, then it won't be at the
time ProcessData is called.

BTW please do not type your code into your message - it could well be different
to the actual code. Use copy/paste to paste the actual code into your message.

Answer #2    Answered By: Fannie Gonzales     Answered On: Sep 21

The ProcessData Sub doesn't require the form  to be open otherwise
the code  wouldn't work  when i step through it in isolation, yes?
Anyhow, it falls over before it gets to the ProcessData call. Would
simply changing the forms ShowModal property to False correct this?

The issue is with setting the rowsource for a combobox. The userform
contains a MultiPage control with four pages. The first page has
four OptionButtons and a CommandButton. Depending on which
OptionButton is selected, the RowSource of a combobox on the second
page is set to one of a number  of Named Ranges. This assignment is
handled by the code behind the CommandButton, which (Cut&Pasted!) is:

Private Sub cmdNext1_Click()
If Me.opt1 = True Then
Me.cboA.RowSource = Range("One").Address
End If
If Me.opt2 = True Then
Me.cboA.RowSource = Range("Two").Address
End If
If Me.opt3 = True Then
Me.cboA.RowSource = Range("Three").Address
End If
If Me.opt4 = True Then
Me.cboA.RowSource = Range("Four").Address
End If
Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End Sub

However, when the form is displayed using the frmCaptureInfo.show
the assignment fails with the Run Time Error 1004 : Method 'Range'
of object  '_Global' failed. Hovering the mouse over the .Address
section doesn't show the range  of the Named Ranges as I expect.
Doing this when I step through the code manually displays the cell
references of the assigned named range.

Does that help  any?

Answer #3    Answered By: Mason Evans     Answered On: Sep 21

When you say that you are stepping though the code  in isolation and stepping
through the code manually, what exactly do you mean? Are you talking about
setting a breakpoint and then stepping the code when the breakpoint triggers?
Or are you running  this code manually rather than triggering it in the normal

Seeing you are getting a range  error, you need to have a look at your Range

Range isn't a function - it's a method  of an appropriate object. Although it
will sometimes make assumptions over which object  it is to associate with, there
are times when it'll make the wrong  assumptions. You're talking about named
ranges here, so each Range call should be qualified to the owner of the named
range (either the application or a worksheet, from memory - check the "applies
to" in the help). This is especially important inside an event, where it may
not know what it should associate the Range method with.

Re modal ... I was actually thinking the reverse. I was wondering if your form
needed ProcessData to run  before the user  starts clicking on the form. In that
case, you either need to kill the modal, or better to put the required code into
the form's initialisation. If it should only run after the user closes the
form, then the form  should stay modal.

As for whether the form needs to be open ... it depends what your later code
does, but also depends on how you close the form. If you just hide it, then it
stays around and you can use its fields. If you unload it, then it's gone and
any further reference to it will either fail or will create a brand new instance
of it - neither being desirable.

However, explicitly qualify all your Range calls - I have a suspicion that this
is where you are having problems.

Didn't find what you were looking for? Find more on Problem with calling Sub from UserForm Or get search suggestion and latest updates.