TechSpot

Microsoft Office Excel 2010 Check Box

By Darrenbilly
Feb 5, 2015
Post New Reply
  1. 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
     

    Attached Files:

  2. cliffordcooley

    cliffordcooley TS Guardian Fighter Posts: 8,560   +2,901

    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.
     
    Last edited: Feb 5, 2015
  3. Darrenbilly

    Darrenbilly TS Enthusiast Topic Starter Posts: 161

    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?
     
  4. cliffordcooley

    cliffordcooley TS Guardian Fighter Posts: 8,560   +2,901

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

    Darrenbilly TS Enthusiast Topic Starter Posts: 161

    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!
     
  6. cliffordcooley

    cliffordcooley TS Guardian Fighter Posts: 8,560   +2,901

    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.
     
  7. cliffordcooley

    cliffordcooley TS Guardian Fighter Posts: 8,560   +2,901

    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
    Code:
    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 likes this.
  8. Darrenbilly

    Darrenbilly TS Enthusiast Topic Starter Posts: 161

    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... :D

    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
     
    Last edited: Feb 6, 2015
  9. Darrenbilly

    Darrenbilly TS Enthusiast Topic Starter Posts: 161

    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 likes this.
  10. cliffordcooley

    cliffordcooley TS Guardian Fighter Posts: 8,560   +2,901

    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 likes this.
  11. Darrenbilly

    Darrenbilly TS Enthusiast Topic Starter Posts: 161

    Your code at work!

    Darren
     

    Attached Files:

    cliffordcooley likes this.

Similar Topics

Add New Comment

You need to be a member to leave a comment. Join thousands of tech enthusiasts and participate.
TechSpot Account You may also...