0

OK I guess this is Part 3 of a larger issue that I was asking about here: Excel VBA opening old file

I'm building a template with a bunch of vlookups and some automation that a whole team of people is going to use record problems with datasets. It would have taken me 10 minutes in Access but no can do in this environment so my Excel template has been evolving with a bunch of workarounds - importing raw data from several other Excel files and redirecting the user to a checklist page that shows the info in a simpler way.

The issue I was having for the past couple days is Excel thinking I'm looking for procedures and/or macros in previous versions of the same file. I have been working on this template for months and I didn't encounter this problem at all until I made some buttons on a custom toolbar so people will be able to run macros and procedures just by clicking a button.

It seems like a strange property of Excel that if I change the name of the file and re-save it (again, it's a template that will be distributed to people all over), the toolbar buttons I created are associated with the macro and/or vb procedure in the previous file. I found some documentation about this, and a way to fix it here: Macro button under customized ribbon tab tries to open old Excel file

but this is crazy, no? I can't be exporting the custom ribbon, removing references to files and reimporting it every time I rename/move/copy the file in question. How do I create buttons in a workbook that will work in that workbook no matter what it's called or where it is?

Thank you all in advance!

BigBen
  • 46,229
  • 7
  • 24
  • 40
Chris
  • 49
  • 6
  • " I made some buttons on a custom toolbar so people will be able to run macros and procedures just by clicking a button" - *exactly* how did you do that? Did you create custom ribbon XML and add it to the workbook? – Tim Williams Jun 17 '20 at 00:32
  • @TimWilliams well I had the macros/procedures and then I selected File>Options>Customize Ribbon. From there you an see a list of macros on the left and add any of them to a place in the toolbar sections on the right. – Chris Jun 17 '20 at 01:40
  • More importantly, I'm starting to think this is a problem which has no solution other than to code a new ribbon each time the file opens: https://social.msdn.microsoft.com/Forums/en-US/9d2cb361-d383-4a97-af35-854846bec5c2/problem-with-custom-ribbon-with-vba-macros-for-excel-after-i-save-as-to-a-new-file?forum=isvvba MS guy says it's not a bug and everyone else disagrees! Just a poor job of implementing this feature – Chris Jun 17 '20 at 01:50
  • That thread is almost ten years old, but the problem is one of misunderstanding how the point-and-click ribbon customization works, vs. creating an embedded ribbon interface using Ribbon XML. It's not as easy as the point-and-click but it's quite manageable and if you want things to work across versions and PC's that's how you have to do it. Ron de Bruin's pages (referenced in that thread) are a good place to start. https://www.rondebruin.nl/win/s2/win001.htm – Tim Williams Jun 17 '20 at 04:36

1 Answers1

0

What type of file is this saved under Chris? Is it saved as an "Excel Workbook"? If so maybe saving it as an "Excel Binary Workbook" This is just an assumption give what I have read though.

Scott P
  • 29
  • 8