PDA

View Full Version : Solved: Can I - if a check box is not selected, cell range = zero



MoosePuck
08-06-2004, 11:18 AM
Hi
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
Irene

Zack Barresse
08-06-2004, 02:27 PM
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.

MoosePuck
08-06-2004, 02:57 PM
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.

Thanks
Irene

Jacob Hilderbrand
08-06-2004, 03:35 PM
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.

MoosePuck
08-07-2004, 06:36 AM
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?

Jacob Hilderbrand
08-07-2004, 06:57 AM
Like this?

MoosePuck
08-07-2004, 07:25 AM
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!!:kiss

Jacob Hilderbrand
08-07-2004, 07:41 AM
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
=IF(And(A1,B1,C1),ValueIfTrue,ValueIfFalse)

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

Jacob

MoosePuck
08-07-2004, 07:48 AM
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!:hi:

Irene

MoosePuck
08-07-2004, 03:41 PM
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.
BUT
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.

:dunno Is there a way around this?

Thank you once again

Irene

Jacob Hilderbrand
08-07-2004, 04:28 PM
Continued Here

http://www.vbaexpress.com/forum/showthread.php?t=635