Troubleshooting Macro Enablement and Breaking Links

I have a set of questions with regard to an existing spreadsheet.  I have a simple Reset Button that I created,  with code that when "pushed", resets a set of fields back to null value.  The problem is that I am now getting an error message with regard to "Macros" being enabled.  It instructs me to save the sheet and when reopening, enable Macros.  I do that, then it tells me I have lnks that cannot be updated.  I know of no links I have in this spreadsheet..  When I open the Update Linkages and choose to Break Links, it doesn't do it.   How do I enable Macros so I can run this simple piece of code for resetting my columns?  And how to I either trace down a link that I've got to get out of my sheet (Lord knows how it got there to begin with) or can I break it....and if I can, why doesn't it go away?   Questions welcomed.
Fixing-Macro-execution-and-delet.ppt
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkpieterseCommented:
Hi,

With regards to the links, download FindLink from www.oaltd.co.uk/mvp.

To be able to enable macros, you must set Excel's  macro security to the appropriate level. What Excel version are you using?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
I am using Excel 2007.  I chose to enable Macros (not recommended) when I crank up Excel, and it still doesn't execute the "reset" for some reason.  I believe it may have something to do with digital signatures....but I hope not...... I don't have any experience with self-generated DS.
Thank you,
Bright
0
jkpieterseCommented:
We'll get there!

First of all, it seems your macros are still not enabled. Can you manually run the code that is tied to the button?

Can you perhaps attach your file (remove confidential information please)!!

Excel knows two types of buttons, Forms buttons and ActiveX buttons.

If you used the forms button, you should be able to right-click the button and choose "Assign Macro".
If that is the case, you'll get a dialog with a macro name mentioned in the top textbox and an Edit button on it. Click the Edit button. You should be taken to the macro you assigned to the button. Is this macro in a normal module (listed under "modules" in the project explorer on the left)?

If it is an ActiveX button, you should have an option called "View code". Where does that take you to?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Bright01Author Commented:
jkpieterse,
Thanks.  Sorry for the delay.  I've been out with a hernia operation recently so I'm responding a little slow.  Can we start at the beginning?  My spreadsheet has become rather complex and I had Macros that worked.  Now the Macros don't work and I have something that says that I have links.  When I look at the links, I try but cannot "break them".  I also now have an encription password I didn't have before.  So I'm trying to figure out what I did wrong.  I pasted part of my sheet into another sheet that I think had these problems and am now trying to trouble shoot it.  
A couple of specific questions;
If I save a spreadsheet as a Macro enabled spreadsheet, do I have to choose the option to run Macros every time I open it?
Is there any way to find the linked fields that are in my spreadsheet so I can change them or delete them?  Any reason why it won't allow me to break a link?
If I cannot find or retrieve the password on the encription tab, do I have to start over or can I copy fields over to another spreadsheet (fortunately, I've versioned it so I won't have to start from scratch.
Thanks much for the help.
Bright
 
 
 
0
Vyyk_DragoCommented:
Hi Bright01,

I have seen this happening before when people copy sheets from one workbook to another to make a new workbook.  Usually, people only copy the sheets and not the underlying macro code.  Is this file actually the original file that contained the macro or is this a copy of that file that was made by copying sheets to a new workbook?  Do you see any macros listed if you press ALT+F8 to bring up the macro dialog box?  If no macros show up, the this is probably the issue.

Also, on your one screenshot, the Developer tab shows the Design Mode button as being active.  Macros will not run while Design Mode is toggled on.

Finally, as for the links - links can be contained in range names.  Those links do not typically show up in the link dialog.  If you go to Insert -> Names -> Define... you can look at the RefersTo for any names and see if any have external definitions.  I have seen instances where sheets being copied across end up creating range names in a workbook that still reference the original file the sheet containing the range name came from although I have never been able to recreate this myself on demand so I suspect it might be caused to some types of corruption that occur in spreadsheets from time to time.

Hope this at least gives you  a starting point.
Regards
Vyyk
0
jkpieterseCommented:
To get rid of the links I strongly suggest you to download the FindLink file I already pointed you to above.
Can you attach a stripped down version of the workbook here? (just remove everything excpet the button(s) you use to start the macro with)
0
Bright01Author Commented:

Vyyk,
Thanks for the note.  Yes, I copied a number of the tabs into another sheet.  And "yes" I have a bunch of range names.  I just checked the Alt.pf8 for Macros and none of my macros are in the new sheet; so must have been left behind.  I think what I'm dealing with here is a corrupted spreadsheet.  The Encription is "on" and I don't have the password so I cannot even add new tabs.  I'm hoping I can copy and paste the sheet into a fresh one and move on with it.  
 
jkpieterse,
I'll download the FindLink file and send you some code shortly.
 
Thanks guys.
 
Bright
0
Bright01Author Commented:
OK gentlemen,
Here's the situation, I think I have some rogue VB code in one of these sheets.  I'm now checking.  I have both an XLSX and XLSM saved that are versioned so the encription is something that has just showed up.  Excel also advises me to save the file as a Macro enabled file XLSM or I lose something called a VB Project.  I don't have to my knowledge a VB Project element to this workbook; unless you tell me a Macro is concidered a VB Project.
Going to run the links software next.
0
Bright01Author Commented:
Is this natural?  I can't add another sheet/tab either.
Why-can-t-I-add-another-tab-shee.pptx
0
jkpieterseCommented:
Hi Bright,
If your file has some VBA code (AKA macro's), it will have what is called a VB Project (it is where your macros are stored). If you save that file to xlsx format, the VB project is removed from the file and hence also your macros.
So if you have a file with macros, you always have to save the file to a format that allows the VB project to be saved with the file. This -amongst others- is the xlsm file format. So before saving, select the xlsm format from the file type dropdown, which you find at the bottom of the Save-As dialog screen.
I suspect the workbook is protected, which is why you cannot insert worksheets.
0
Bright01Author Commented:
Hard set of questions that the guys here jumped right in and gave me good advise.  My sheet is back up and running.

Big "thanks"!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.