Hoping for some clever people to help out. I'm a VBA novice, but trying my best.
I have a large excel tool, with a lot of VBA included. Performance isn't perfect, but it's completely workable. I'm running Office 365 ProPlus 64 bit, other users running 32bit are also having this problem.
I have 5 form control checkboxes included on 1 sheet. As an example, 1 of them is assigned to the below VBA in a module;
If I click these checkboxes at any time they work perfectly UNLESS I have run 1 particular routine immediately before. Let's call that one RoutineA.
RoutineA does a number of things including making an ADODB connection, downloading a record set & then a number of formatting changes on other sheets.
If I've run RoutineA immediately before, then immediately click any of the checkboxes to run the checkbox VBA - excel locks up completely. The file doesn't crash in the standard excel sense (screen doesn't white out), the cursor does trail around but clicks are unresponsive everywhere. I can still close the file using the cross & it does give me the save dialogue box.
If I've run any other piece of VBA between RoutineA & clicking the checkbox - the checkbox VBA runs fine on a cell click.
If I step into the checkbox VBA, rather than running off the checkbox mouse click then it steps through fine with no errors.
If I continuously press ESC when excel is in this stage, the majority of the time I get no response/change in the state but once or twice I've get a pop up saying "The cell or chart you're trying to change is on a protected sheet" which makes me think the checkbox VBA is still running in the background & that's what is locking up excel.
Is there something I should clean up after my RoutineA that could be causing conflicts with this checkbox VBA?
Or is there something in this checkbox VBA which is bad practice & could be causing this?
If possible, I really want to keep the checkboxes- I've seen some people complain about them causing performance issues & suggest alternative options of using cell clicks & windings to make it appear like it is a checkbox but that would require a re-design of the relevant tab & I'd really like to avoid if possible.
Many thanks for any suggestions!