Checkbox crashing/locking up excel resources ONLY when used after another VBA routine

[Sah213]

Hi All!

Hoping for some clever people to help out. I'm a VBA novice, but trying my best.



Context:
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.


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;

Sub EditStages()

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

Sheets("Project Data").UnProtect "CTCTool"

If Sheets("Project Data").Shapes("EditStagesCheck").ControlFormat.value = 1 Then
    Sheets("Project Data").Rows("44:60").Select
    Selection.EntireRow.Hidden = False
Else
    Sheets("Project Data").Rows("44:60").Select
    Selection.EntireRow.Hidden = True
End If

With Sheets("Project Data")
    .Protect Password:="CTCTool", AllowFiltering:=True, AllowInsertingHyperlinks:=True
    .EnableSelection = xlNoRestrictions
End With

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

End Sub

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.


I've tried:

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.

I've tried updating the VBA of the checkbox so it refers to the true value of the linked cell, rather than the control.format of the checkbox, but same issue.


Questions:
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!


p.s. cross posted to ExcelForum after no response there, so sorry if you've seen this twice.

Checkbox crashing/locking up excel resources ONLY when used after another VBA routine

Hi All! Hoping for some clever people to help out. I'm a VBA novice, but trying my best. Context: 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...

www.excelforum.com

 

[Sah213]

RoutineA ends with showing a modeless UserForm which the user closes down manually using the cross.

I've just realised there's a crossover there with form controls & UserForm - not sure if it's relevant or gives any more ideas.

 

[Sah213]

Tried changing form control checkbox to activex checkbox launching macro using _click event & same behavior from Excel.

I've decided the excel behaviour is best described as "hanging" rather than crashing.

Keeping thread updated with different attempts I've tried to "bump"