Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Help:Can I access a userform from another workbook?

3 views
Skip to first unread message

Thomas Ogilvy

unread,
Mar 15, 1999, 3:00:00 AM3/15/99
to
I don't believe you can do this. You need to export the userform and import
it into the new workbook. I believe this is the designed behavior - someone
may have discovered a workaround.

Regards,
Tom Ogilvy

ztx wrote in message ...
>Hi
>I have some userform in workbook1,and I want to reuse it at a new
workbook,I
>add a refrence of workbook1 in newworkbook,then call userform1.show,but I
>get a run time error "object required",what the matter,can I access a
>userform from another workbook?
>Thanks!
>z...@usa.net
>
>
>

ztx

unread,
Mar 16, 1999, 3:00:00 AM3/16/99
to

Keith Willshaw

unread,
Mar 16, 1999, 3:00:00 AM3/16/99
to

ztx wrote in message ...

You cant do this directly. What you need to do is create a Public procedure
which issues the Show Form command. This procedure CAN be
called from a remote workbook

You need to set a reference to the other workbook and then use a
fully qualified name

So if in Book1 you want to show a Form in Book2

Set the reference from Book1 to Book2

In Book2
Make sure the VBA project has a unique name say P1
Rename the ThisWorkbook Object
Add a Module - Say m1

In Mod 1 add a Public Sub


Public Sub test()
UserForm1.Show
End Sub


From Book1 you cn now show your form by calling
the Sub in Book2

P1.m1.test

This will show the form

Keith

Tushar Mehta

unread,
Mar 16, 1999, 3:00:00 AM3/16/99
to
[This followup was posted to microsoft.public.excel.programming with copies to
Thomas Ogilvy <twog...@email.msn.com> and ztx <z...@usa.net>.]

Thomas Ogilvy's right on both counts. First, the intended design is for one to
export/import the form. Second, the workaround is not difficult - with the
help of the workbook that contains the form. In a quick test I did the foll.:

Book1.xls contains a userform (userform1) with a button (commandbutton1) with
an event procedure, commandbutton1_click:

Private Sub CommandButton1_Click()
MsgBox "button clicked"
Me.Hide
End Sub

The WB also contains a standard module with one procedure:
Sub showform()
UserForm1.Show
End Sub

Book2 contains a standard module withthe test procedure:
Sub testBook1()
book1.Module1.showform
End Sub

After saving Book1.xls and establishing a reference from Book2 to Book1,
running testBook1 results in userform1 popping up. Clicking on the sole button
run the correct event proc.

What are the consequences of this mumbo-jumbo? I have no idea, but I can
speculate. First, unless the designer(s) of Book1 thought of - and
accomodated - this type of access, the side-effects could, potentially, be
disastrous. Second, this sneaks around an intended VBA feature. If MS decides
to block this "ability" those exploiting it are on their own.

--
Regards,

Tushar Mehta
--
In article <O6myyN2b#GA....@cppssbbsa02.microsoft.com>, Thomas Ogilvy
(twog...@email.msn.com) says...


> I don't believe you can do this. You need to export the userform and import
> it into the new workbook. I believe this is the designed behavior - someone
> may have discovered a workaround.
>
> Regards,
> Tom Ogilvy
>

Keith Willshaw

unread,
Mar 16, 1999, 3:00:00 AM3/16/99
to

Tushar Mehta wrote in message ...

>
>What are the consequences of this mumbo-jumbo? I have no idea, but I can
>speculate. First, unless the designer(s) of Book1 thought of - and
>accomodated - this type of access, the side-effects could, potentially, be
>disastrous. Second, this sneaks around an intended VBA feature. If MS
decides
>to block this "ability" those exploiting it are on their own.
>


Why do you feel this is a bad thing !. All you are doing
is calling a macro with a user form from a remote
workbook or add-in

It allows the developer to put standard forms and code in
an add-in and use them from many workbooks.

This is usually regarded as GOOD as it greatly simplifies
support.

Keith

ztx

unread,
Mar 19, 1999, 3:00:00 AM3/19/99
to
Thank you very much,I resolved the problem with your help!

Tushar Mehta

unread,
Mar 24, 1999, 3:00:00 AM3/24/99
to
[This followup was posted to microsoft.public.excel.programming and a copy was
sent to Keith Willshaw <10057...@compuserve.com>.]

Keith:

I did not (mean to) imply that accessing an object from another workbook was
'bad' programming, per se. If anything, it is the exact opposite since I'm a
strong believer that once one makes a copy of an object, the probability that
the two (the original and the copy) will remain identical approaches zero
exponentially.

However, in this case, there are two factors that one must consider. First,
the design of VBA doesn't explicitly allow one workbook to access an userform
in another workbook. The documentation indicates that one should export the
userform from the original workbook and then import it into the second workbook
- effectively creating a second userform that, at least at the moment of its
creation, is identical to the first. To enforce the restriction VBA disallows
code such as other_workbook.userform1.show. That is why one must use the 'work
around' I mentioned. However, what's to stop MS from closing that 'loophole?'
In which case, one would have to retrofit one's code from prior years.

The second factor - and the one with the potential for more immediate problems
- is the extent to which the workbook that 'owns' the userform anticipates and
cooperates with external users of the userform. For example, if the designers
of the userform did not plan for its use outside of the owning workbook, its
event procedures might contain references to 'thisworkbook' or might reference
variables not visible to other workbooks. The consequence would be bugs that
would be difficult, if not impossible, to debug.

One can argue that the userform restriction is arbitrary, meaningless,
wasteful, inherently error-inducing, etc, etc, etc. Unfortunately, dems de
rules!

--
Regards,

Tushar Mehta
--
In article <#eCtf38b#GA....@nih2naab.prod2.compuserve.com>, Keith Willshaw
<10057...@compuserve.com> says...

0 new messages