Macros in XLSX Files

Topic

New Reply

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

Reply | Quote

Replies

[brian1248]

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.

• This reply was modified 5 years ago by brian1248.
• This reply was modified 5 years ago by brian1248.

Reply | Quote

Got it and it works. Thanks.

 

 

Ronny

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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

Reply | Quote

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

Reply | Quote