Removing unused reference

I have a spreadsheet in Excel 2000 that has a reference set to "Microsoft
Forms 2.0 Object Library." This spreadsheet has no forms in it -- it
consists of one sheet, with some calculations and two short macros. The
spreadsheet was created in 1998, so it's possible at one time there were
forms in it, but there aren't now.

I am trying to check the box to remove the reference to it, and I'm getting
a message that says "Can't remove control or reference; in use." I can't
find anyplace where it is in use.

I've even gone so far as to delete ALL the rows in the spreadsheet below the
initial four column headings, thinking I'd just type the calcs back in and
copy it down -- so whatever might have been in there and not been visible
SHOULD have gone away -- but hasn't.

How can I get this reference to go away? Thanks in advance.

Any Excel 97 file that was exposed in the Excel 97 VBE will automatically
get the Forms reference added, even if no code was subsequently inserted.
AFAIK once saved it can't be removed. But it won't do any harm and doesn't
flag any macro security warning (assuming no inserted modules and no code in
sheet/worksheet modules). Why do you need to remove it.

In later versions the ref only gets added if you insert a userform or add it
in tools references.

Regards,
Peter T

"KarenH" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet in Excel 2000 that has a reference set to "Microsoft
> Forms 2.0 Object Library." This spreadsheet has no forms in it -- it
> consists of one sheet, with some calculations and two short macros. The
> spreadsheet was created in 1998, so it's possible at one time there were
> forms in it, but there aren't now.
>
> I am trying to check the box to remove the reference to it, and I'm
getting
> a message that says "Can't remove control or reference; in use." I can't
> find anyplace where it is in use.
>
> I've even gone so far as to delete ALL the rows in the spreadsheet below
the
> initial four column headings, thinking I'd just type the calcs back in and
> copy it down -- so whatever might have been in there and not been visible
> SHOULD have gone away -- but hasn't.
>
> How can I get this reference to go away? Thanks in advance.

Thanks!

In answer to your question about why I'm trying to remove it, the
spreadsheet is hugely bloated (over 3meg), and freezes when anyone tries to
cut & paste, copy and paste, or delete anything. In trying to fix this, I
noticed a lot of references to things that aren't even in there, and this was
one of them.



"Peter T" wrote:

> Any Excel 97 file that was exposed in the Excel 97 VBE will automatically
> get the Forms reference added, even if no code was subsequently inserted.
> AFAIK once saved it can't be removed. But it won't do any harm and doesn't
> flag any macro security warning (assuming no inserted modules and no code in
> sheet/worksheet modules). Why do you need to remove it.
>
> In later versions the ref only gets added if you insert a userform or add it
> in tools references.
>
> Regards,
> Peter T
>
> "KarenH" <[email protected]> wrote in message
> news:[email protected]...
> > I have a spreadsheet in Excel 2000 that has a reference set to "Microsoft
> > Forms 2.0 Object Library." This spreadsheet has no forms in it -- it
> > consists of one sheet, with some calculations and two short macros. The
> > spreadsheet was created in 1998, so it's possible at one time there were
> > forms in it, but there aren't now.
> >
> > I am trying to check the box to remove the reference to it, and I'm
> getting
> > a message that says "Can't remove control or reference; in use." I can't
> > find anyplace where it is in use.
> >
> > I've even gone so far as to delete ALL the rows in the spreadsheet below
> the
> > initial four column headings, thinking I'd just type the calcs back in and
> > copy it down -- so whatever might have been in there and not been visible
> > SHOULD have gone away -- but hasn't.
> >
> > How can I get this reference to go away? Thanks in advance.
>
>
>

I have a similar problem. I experimented with recording a macro on an excel
spreadsheet (Office 2003). It didn't do what I wanted and I deleted the
macro. Although there are no macros in this file anymore, everytime someone
opens the file he gets a message,"Macros are disabled because the security
level is set very high, etc."

I've tried setting security low, setting it high, in the middle, to no
avail. The message keeps appearing. Frankly its a bit embarassing for me that
all the users of this spreadsheet (there are scores of people who refer to
it) need to clik "OK" before they can use the spread sheet. All because of my
failed experiment at creating a macro. Is there anyway to get rid of it?

I've just found I can remove the ref programmatically, ie Forms ref that was
set in XL97 but can't be removed manually.

Sub test()
Dim wb As Workbook
Dim oRefs As Object ' References
Dim oRef As Object ' Reference
Dim sDes As String

Set wb = Workbooks("BookTmp.xls")
Set oRefs = wb.VBProject.References
For Each oRef In oRefs
sDes = oRef.Description
Debug.Print oRef.Name, sDes
If InStr(sDes, "Microsoft Forms") Then
oRefs.Remove oRef
End If
Next

End Sub

If you are using XL 2002/3 you'll need to allow access to VB in the second
tab of your macro security settings.

If you're having problems with your file I can't guarantee removing this ref
might do more harm than good. Not that I think it would but if you use this
do it in a back up.

I doubt removing the old Forms ref will cure your other problems you
describe.

Other readers - don't run this on a file that actually needs the Forms ref.

Regards,
Peter T

"KarenH" <[email protected]> wrote in message
news:[email protected]...
> Thanks!
>
> In answer to your question about why I'm trying to remove it, the
> spreadsheet is hugely bloated (over 3meg), and freezes when anyone tries
to
> cut & paste, copy and paste, or delete anything. In trying to fix this, I
> noticed a lot of references to things that aren't even in there, and this
was
> one of them.
>
>
>
> "Peter T" wrote:
>
> > Any Excel 97 file that was exposed in the Excel 97 VBE will
automatically
> > get the Forms reference added, even if no code was subsequently
inserted.
> > AFAIK once saved it can't be removed. But it won't do any harm and
doesn't
> > flag any macro security warning (assuming no inserted modules and no
code in
> > sheet/worksheet modules). Why do you need to remove it.
> >
> > In later versions the ref only gets added if you insert a userform or
add it
> > in tools references.
> >
> > Regards,
> > Peter T
> >
> > "KarenH" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a spreadsheet in Excel 2000 that has a reference set to
"Microsoft
> > > Forms 2.0 Object Library." This spreadsheet has no forms in it -- it
> > > consists of one sheet, with some calculations and two short macros.
The
> > > spreadsheet was created in 1998, so it's possible at one time there
were
> > > forms in it, but there aren't now.
> > >
> > > I am trying to check the box to remove the reference to it, and I'm
> > getting
> > > a message that says "Can't remove control or reference; in use." I
can't
> > > find anyplace where it is in use.
> > >
> > > I've even gone so far as to delete ALL the rows in the spreadsheet
below
> > the
> > > initial four column headings, thinking I'd just type the calcs back in
and
> > > copy it down -- so whatever might have been in there and not been
visible
> > > SHOULD have gone away -- but hasn't.
> > >
> > > How can I get this reference to go away? Thanks in advance.
> >
> >
> >

Did you delete the code module(s) you inserted.

Alt F-11 to open the VBE

Right-click on your project file in the left panel, Remove

Regards,
Peter T


"Elliot" <[email protected]> wrote in message
news:[email protected]...
>
> I have a similar problem. I experimented with recording a macro on an
excel
> spreadsheet (Office 2003). It didn't do what I wanted and I deleted the
> macro. Although there are no macros in this file anymore, everytime
someone
> opens the file he gets a message,"Macros are disabled because the security
> level is set very high, etc."
>
> I've tried setting security low, setting it high, in the middle, to no
> avail. The message keeps appearing. Frankly its a bit embarassing for me
that
> all the users of this spreadsheet (there are scores of people who refer to
> it) need to clik "OK" before they can use the spread sheet. All because of
my
> failed experiment at creating a macro. Is there anyway to get rid of it?

I was a bit short on explanation. When you record a macro a "Module" is
added. Find your workbook in the VBE as I described and expand any + signs
and look for "Modules".

Unless you've renamed them they'll be named Module1 etc. These are what you
need to remove, I assume you haven't added any Class or Userform modules.
Don't try removing anything under "Microsoft Excel Objects". When done
collapse the tree by clicking the - signs.

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Did you delete the code module(s) you inserted.
>
> Alt F-11 to open the VBE
>
> Right-click on your project file in the left panel, Remove
>
> Regards,
> Peter T
>
>
> "Elliot" <[email protected]> wrote in message
> news:[email protected]...
> >
> > I have a similar problem. I experimented with recording a macro on an
> excel
> > spreadsheet (Office 2003). It didn't do what I wanted and I deleted the
> > macro. Although there are no macros in this file anymore, everytime
> someone
> > opens the file he gets a message,"Macros are disabled because the
security
> > level is set very high, etc."
> >
> > I've tried setting security low, setting it high, in the middle, to no
> > avail. The message keeps appearing. Frankly its a bit embarassing for me
> that
> > all the users of this spreadsheet (there are scores of people who refer
to
> > it) need to clik "OK" before they can use the spread sheet. All because
of
> my
> > failed experiment at creating a macro. Is there anyway to get rid of it?
>
>