Microsoft Office Excel 2010 Check Box

[Darrenbilly]

Good afternoon,

I use a spread sheet which contains Check Boxes, that I manually tick to show a task has been fully completed. I have a basic formula that calculates returned items, and once it reaches 0 (all returned) I tick the box. I wish to have the Check Box auto-tick itself once the returned items count reaches 0. I'm damned certain this is possible, but beyond my abilities. I know you can have formulas to display text once a Check Box is ticked, I am kinda trying to do it the other way around.



Thanks for any help

Darren

 

[cliffordcooley]

I'm damned certain this is possible, but beyond my abilities.

You are correct, it is possible.

What you need to do is learn the Visual Basic for Applications coding in Excel. Then you can add code that runs every time the sheet calculates (basically when something changes on the page). The code can check the status of a cell and change the status of another cell (or object in your case) based on criteria you have coded.

I'm not a programmer but playing with Excel Macros (VBA coding) and manipulating excel cells is a long time hobby of mine.

The easiest way to learn is to record Macro's and then study the code. It is easier learning the code if know first hand what it does. After you are familiar with how the code works, you will be surprised at how easy it is to code your own macros. Once you have the macro working for you, it is easy to move the code and have it run every time the sheet loads, calculates, closes, or several other possibilities.

 

[Darrenbilly]

It's quite frustrating that by assigning the Checkbox to the relevant Cell, if it contains 'True' then it will tick the box. All I need to do is change the rule so that it checks the box upon reading '0' in the cell...! I cannot work out how to do that, if it's possible to change the rule/relationship between the checkbox and its assigned cell?

 

[cliffordcooley]

Let me look at it further, I'll see if I can make sense of it.

 

[Darrenbilly]

Let me look at it further, I'll see if I can make sense of it.

Much obliged, thank you.
It seems so simple in ones mind. Simply IF CELL X = 0 then BOX = TICKED! If only code was THIS simple!

 

[cliffordcooley]

The checkbox is looking for "TRUE" (1) or "FALSE" (0) criteria from the cell. The checkbox will not function if the reference is anything but true of false. And if you toggle the checkbox, it will also toggle the value of the cell (TRUE or FALSE), over writing anything previously in the cell. It appears the check-boxes are used to control the cell values not the other way around. That is short of creating code to control objects based on cell values.

 

[cliffordcooley]

It may not be written correctly, but I think it will work for you. If you have any questions, I'll do my best to answer. The CBoxLinks Array should be the only line you need to edit.

Steps:

1. Open a new workbook
2. Right click any of the sheet tabs and select "View Code"
3. Paste the following code to the worksheets code page and then close VBA
4. Make a few changes in the worksheet to initialize the code
5. The code is written in "Worksheet_Change" so there must be a change before it will parse

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CBoxLinks As Variant:  Dim Cel As Variant:
Dim CBExist As String:     Dim CBox As Object:

' - - - - - - - - - - -Each cell address is all that needs to be edited - - - - - - -
' - - - - - - - - - - - - - - - Add as many addresses as needed - - - - - - - - - - -
CBoxLinks = Array("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10"):
                                             ' cell list of all linked Checkboxes

' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' - - - - - The following section of code is what controls checkbox status- - - - - -
For Each Cel In CBoxLinks:                   ' parse all links stored in CBoxLinks Array
CheckAgain: CBExist = "No":
For Each CBox In ActiveSheet.Shapes:        ' parse all objects within Active Sheet
  If CBox.Name = Cel & "_CB" Then            ' control only objects where CBox Name
   CBExist = "Yes":                          ' matches a link stored in CBoxLinks array
   If ActiveSheet.Range(Cel).Value = 0 _
   Then CBox.ControlFormat.Value = xlOn _
   Else CBox.ControlFormat.Value = xlOff:    ' toggle object state depending on the cell
  Else: End If:                              ' value of link stored in CBoxLinks array
Next CBox:                                  ' start loop over and parse next CBox
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' - - - - - - - The following section of code is ignored if object exist- - - - - - -
If CBExist = "No" Then                      ' if not exist, create and name Checkbox
  For N = 0 To 5                             ' finding first numeric character in address
   If IsNumeric(Right(Left(Cel, N + 1), 1)) _
     Then NumLen = Len(Cel) - N: Exit For    ' cropping line number from cell address
  Next N:
  CBTop = Right(Cel, NumLen) * 15 - 15:      ' using line number for top positioning
  ActiveSheet.CheckBoxes.Add(110, CBTop, 50, 15).Select:
  Selection.Name = Cel & "_CB":
  Selection.Characters.Text = Cel:
  Msg = "Checkbox " & Chr(34) & Cel & "_CB" & Chr(34) & " does not exist" & _
    vbCrLf & "and will now be created.":
  MsgBox (Msg): GoTo CheckAgain:             ' message box prompting user
End If:
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Next Cel:                                    ' start loop over and parse next Cel
End Sub

 

[Darrenbilly]

Hold up... I believe I have realised the error of my ways, let me tinker some more. Only read the praise in the below for now...

Woah... That's a pretty awesome code right there - thank you. It works in principle, however there are a few things that... break it. I have been trying to tweak it.

There is a basic formula involved to reached the '0' figure, which the CB looks at to know if it should be checked or not. I.e. A1-A2 = A3. A3 is the cell in which the CB is looking at, once it equals 0, it should tick. However it seems that the coding overwrites the formula I enter into A3 and adds 'true/false'. Is there a way around that, or do I have to adapt to this?

Don't get me wrong, I appreciate immensely the work you have already done for me, I feel incredibly close to the desired outcome! Even if I have to tweak my spread sheet to work with the coding. I wonder if there's a way for the code to detect 'true' in A3 and then convert that to a 0. So 'IF A3 = 'true' show '0' kinda thing...

Thank you, oh Sage one

 

[Darrenbilly]

It may not be written correctly, but I think it will work for you. If you have any questions, I'll do my best to answer. The CBoxLinks Array should be the only line you need to edit.

Steps:

1. Open a new workbook
2. Right click any of the sheet tabs and select "View Code"
3. Paste the following code to the worksheets code page and then close VBA
4. Make a few changes in the worksheet to initialize the code
5. The code is written in "Worksheet_Change" so there must be a change before it will parse

Crikey. You've bloody cracked it! I realised I was making a silly mistake, each CB name (A1-A10) were linked solely to the corresponding cell. So when I was trying to Assign it to another, it was getting confused. Now I have changed the code to reflect the cells I wish the CB to look at for '0'. It works an absolute treat!

I send you endless thank you's, cliffordcooley.

 

[cliffordcooley]

Excellent, just excellent! I was worried you wouldn't understand well enough to implement it. I'm overjoyed at how quickly you caught on.

 

[Darrenbilly]

Your code at work!

Darren