Program a tick box with a macro

[stuj77]

Hi. When the tick box is checked I would like ' fill in cell a2' displayed as a message. and when the tick box is unchecked I would like ' remove data from cell a2' displayed in a message. Please could anyone help me? as I have no clue with macros. Thank you so much

[vamosj]

Add the tick box, right click, assign macro, give it a name, then add this in the macro.

Make sure you note the name of the tick box you use.

Checkbox1 for example.

if Checkbox1.value = true then msgbox("Fill in cell A2") else msgbox("Remove data from cell A2")



Just doing that off the top of my head, but the code should look very similar to this.

[stuj77]

Thanks for the quick reply. I have tried this code and I get an error message saying compile error: Else without If?

[Rocky2013]

I am new in VBA also. Try this.

Sub CB1()
    If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = 1 Then
    MsgBox "fill in cell a2"
    Else: MsgBox "remove data from cell a2"
    End If
End Sub

[stuj77]

I really don't understand... Sorry for sounding dum. All I want is a message box to appear when the checkbox is ticked stating one message and appearing when unchecked stating a 2nd message. Thx

[vamosj]

Create a checkbox to use as your tick box. (Found in the developer tab)

Right click on the checkbox and in the upper left corner of the spreadsheet you should be able to see the name of it.

On the menu that popped up on the right click, select "Assign Macro"

You can rename the macro if you want but if this is the only one there, just leave it as is and then select "New"

In the visual basic window that pops up past the following code. Make sure to change the name (highlited in red) in line 1 of the code to the name of the checkbox.

The message box should now pop up whenever the status of the checkbox is changed.

Set ChkBx = ActiveSheet.Shapes("Check Box 1")  'Set this to whatever the name of the checkbox is
If ChkBx.OLEFormat.Object.Value = 1 Then MsgBox ("Fill in cell A2") Else MsgBox ("Remove data from cell A2")

[stuj77]

image.jpg this is what happens when I use the code your provided.. :/

[Rocky2013]

Does my code works for you?

[stuj77]

Hi Rocky. No. I still get the Compile error message that says Else without if.

[Rocky2013]

I opened attached excel file from post#4, run and no issue to check and uncheck to prompt the messages.

This file was created using Excel 2010.

Below are the snap shots when I checked and unchecked the checkbox.

[vamosj]

Put the ELSE back up in the same line and remove the colon after it.

If ChkBx.OLEFormat.Object.Value = 1 Then MsgBox ("Fill in cell A2") Else MsgBox ("Remove data from cell A2")

Or you can do it this way.

Set ChkBx = ActiveSheet.Shapes("Check Box 1")  'Set this to whatever the name of the checkbox is
If ChkBx.OLEFormat.Object.Value = 1 Then 
     MsgBox ("Fill in cell A2") 
Else 
     MsgBox ("Remove data from cell A2")
End If