Making a check box be "on" or "off" depending upon the value in another cell

rmccafferty

Board Regular
Joined
Jul 16, 2009
Messages
60
I am spoiled by some of the functionality in Access when I use Excel, and am also amazed at what people have figured out how to do in Excel.

What I would like to to in Excel:
When I leave the current cell,
1. evaluate the contents of the cell I am leaving (essentially determining if it is empty or not)
2. If it is empty, do nothing
If it is not empty (or maybe if it has a number value in it)
3. Then uncheck a box (a check box form control)

In Access, I would enter code in On Leave property
It would essentially change the value in the text box that would be named or have some other unique method of identifying it.

So I have two issues:
1) In Excel, I do not know to make a macro fire upon leaving a cell
And
2) I do not know how to refer to the properties of a specific check box control. that is, there will be many of them in the spreadsheet and I do not see how to name them. Clicking of properties for the check box gave no hint of it.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Maybe ...

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static r As Range
    
    If r Is Nothing Then
        Set r = Selection(1)
    Else
        Me.Shapes("Check Box 1").DrawingObject.Value = IIf(IsEmpty(r), xlOn, xlOff)
        Set r = Selection(1)
    End If
End Sub
Tweak for the logic you want.
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,523
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
shg,

Very nice solution. I learned something today!!!

M.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Why, thank you, Marcelo :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,787
Members
416,982
Latest member
lisam77

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