Thread: MS Excel - suddenly I can't run my macros - didn't do nuffink.

The REALLY bloody weird thing is that the macros in the older version still work perfectly!

Do you have custom controls in the spreadsheet? The historical information that I can see about this kind of thing is when custom controls go wrong / get unregistered on the PC, and then they can't load. Have your recent changes to the sheet added anything "unusual"?

Brett, it is not just Windows where Excel struggles, but on my MacBook Pro (2019) as well. It cannot be a lack of RAM as I have 16Gb, and I certainly do not have a slow processor or SSD (top of the range on both accounts).

I was using Office 2011 until two years ago, when it no longer ran under the (then) latest OS (Mojave). I upgraded to Office 2016. No problems with Word or PowerPoint, but Excel would hang after two days (refused to save or close a file quickly ... the wheel would turn and turn for about 30 seconds). Only a complete reboot would get it running. (I use Excel for patient account records, invoices, receipts, and appointment notes, so re-booting loses time).

I now have Office 2019, and it is better behaved ... except Excel still hangs, but at least it will re-run after closing and re-opening Excel (rather than the computer). Word now no longer opens to a "full" screen (that is, up against the title bar, which I do not want to omit).

I would go to something else, like OpenOffice, if it had the ability to add Notes in the Excel-like substitute (this is where I write notes about the appointments).

Anyone help with opening Word (other than widening the screen and saving files individually)? Or curing Excel's habit of hanging after 2 days?

Regards from Perth

Derek

Thanks for your reply (again) poundy. Not sure what you mean by Custom Controls, but I don't think so. (I've forgotten most of my VB....use it or lose it). The macros are just in shapes (ovals in this case).

I haven't actually made any recent changes because of the cock-up msg I have been getting (summink to do with Sheet 5, which is not this sheet). I just ignored it for a few weeks, and didn't use the workbook much.

A little more info: the front sheet of this workbook has a stack of macros to open various things, and here is a sample:

Capture.JPG

The orange cells are to open folders via hyperlinks. =HYPERLINK("C:\2. MUSIC","2. MUSIC")

The grey cells are to open workbooks, and I have to click in the cell to trigger a Private Sub.

THEY WORK PERFECTLY!



The code for the macros that I "don't have a licence for" is stored in the sheet that they are used from. I have just tried an experiment and reassigned them back to the older workbook that I copied the shapes from, and they work. THEY ARE MOSTLY GO_TO macros. SO when they are sourced from the old workbook it has to be opened before they will work, of course - I expected that. But they do scroll to the line in the correct and CURRENT workbook.

Originally Posted by derekcohen

I now have Office 2019, and it is better behaved ...

My great difficulty with upgrading to Office 2019 is that my ribbon is HUGELY customised with tons of xml code and macros built into it. I have completely forgotten how to do all that, and it was a month's work at the time. It will be a bloody nightmare to upgrade.

Fer chrissake bring back XP! Utterly stable, and not a single crash in >10 years.

If you wanted to PM or email me the file Brett, I'd be happy to take a look - but I suspect that given much of it is external referenced files it may not be a relevant test, but again happy to try to assist. (for a long long time I have been an excel macro user, sometimes maestro )

Thanks poundy. You can remove it now. Yes, back in the day I wrote a few MBs of VB code for a mortgage broking suite, but things are much simpler now.

Hi Brett,

I've come across this before. If I recall it was a missing registry entry, and likely triggered by a security change that has affected the UAC controls, or something recently installed that has changed some registry entries. Your VBA is likely referencing older components such as an older ActiveX control.

If you can isolate the offending control, simple re-register should fix this.
i.e. run the following:

Code:

regsvr32 <control / component>

Note that this assumes you only have ONE copy of the same control/component - you can't register the same thing more than once, so this may also be an issue if you have multiple versions of the same DLL, OCX etc

If not the above, check that the workbook name matches (sometime the default is not what it should be, particularly if you use non-English versions of Office).

Add this:

Code:

$workbook->set_vba_name( 'ThisWorkbook' );

Finally, if you are using old VBA for Windows (v6 or older) on new systems this may cause problems if you used components from that in Excel. Solution would be to replace those components.

Hi Ross, thanks for your reply again too. There are no ActiveX controls in this workbook - IIRC they are the drop down menus and buttons et al that can be created from VB. I only run these simple macros from shapes (assign the macro to them). How can it be that the slightly older version of the workbook is okay, and this current one is not ok....but only on this sheet?

One thing I tried to do was to create a new shape on this sheet and assign a macro to it (a simple go_to). I still get the same msg.
Another thing was to create a new shape, assign a macro, but on a different page (the front sheet that has Private Subs that are still working). Nope.


The sheet 5 that was giving me the xml error was a hidden one - it's just a list of my fonts with no buttons, shapes or macros, so I dunno what that error was all about.

Doing a little digging and observing....

Press the button to look at the code - note that I have selected This Workbook macros. Those first four are in Module one, and the buttons on the right are all accessible, except "Create" because I have selected a macro.
ALL GOOD.


Capture.JPG

Sheet 24 is "MUSIC". The macro highlighted in this shot is housed within the Sheet code for Music, but is NOT called Sheet24.Screen_music, and all the buttons are greyed out.

Capture1.JPG


However, the macros beginning with Sheet24 at least get the top two buttons activated.

Capture2.JPG


Then we get to the truly bizarre. The top one (expanded) is the older version. Everything looks as it should. However, note Sheet24 on the bottom one (current version). It does not have the name "MUSIC" but it does in the Excel display. It has a different icon to the others too.

Capture3.JPG

Is that the ActiveX control that is playing up?

Ok, there is definitely something fishy with Sheet 24. I just copied one of the macros into a regular module (which I have called MUSIC_macros), reassigned the shape to it, and it works.

Now, it is no big deal to reassign all the other buttons, but that still doesn't solve the problem of the dodgy sheet.
Equally, it is probably no big deal to recreate the data on the sheet - maybe I can just duplicate the sheet (but I suspect the new one would also be dodgy), so I can just copy all the data, formats and equations
BUT
that doesn't really provide an explanation for what happened (and therefore how to avoid it again).....

Here's another clue (but I don't know what it is or means).

The Properties for all the other sheets look like this:
Capture1.JPG


but the dodgy sheet has a much longer list:

Capture.JPG


¿Que?

Originally Posted by FenceFurniture

Capture3.JPG

Just looking at that capture again. In the top (older) workbook, you can see Sheet 24 (MUSIC), and that is the last sheet created. However, in the book below (the problem book) there is Sheet 25 (WORK SHEET) which is a recent addition, and not related to this problem, but then there is Sheet26 (MUSIC) which is obviously the one I can see in Excel.

Originally Posted by derekcohen

Brett, it is not just Windows where Excel struggles, but on my MacBook Pro (2019) as well. It cannot be a lack of RAM as I have 16Gb, and I certainly do not have a slow processor or SSD (top of the range on both accounts).

I was using Office 2011 until two years ago, when it no longer ran under the (then) latest OS (Mojave). I upgraded to Office 2016. No problems with Word or PowerPoint, but Excel would hang after two days (refused to save or close a file quickly ... the wheel would turn and turn for about 30 seconds). Only a complete reboot would get it running. (I use Excel for patient account records, invoices, receipts, and appointment notes, so re-booting loses time).

I now have Office 2019, and it is better behaved ... except Excel still hangs, but at least it will re-run after closing and re-opening Excel (rather than the computer). Word now no longer opens to a "full" screen (that is, up against the title bar, which I do not want to omit).

I would go to something else, like OpenOffice, if it had the ability to add Notes in the Excel-like substitute (this is where I write notes about the appointments).

Anyone help with opening Word (other than widening the screen and saving files individually)? Or curing Excel's habit of hanging after 2 days?

Regards from Perth

Derek

I now use the current version of Libre office and have no problems inserting comments or text boxes in the calc (XL) sheets.
I convert old MS office documents as needed and if emailing to someone just send them a PDF or if they request an MS format just save it in that and email it to them