Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Message (2000)

    I keep getting a message when opening a large spreadsheet saying I have macros and get the usual three buttons "disable", "enable" and "more info".

    Trouble is, can't find any macros in the file. Have gone into Tools, Macro, Macros but the dialogue box does not list anything. Can anyone help me to find what is causing this message?

    Thanks in advance.

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

    Re: Macro Message (2000)

    There might be functions or event procedures in your workbook. They don't show up in the macro list. To check this, open the VBA editor (Tools/Macro/Visual Basic Editor or Alt+F11). You should see the project explorer - a window titled Project - VBAProject or something like that. If not, open it using View/Project Explorer or Ctrl+R.

    The project explorer shows a hierarchical tree structure like the left pane of Windows Explorer. See if you can find any Modules or UserForms belonging to your workbook. Also double click the items under Microsoft Excel Objects - ThisWorkbook and all sheets in you workbook.

    If you don't find any code there, the macro warning might be an artefact. You can try copying all sheets to a new workbook.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Message (2000)

    Create a new workbook, Alt+F11 to the VB editor; insert / module; paste the following code into it:

    Sub RemoveMacros()
    Dim O As Object
    For Each O In ActiveWorkbook.Sheets
    If TypeName(O) = "Worksheet" Then
    Select Case O.Type
    Case xlExcel4MacroSheet, xlExcel4IntlMacroSheet
    Application.DisplayAlerts = False
    O.Delete
    Application.DisplayAlerts = True
    End Select
    ElseIf TypeName(O) = "Module" Then
    Application.DisplayAlerts = False
    O.Delete
    Application.DisplayAlerts = True
    End If
    Next
    If Val(Application.Version) >= 8 Then
    With ActiveWorkbook.VBProject
    For Each O In .VBComponents
    Select Case O.Type
    Case 1, 2 ' standard or class module
    .VBComponents.Remove O
    Case Else ' form or document
    With O.CodeModule
    .DeleteLines 1, .CountOfLines
    End With
    End Select
    Next
    End With
    End If
    End Sub

    Then open the workbook which is said to contain code and
    Tools / Macro / Run / <the other book>!RemoveMacros

    Courtesy: Bill Manville
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro Message (2000)

    You get this message whenever there is a module in your worksheet, even if the module is empty. Open your workbook and press <Alt> <F11> to get to the IDE. In the project explorer window (upper-left) see if there is an entry for your workbook that says Modules. If so, expand it and check the modules in it. There is probably an empty one that you can select and then delete with the File | Remove ... menu. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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