News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Macros in XLSX Files

    Viewing 3 reply threads
    • Author
      Posts
      • #2255669 Reply
        Ronny
        AskWoody Lounger

        I am working on converting a batch of Excel files to XLSX files. (Long story.) One thing that surprised me is that when I converted the files to XLSX, the macro behind the CLEAR button on many of the worksheet tabs continued to work and I did not get a macro warning when I open the files.

        I’ve tried Google and it cannot tell me why the clear button continues to work. I’ve attached one of the worksheets in case anyone wants to take a look at it. It appears that the VBA code is password protected and I do not have the password. Also, I cannot figure out how to move or delete the clear buttons.

        Any help would be appreciated.

         

        Ronny

        Attachments:
      • #2255711 Reply
        brian1248
        AskWoody Lounger

        Go to Review.  Unprotect Sheet (on whatever sheet you need to remove the Clear button).

        Then go to Developer.  Activate Design Mode.

        Right click button to remove.  Click Cut, and just don’t  paste it anywhere.  Or you could paste it somewhere else to move it.  Or move it as usual by dragging in this mode.

        I downloaded your file, and did this myself successfully.

        Unprotect

        Design-Mode

        Right-Click

        After-Cut

        • This reply was modified 2 months, 1 week ago by brian1248.
        • This reply was modified 2 months, 1 week ago by brian1248.
        Attachments:
        • #2256961 Reply
          Ronny
          AskWoody Lounger

          Got it and it works. Thanks.

           

           

          Ronny

      • #2255726 Reply
        brian1248
        AskWoody Lounger

        The macros might still be in there because the sheets are Protected, since you need to Unprotect to remove them, as noted in my prior response.

        This might be a way for someone to design using XLSM and then distribute it using XLSX.  I can’t say for sure.  I’ve never written a macro, protected a sheet, and then converted to XLSX before, but it appears plausible.

      • #2256417 Reply
        mngerhold
        AskWoody Lounger

        Can you confirm on which sheet(s) the button works for you, because none of them work for me – not surprising, as there appears to be no code behind them – the VBA project window opens OK (it doesn’t seem to be protected) and shows an empty set.  I did add some code, and saved successfully as .xlsm (as one might expect).  I then saved as .xlsx, and after a warning the file was saved – but the code had been cleared (as one might also expect).

        • #2256960 Reply
          Ronny
          AskWoody Lounger

          I think I have resolved part of the issue. I was converting a bunch of these files from xltm to xlsx for a client. So, I would load the xltm file, save it as xlsx, and then make the changes the client wanted. At that time, the clear button worked as an xlsx file. Now, if I go back and load the xlsx file from scratch, the clear button no longer works.

          I am assuming what happened is that Excel retained the macros in memory even through xlsx does not support macros. Once I closed the xlsx file and restarted Excel, those macros no longer worked. They never worked for you because you did not start with the xltm files. So, yea, that is resolved.

          That leaves the problem of the clear button. I’m the one who protected the sheets (at the client’s request) but even after turning off the protection, I cannot select or delete the clear buttons by right clicking on them.

          Any suggestions?

           

          Ronny

          • #2256996 Reply
            mngerhold
            AskWoody Lounger

            Brian answered that: “…Then go to Developer. Activate Design Mode. Right click button to remove.”

    Viewing 3 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Macros in XLSX Files

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