I would like a pop up warning for the new check boxes

[Beks]

Hi,

I can only find a solution with vba code for check boxes inserted using the old form controls, it does not seem to work for the new check boxes, or I am entering it wrong.

I am using the new checkboxes direct from the insert menu.

I would like a warning, asking if you would wish to continue when a box is checked. Currently I'm using data validation to pop up a message but I want the user to have to click ok to continue after checking a box.

I have no experience with vba so if it is a vba solution please provide the directions to where and how I insert the code, that would be greatly appreciated.

FYI, right clicking on the checkbox does not open the vba box for me, which I have seen as a way to enter it.

Thanks in advance!

 

[Beks]

I couldn't see a way to edit my post.

Below is the code I've found but I cannot get it to work. It was originally an answer given for the old check boxes.

If CheckBox1.Value = True Then
MsgBox "Click OK to continue", vbDefaultButton1
Else: Exit Sub
End If


insert this code into the checkbox code or into a macro.

 

[abdelfattah]

Hi

I couldn't see a way to edit my post.

because you're new member Just subscribed for time soon.
I'm not sure if I can help you , but after click ok what should do ?!!

 

[Beks]

Hi

because you new member Just subscribed for time soon.
I'm not sure if I can help you , but after click ok what should do ?!!

Hey,

Good point. After clicking OK it will check the box., it will also need to have a cancel option. Click cancel and it will not check the box.

 

[abdelfattah]

After clicking OK it will check the box.

but the code does really that .

 

[John_w]

I can only find a solution with vba code for check boxes inserted using the old form controls, it does not seem to work for the new check boxes, or I am entering it wrong.

Form Control check boxes are generally simpler to use than new (ActiveX) check boxes. Do you need ActiveX check boxes?

With multiple ActiveX check boxes on a sheet you would need a class module to handle the click event from any check box and code which assigns this class module to each check box when the workbook is opened.

With Form Control check boxes you just set the OnAction property, which specifies the routine to run when a check box is clicked and this setting persists when the workbook is saved.

The code below is for Form Control check boxes.

Public Sub Set_OnAction_CheckBoxes()
    
    Dim cb As CheckBox
    
    'Set the OnAction property of all checkboxes on the active sheet to "CheckBox_Click"
    
    For Each cb In ActiveSheet.CheckBoxes
        cb.OnAction = "CheckBox_Click"
    Next
    
End Sub


Public Sub CheckBox_Click()

    Dim cb As CheckBox
    
    Set cb = ActiveSheet.CheckBoxes(Application.Caller)
    If cb.Value = xlOn Then
        If MsgBox("Do you wish to continue?", vbYesNo) = vbYes Then
            MsgBox "Continue"
            'Put 'continue' code here
        Else
            'Don't continue - untick the box
            cb.Value = xlOff
        End If
    End If
    
End Sub

First, run the Set_OnAction_CheckBoxes routine. Now when any check box is clicked it runs the CheckBox_Click which displays the Yes/No warning if the box is ticked and if No is clicked the check box is unticked.

FYI, right clicking on the checkbox does not open the vba box for me, which I have seen as a way to enter it.

Click Design Mode on the Developer tab and then right-clicking on an ActiveX check box allows you to access its properties and code.

 

[Beks]

but the code does really that .

Form Control check boxes are generally simpler to use than new (ActiveX) check boxes. Do you need ActiveX check boxes?

With multiple ActiveX check boxes on a sheet you would need a class module to handle the click event from any check box and code which assigns this class module to each check box when the workbook is opened.

With Form Control check boxes you just set the OnAction property, which specifies the routine to run when a check box is clicked and this setting persists when the workbook is saved.

First, run the Set_OnAction_CheckBoxes routine. Now when any check box is clicked it runs the CheckBox_Click which displays the Yes/No warning if the box is ticked and if No is clicked the check box is unticked.

Thank you very much! This does work if I use the form control checkboxes.

The reason I wanted to use the new checkboxes (aside from being easier to insert) was for the true/false being in the cell reference automatically. So I didn't need to cell link each checkbox, use a hidden reference column etc. for conditional formatting and so on when checking the box.

I did find a vba code for linking cells to adjacent columns but now my sheet runs really slowly, even after deleting that macro.

Thank you again though, this works great with a couple little extra work arounds for what I need to accomplish.