Excel VBA Reference to Forms Object Library (to be unchecked)

T

tkt_tang

1. Enter Excel workbook VBA code modules.

2. Select Tools > References, and it shows that "Microsoft Forms 2.0
Object Library" is checked.

3. Now, it's unable to uncheck "Microsoft Forms 2.0 Object Library".

4. Why ? Since it shows that "Can't remove control or reference ; in
use".

5. However, go to Worksheet ; Select Edit > GoTo > Special > Objects ;
and there, it shows that "Objects not found".

6. The requirement is to uncheck the reference to Forms Object Library
so that the code modules could be run in order.

7. But, How to uncheck the Forms Object Library ?

8. Please share your experience. Regards.
 
P

Peter T

Like you I cannot remove the MSForms reference manually, though this worked
for me -

Declare 'ref's' & 'rf' As References & Reference respectively if working
with a reference to the Extensibility library, otherwise 'As Object'

Set refs = ThisWorkbook.VBProject.References
On Error Resume Next
Set rf = refs("MSForms")
Err.Clear
If Not rf Is Nothing Then
refs.Remove rf
End If

I assumed this would fail if a Userform exists in the project. To my
surprise it didn't (ie can remove the reference) but not sure of the
consequences if any form modules are not first exported and removed.

To add it back again if necessary, assuming it's known not to exist or with
an error handler -

sGuid = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"
refs.AddFromGuid sGuid, 0, 0

Be aware other things also use this library, eg DataObject
6. The requirement is to uncheck the reference to Forms Object Library
so that the code modules could be run in order.

Interesting, why?

Regards,
Peter T
 
N

NickHK

I see this behaviour if I have added a userform or any of the normal control
to a worksheet and then deleted all of those objects.
Even after saving, closing and re-opening.
May be Rob's Code Cleaner will help:
http://www.appspro.com/Utilities/CodeCleaner.htm

However, why is this a problem ?

If no userforms or controls have ever been added, this reference is not
checked.

NickHK
 
T

tkt_tang

6. The requirement is to uncheck the reference to Forms Object Library
so that the code modules could be run in order.
Interesting, why?

Regards,
Peter T
______________________________________________________________________

<[email protected]> replies :-

The affected code modules could be copied to a new workbook ; and
there, the code runs "flawlessly". Upon examination, Tools >
References, Microsoft Forms 2.0 Object Library is not checked in the
new workbook.

When the code is run in the original workbook, it causes an error,
"Type Mismatch". The error does not recur when the code is run in the
new workbook.

If there's a simple way to uncheck the Forms Object Library in the
original workbook, it would save deploying a new workbook.

The "check" has resulted from Chart plotting in the original workbook,
I think. And then, all the charts are deleted ; and there, unable to
uncheck the Forms Object Library (as much as I could repeat saying
once more over again).

Regards.
 
P

Peter T

Comments in line below -

6. The requirement is to uncheck the reference to Forms Object Library
so that the code modules could be run in order.
______________________________________________________________________

<[email protected]> replies :-

The affected code modules could be copied to a new workbook ; and
there, the code runs "flawlessly". Upon examination, Tools >
References, Microsoft Forms 2.0 Object Library is not checked in the
new workbook.

When the code is run in the original workbook, it causes an error,
"Type Mismatch". The error does not recur when the code is run in the
new workbook.

Where do you get a "Type Mismatch"
If there's a simple way to uncheck the Forms Object Library in the
original workbook, it would save deploying a new workbook.

As I mentioned before, like you I could not uncheck the Forms library, even
with nothing in the workbook (worksheet controls) or in the VBProject is
associated with the library.

However, the macro I posted does appear to remove the reference. If the
macro is not in the same project, change 'ThisWorkbook' to an appropriate
workbook reference, eg ActiveWorkbook. Of course you need to be sure the
Forms library is not required. Did you try the macro.
The "check" has resulted from Chart plotting in the original workbook,
I think. And then, all the charts are deleted ; and there, unable to
uncheck the Forms Object Library (as much as I could repeat saying
once more over again).

I can't think of any reason Chart plotting, or anything to do with charts,
would add the Forms reference. Are you sometimes exchanging workbooks
between with Excel 97 and a later version. The Forms library is added by
default in XL97 even if not required.

Between XL 97 to 2003 I think (but not certain) there has been one version
change to the Forms library. However I don't recall ever having a backwards
problem compatibility with this lbrary.

The first thing to find out is why is the Firms ref getting added. With the
reference checked, press F2. In the Object Browser select MSForms in the top
combo and look at all the objects listed under Classes for anything that
might exist in your workbook.

Even with the Forms ref, you shouldn't get the 'Type Mismatch', post the
code where you get this. Do you have any other references indicated as
MISSING.

Regards,
Peter T
 
N

NickHK

Peter,
Whilst it is strange that it is not possible to manually uncheck the Forms2
reference, I do think the OP is barking up the wrong tree on this.
I go for another cause to problem.

NickHK
 
T

tkt_tang

Thank you for replying to my query.

Will post another query to detail the cause of "Type Mismatch".

For now, there are 2 workbooks (containing the same piece of code) ;
one can run, the other cannot.

Regards.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top