Log In
Register
Home
July 7, 2016
Offline
Hello,
I have a strange problem that keeps arising. The macros in question are the "+" and "-" boxes on the top row. Macros are assigned to these box shapes that hide or unhide columns. Excel often keeps the macro names the same when I save the file under a new name. For example, in the attached file, Ratios.xlsb, the + macro button in column BU, Ratios.xlsb!Rectangle41_Click, can keep exactly the same name under a new file name, in which case the macro won't execute in the new file. Usually, the macro name would appear as Rectangle41_Click. But when it acts up, I then have to go through each button and eliminate the Ratios.xlsx! prefix and it works, only to be repeated later.
I use .xlsb format because it reduces file size and works with code. I haven't had any problems with it in the past.
What are some solutions?
Paul
October 5, 2010
Offline
Hi Paul,
There are no macros in that workbook.
It's an XLSB so perhaps somehow in the conversion from XLSM to XLSB the code modules were removed?
With regards to the naming, you can't save macros in an XLSX file so you shouldn't be seeing macros assigned to button that begin Ratios.xlsx!
This will only happen when you create an XLSX, then write a macro, assign it to a button/shape and do not save the file as XLSM/XLSB
At this point you have VBA code in an XLSX file that will execute. When you try to save the workbook, Excel will tell you that you need to save it as an XLSM to keep the VBA Project (the code modules). If you save it as XLSX, the code remains as long as you keep the workbook open.
As soon as you close it, the code is removed. When you open the workbook again there are no code modules.
regards
Phil
July 7, 2016
Offline
Hi Phil,
I saved a copy of the Ratios tab from a much larger file into a new workbook for purposes of attaching it to this post. I think this tab was initially saved as .xlsx, so that would have wiped out the code, as you say. The source file is .xlsb format and I haven't had it in any other format from the beginning.
So the file I attached doesn't help, and yet, I do get the problem described above on occasion. I'm not able to reproduce it at the moment. At first, I thought it might be related to saving the file in a new location, but I think that's not the problem.
Paul
1 Guest(s)
