Results 1 to 11 of 11

Thread: Can I - if a check box is not selected, cell range = zero

  1. #1

    Can I - if a check box is not selected, cell range = zero

    I have 'some' idea what I am doing, in in some cases am not sure of the terminology or the correct method, so please bear with me.

    In my spreadsheet, I want the user to use a checkbox.
    If he selects it, default values are available in cells C26, D26 - C20, D29 - C39, D39 - C42, D42. if he unchecks it, all of those cell values = zero

    I am so new to this (not sure if I am using the checkmark correctly ... I just sort of put it there beside my Subject text in cell B5)

    What information do you require from me to assist me?
    I am on Windows ME, running Office 2000

    Thank you

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    May 2004
    Oregon, United States
    Hi Irene,

    First off, what type of checkbox did you use? Did you create it from the Forms menu or the Controls Toolbox menu? A quick way to determine is to right click the object, do you see a "Assign Macro" near the bottom of the pop-up menu? If so, it's from the Forms toolbar, and if not it is from the Controls Toolbox. Let's start with that.

  3. #3
    Hi firefytr

    I used the 'forms' checkbox

    I was in Microsoft KB the other day and ran across an article about Excel 2000 not having the Controls Toolbox (and some other related). I tried to go back and find that before I replied, but was lost in their space, but I do not have that.

    I have attached a zip with a sample of my task.


  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Jun 2004
    Roseville, CA
    Right click on the check box and select cell link. Then select a cell. This will add True or False into the cell. You can then use that in a formula to change the value of another cell.

  5. #5
    Thanks DRJ, but I tried that route. it is not obvious to me how to make it work
    If I do that (cell link), I can only address one cell and even from there, in my ignorance, do not know how to modify the TRUE/FALSE to do what I want (I am an absolute newbie to VB etc)

    Please take a look at the attached spreadhseet (it has errors as this is the sheet I am using to practice - notice how the checkboxes returned "true" in only first cell, and didn't change the next one)

    My intention is, if the user deselects "global" at the top (check box beside cell B5) all global references will be deselected as well (check box beside cell "A26-global topic" will deselect, cells C26 & D26 will then return zero)
    If they are left checked, the default values I have typed in will remain.

    Is this doable?
    Am I in the right direction?

  6. #6

  7. #7
    Oh my gosh - yes - incredibly close!

    Can you tell me how you did that?

    I would like to follow your lead and tweak this some (I see that the formula is in the changing cell - I am going to see if I can follow you here and practice - hopefully I can show you what you have taught me!)

    how wonderful and brilliant you are!!

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Jun 2004
    Roseville, CA
    Glad to help as always.

    Just set all the check boxes to their own cells each, which will be TRUE or FALSE. Then use a formula like
    You can change the And to an Or if you need only one to be TRUE as well.

    Post back if you need more assistance.

    Take Care


  9. #9
    Thank you so much

    I am playing (and seemingly succeeding!) in making more checkboxes etc. and following your lead, all seems well.

    I think I have another question, but I am going to play for a bit first to see if my answer comes to me before I jump the gun.

    I will let you know how I make out.

    Thanks again - this is fantasitc!


  10. #10
    I did it!! Thanks to your kind help !! I wanted the Minimum and Maximum to reflect the selection, so I put another forumla in the Minimum cell to act on the Maximum (affected by the checkbox)

    I feel great!

    One last question for this task.

    I want to protect the Text in the cells, and the Totals from being modified, so I have locked them in the Cell properties.
    if I protect my sheet, the Checkboxes are also protected & can't be changed.
    In the Checkbox properties I have unchecked both Lock Text and Lock (and a combo)
    In the Security properties for the sheet I have tried checking and unchecking all of them (Contencts, Objects, Scenerios) but everything locks the checkboxes.

    Is there a way around this?

    Thank you once again


  11. #11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts