Can you write a formula to an ActiveX control in Excel?

Question

I am working on a spreadsheet at work. I am trying to figure out if I can set something up so if someone enters a value of 1 or higher into one cell, that the "Yes" check box will automatically check itself in another cell.

Anyone know how to do that?

Answer 1

Here's the solution to your problem

http://www.freewebs.com/swhtam/YA/entry2...

Inside the file there will be notes on the solution,

Please contact for more info

Answer 2

You can do it with a VBA macro. First, you need to do a little preparation.

Check boxes created by the Forms tool bar can be linked to a particular cell by right clicking the checkbox and selecting 'Format Control'.  

Select the 'Control' tab and enter a cell reference in the Cell Link field. You can use the cell reference that is under the Check Box and set the font to white, but True or False will print if you print the worksheet.  

I prefer to use a cell in a column way off the radar like Column ZA, then hide that column.

Next,  change the cell reference 'A1' in line 2 of the macro to the cell you want to monitor.  If you are not using ZA1 as your linked cell in your check box, change the ZA1 references to your linked cell reference and copy this macro to the clipboard:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value >= 1 Then

Range("ZA1").Value = True

Else

Range("ZA1").Value = False

End If

End Sub

Next, press ALT + F11

Double click the Sheet your check box is in (upper left), and paste the macro into the module space to the right.

Close back to Excel.

Now, whenever data is keyed to any cell in the sheet, the macro will monitor the value in your cell and check or uncheck the check box accordingly. The check box will remain checked as long as the value in your monitored cell remains greater than or equal to 1.

Note: if the check box was created with the Control Toolbox, the process is a little different.

No need to create a linked cell.  Change the 'A1' references to your monitored cell. If you check box is not named 'CheckBox1', change the check box references to the number it is, i.e. 'CheckBox2'.  Then copy this macro to the clipboard:

Private Sub Worksheet_Change(ByVal Target As Range)

If [a1].Value >= 1 Then

ActiveSheet.CheckBox1 = True

ElseIf [a1].Value < 1 Then

ActiveSheet.CheckBox1 = False

End If

End Sub

Then follow the above procedure from 'Next, press ALT + F11'.