VBA Checkboxes (Forms)

[PatHay]

Hi, this is my first post here, but have been using this forum for some time now and always have found solutions and answers to my various Excel/VBA issues.

My current issue that I need some help with is to do with a form Checkbox (used in an Excel sheet) and can't seem to find any topics directly related to my issue.

In a worksheet I have some code that will uncheck a Checkbox if a specific cell value is 'Yes' and then it waits 30 seconds before activating the checkbox again - this all works fine.
When the checkbox is ticked, it changes a group of cells from their default colour (grey) to various colours, subsequently when when unticked they are all grey again.

My issue is that when the auto unticking happens (because of the cell value 'Yes') the group of cells do not go back to their grey colour and remain multi coloured as iF the box was still ticked, but it is not.

The code is a Change one and I guess then that it does just that and change the Checkbox status and does not trigger any actions - like it normally does with a mouse click when toggling it on and off.

This is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("DA1").Value = "Yes" Then
ActiveSheet.CheckBoxes("EnableTriggers").Value = xlOff

Application.Wait (Now + TimeValue("00:00:30")) 'wait 30 seconds from now
ActiveSheet.CheckBoxes("EnableTriggers").Value = xlOn
End If
End Sub
=========
What else do I need to add to make this work please - is it some sort of Click action?

Am a novice as such using VBA, even if I have used it a lot over the years and am still finding great ways to use VBA almost every day. Any assistance/advice greatly appreciated, thanks.

 

[Micron]

Seeing the checkbox code might help. Your colour changing code should be in there, and AFAIK, checkbox click event code will run if checkbox state changes even if triggered by other code.
Please use code tags (vba button on posting tool bar) to maintain indentation and readability.

 

[PatHay]

Thank you for your prompt response. The Checkbox on/off status relates to a specific cell, that shows either True/False (there is no Checkbox code) that then makes the other cells change colours with various IF statements in, re True/False etc. I really thought that this would perform the same way when triggered by my auto on/off code, but oddly it does not.

Tried it also by removing the timeout line, just in case that was having an impact, but made no difference, same result.

Am testing now without the cell value 'Yes' to run it and just doing a macro to tick & untick the Checkbox to see if it behaves the same way. Will see how that goes and update later.

 

[PatHay]

I have found another post here that has helped to solve my issue - to untick a Checkbox and still have it trigger its action. This post was so useful to me:

Checking a Check Box using VBA

Hey everybody, I'm just wondering if it's possible to check or uncheck a check box in VBA? If it is, I would really like to know how please =)

www.mrexcel.com

Posting it here in case anyone lands here for the same issue. Thanks.