• Storing macros (Excel 2003)


    OK, OK, you’ve probably already told me this already (department of redundancy department), but my computer, eh, hiccuped and the 2 Macros that I use all the time somehow died. Regardless, I killed what was left of them as I tried to repair them!

    Now, I want to record a macro that is ‘active’ as a button on the toolbar each time I open an Excel file. OK, I know how to assign a Macro to a button, so that is not part of the problem. I believe I want to save said Macro in my “personal.xls” file so that it is ‘active’ each time I use Excel.

    When I tried that, the Macro was saved in a different place — in the file I was working on — and not in personal.xls. So, I am stupidly doing something wrong. Does anyone have any words of advice to help?? Thanks….

    For starters, the image looks correct, verdad?

    Viewing 0 reply threads
    • #1105807

      OK, sorry, I spent a half hour messing around with this and I THINK I got the Macros stored where I want them.

      HOWEVER, if the Macros are stored in Personal.xls (which I have as a hidden file), why does Excel feel compelled to warn me as I open another file WITHOUT Macros that the file MAY have Macros? I wiped out (as best I could) any residual Macros (except for the two I use regularly, which I made double-sure are stored in Personal.xls). So why do I have to see a warning each time I open an Excel file warning me that it may contain Macros? (No, only personal.xls has Macros).

      Is there a simple solution, or do I have to live with these warnings? They are almost as annoying as the warnings telling you that you need to “Notify” that you are opening Personal.xls! Is there a way to kill those messages as well?? Thanks for humoring me. Just trying to make my life less annoying… Cheers. R2

      • #1105810

        You may have removed the macros, but have you removed the module(s) they may have been in? I believe this will still trigger the warning.
        In the VBA Editor, select File > Remove Module 1… and opt not to export it.

      • #1105811

        If you delete the text of macros but leave the module that contained them in the workbook, you’ll still get the macro warnings. To get rid of the macro warnings, delete the module:
        – Right-click the module in the Project Explorer (the treeview on the left hand side).
        – Select Remove Module from the popup menu.
        – Answer No to the question whether you want to export before removing.
        – When you have removed all modules from the Modules section, save the workbook.

        You’ll get a warning that Personal.xls is in use etc. if you open a second instance of Excel, for example by double-clicking a shortcut to the Excel application while Excel is already running. You should try to avoid opening multiple instances of Excel.

        • #1105891

          Leif / Hans….. I never knew that! A little more knowledge!! Thanks

        • #1106187

          To you both – a gracious thank you. Sometimes it is the simple annoyances, once removed, that make your day go by some much nicer!


        • #1122186

          I am also trying to remove a macro, but there are no modules in Visual Basic. I work in a networked environment, maybe the macros are hiding somewhere. Everytime I open my spreadsheet it asks if I want to disable the macro. I don’t want a macro in there at all! Any clues? My co-workers will be more inclined to look at my ss if that extra step is out of there!


          • #1122187

            There could also be code in the worksheet modules or in the ThisWorkbook module – see the screenshot in Leif’s reply post 706,447 higher up in this thread.

          • #1122188

            You also would need to check the sheets and workbook as well.

            • #1122191

              Maybe I’m looking in the wrong place. I’m not conversant with VB. I go to tools, macros, VBE and what I see is attached to this post as a bmp. Please let me know! thanks By the way, how did you get your screenshot into the message body?

              TX! baby

            • #1122194

              By the way, how did you get your screenshot into the message body? the same way you did grin

              Try right clicking on one of your sheets and choose “View Code” (see below) It will open the code window for that sheet. You then check the rest of the sheets by double clicking on the sheets (or workbook) in the Project Explorer (press Ctrl+R if you cannot see the Project Explorer)

            • #1122195

              Did that ! no code!

            • #1122196

              Can you post a locally saved version? Delete all of the data on the sheets since the data is not causing the problem.

            • #1122197

              sorry, that one was protected. try this one

            • #1122198

              Having userforms in VB will trigger the warnings even if they do not have code. You have 2 userforms…


            • #1122200

              thank you STEVE!! I removed them. problem solved. no more macro requests. where did they come from? bravo

            • #1122204

              Both userforms were blank, so I suppose that someone was playing around in the Visual Basic Editor and selected Insert | UserForm without having a definite purpose.

            • #1122205

              Like Me!! laugh

            • #1122199

              The workbook contains 2 userforms. These cause the macro warning.
              – Activate the Visual Basic Editor (Alt+F11).
              – Make sure that the Project Explorer is displayed (Ctrl+R).
              – Expand (i.e. click the + to the left of) Forms.
              – You’ll see UserForm1 and UserForm2.
              – If you want to get rid of them, right-click them and select Remove.

            • #1122201

              …and thank you Hans!!

    Viewing 0 reply threads
    Reply To: Storing macros (Excel 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: