Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Annoying Excel Messages (Excel 97, SR2)

    I am trying to post several hyper-linked workbooks onto a hosting server. Whenever I open any of these workbooks, I get a message asking me if I want to run Macros, and then a second one saying, "The workbook you opened contains automatic links to information in another workbook. Do you want to update this workbook with changes made to the other workbook? YES/NO".
    I don't need to update the sheets as the data is static. How do I prevent these annoying messages from appearing in the first place?

    I tried going into Tools/Options/Calculation and changing the 'Workbook Options' settings, but to no avail. Can anyone help?!

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    Userforms or modules or classes or code in This Workbook or any Sheet object will cause a warning about code.

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    Many thanks - I had a look at the Modules folder and cleared that away. Used the find links utility to identify some redundant links (dunno where they came from!) and deleted those. Works like a dream now.

    Thanks again,
    Cunners

  4. #4
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    I am playing about with the same bunch of files again and my problem seems to have got WORSE! I have gone into the Macro Editor, looked at Modules and removed them all. Yet still when I open my spreadsheet I am getting the "The workbook you are opening contains macros - Do you wish to open them?" warning box. I really want to get rid of this!
    Is there an add in or something that can help you track them down? (I know nothing about the code so looking at the sheets in the macro editor doesn't help)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    I don't know of a utility to find all code.

    You can't remove the modules belonging to ThisWorkbook and to the individual sheets, but you can make sure that they are all empty (double click each in turn). If that doesn't help, you might create a new blank workbook and copy all sheets from the problem workbook to the new one, save it and test whether the new one displays the macro warning.

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    There also could be code in tyhe ThisWorkbook and the worksheet objects.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    I will point you to <post#=258203>post 258203</post#> from Hans and I will reiterate (and expand on) his suggestions:

    Activate the Visual Basic Editor (Alt+F11)
    Display the Project Explorer (Ctrl+R)

    In the Folder labeled "Microsoft Excel Objects"
    Dbl-click each Sheet object and look at for any code in the "code Window". Delete any code you see.
    Dbl-click the ThisWorkbook object and look at for any code in the "code Window". Delete any code you see.

    If you see a folder for "UserForms": Open it and right click on EACH "object" in the Explorer view and select "Remove" and then "No" to exporting.
    If you see a folder for "Modules": Open it and right click on EACH "object" in the Explorer view and select "Remove" and then "No" to exporting.
    If you see a folder for "Class Modules": Open it and right click on EACH "object" in the Explorer view and select "Remove" and then "No" to exporting.

    Once this is done you have no macros and no indication of any macros. If you HAVE a "folder" labeled as "Forms", "Modules", or "Class Modules", even if they have no code in them, it will trigger the warning. The Modules/forms MUST be removed.

    FYI, adding controlbox toolbars items and adding code to them will trigger a macro warning. This code is NOT part of a module, but is a part of the sheet object of the sheet they are added to. That is why you must also check in the sheet and thisWorkbook object.

    Steve

  8. #8
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    Hi Hans,

    I have tried all of that and I also tried copying the worksheets to a new workbook, but I still get the error...

  9. #9
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    Steve, I went into the project explorer and I had the following:

    AUTOSAVE.XLS (autosave.xla)
    VBA Project (<filename>.xls)
    Microsoft Excel Objects
    (long list of sheets here)

    I have double clicked on each of these sheets and in the "code window", with (General) showing in the object drop down box on the top left, there is no code. If I change the drop down from (General) to Worksheet , I have the following code:

    Private Sub Worksheet_selectionchange (ByVal Target As Excel.range)

    End Sub


    This is the same on every sheet and if I try to delete it, it won't go away. Is any of this of any relevance?

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    That code is doing nothing.
    You can delete the code, but not the sheet objects containing the code.

    If deleting the code doesn't do the deed, then you could create a new workshhet and copy the worksheet contents, but not the code, for each worksheet,

    The other issue is you need t o find out whether the AutoSave add-in is modifying anything.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    When you click on the box and select Worksheet, you are CREATING this code. If you dbl-click on the object and the caption is correct for workbook and object and there is nothing that is all you need do.

    If you still find no code and you still get the message:
    Make a copy of the workbook.
    In that COPY
    Right-click on the sheet names and choose select all sheets
    Type <ctrl>-A to select all and hit <delete>
    Now your sheets are empty and all you should have is the code.
    Save it and then attach it to a message here and we can look for macros.
    Erasing all the sheets will eliminate anything proprietary.

    Steve

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    Drag the mouse over the
    Private Sub Worksheet_selectionchange (ByVal Target As Excel.range)

    End Sub
    ..and press the [delete] key.

    zeddy

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Annoying Excel Messages (Excel 97, SR2)

    (Edited by HansV to update link to Excel MVP site)

    About the macros: check carefully if there is any code in your workbook. Activate the Visual Basic Editor (Alt+F11), display the Project Explorer (Ctrl+R). If there is a section UserForms or Modules, that will cause the macro warning. Also double click ThisWorkbook and each of the worksheets to see if the associated module contains code.

    Hyperlinks should not cause the message about automatic links. To find out if there are external links in your workbook, download Bill Manville's FindLink add-in. This add-in allows you to convert external links to static values.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •