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

Sah213

New Member
Joined
Feb 25, 2014
Messages
5
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;

File-Copy-icon.png

VBA Code:
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Sah213

New Member
Joined
Feb 25, 2014
Messages
5
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

New Member
Joined
Feb 25, 2014
Messages
5
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" ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,210
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top