Use checkboxes to sum total (EXcel 2003)

Topic

New Reply

I have a worksheet with In and Out amounts in Col C and checkboxes in each cells next to it. I want to be able to :

1) Auto checked the checkboxes of Open Bal, and those Accept status
2) Sum up the running total amounts for those amounts which checkboxes are checked at any point of time during the day.
This totals will change during the day as the unchecked may be checked due to receiving the amount or paying out the amount as we find a match in the
InterBank money market
3) the list of entries are imported from another application and are variable from day to day and may run up to 1500 entries per day, is it possible to have the
checkboxes generate in the cells next to the amounts?

Thanks in advance

regards, francis

Reply | Quote

Replies

I am not clear exactly what you need could you elaborate?

One comment: if you are going to use checkboxes and want formulas to be able to read them, you have to change the “LinkedCell” property of each checkbox to refer to a cell and then have the formula read the “linkedcell” (it will be True or False). Formulas can not read the checkbox directly. You can use SUMIF to calculate once you have this setup (sum the values based on the true/false values).

The autochecked ones you could link to cell which contains a formula (which “autochecks”) that yields True/False and have the checkbox not enabled (so the user can not change it)

If you are going to input, you could use a macro to create the checkboxes and link them, but it may be easier to just work directly with the True/false in the cells or have the user add an “x” to a cell and have the formulas use this instead of going to the trouble of creating 1500 objects in the sheet.

Steve

Reply | Quote

Steve,

thank for the enlighten guide on using SUMIF formula. I am able to use this to sum cells with a check in the checkboxes. That’s clear
point 2 of my original post. I am now working on the “autochecks” and will post back if I can’t figure it out.

Good suggestion on using the “x” in the cells with a formula. I will need to find a way to make the cell appear as through it was checked by “x”

cheers, francis

Reply | Quote

If you format the “C” columns font as Wingdings and have the person insert an “x” into it, it appears like a checkbox with an “x” inside it. This has a great visual display simulating the the checkbox objects you were using.

Another idea you could use is some event based code that will change any value entered into the cells of the “C” column to an “x” when someone enters a value into the cell. Only an “x” should be entered, else you get a myriad of other Wingding symbols that would not look like checkboxes. The code: Something to the point of if the target cell value is not equal to “x”, then make it equal “x”, if there is something typed into the cell.

Reply | Quote

[indent]

---

The autochecked ones you could link to cell which contains a formula (which “autochecks”) that yields True/False and have the
checkbox not enabled (so the user can not change it)

---

[/indent]. I am unable to figure this out.

As suggested, if I have the user add an “x”, how can I have this worksheet when open always show in Col C as a box
( this was suggested by Rudi using Wingdings fonts ) and that the only character to be input in those cells is an “x” only.
I have tried Data > Validation but its not working as expected. see attached

thanks

regards, francis

Reply | Quote

1) in C5 you could enter something like this (it would not have validation, since you don’t want people to enter, but have it read automatically):
=IF(D5=”Accept”,”x”,””)

2) The validation in C6 would not be ‘x’ as you entered but:
=C6=”x”

Note if you are only going to allow entry into certain cells you might consider unlocking the cells they will place an ‘”x” and then protect the sheet. The “autochecked” cells would remain locked…

Steve

Reply | Quote

Hi Steve,

Thank for the formula and Vaildation is something I have not tried before.
Is it possible that when user click on the cells in Col C, it will input a “x” without entering a “x” from the keyboard? Seniors like the idea of checkboxes.

TIA

regards, francis

Reply | Quote

Hi Francis,

The code may need a bit of rounding off, but based on your request to just click in the cell to add the X, you will need to use code to do this. I put this together. It works, but I think it needs a bit more work which I am not sure to do? You will notice that if you click in a cell it puts the X in it. If you click on a cell with an X, it takes it out again. But, if you select a range of cells, it debugs??!!

The code must be inserted into the sheet module. To do this, copy the code, right click on the sheet tab in excel, and choose View Code. Paste the macro into that module. Then it is active.

Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Range, myR As Range
Set myR = Range(“C4:C13”)
Set isect = Application.Intersect(Target, myR)
If isect Is Nothing Then
Exit Sub
Else
If Target.Value = “” Then
Target.Value = “x”
Else
Target.Value = “”
End If
End If
Set isect = Nothing
Set myR = Nothing
End Sub

Reply | Quote

That code will put a “x” into the cell if the active cell is a different cell and the user clicks in the cell. However, if the active cell is is already the cell (lets say the user selected the cell and then deleted the “x”), no “x” will be put into the cell. Also, if the user selects the cell by other means (tabs into it or gets there after pressing return, or by the arrow keys for example), then the “x” will be put into the cell. I think this would be a little too unpredictable for most users. Wouldn’t it be better to use the BeforeDoubleClick event? I would also recommend disabeling events before putting the “x” into the cell and then enabeling them again after. Putting the “x” into the cell will trigger the worksheet change event and possibly the worksheet calculate event which could result in a cascading event loop. If you do that, then you would also need to force a recalculate after reenableing events if there are formulas that are dependent on the cell where the “x” is being placed.

Reply | Quote

Hi Legare,

Sorry, I don’t understand you what you means, it is out of my sync on those that you have suggested which I believe that are something really useful here.
I have tried on the tabbing and other means but nothing happen to other cells, I think basically the codes is confine to col C and I have the worksheet
protected and the validation on.

Especially on this which I am very concern with, would you elaborate a bit more?
[indent]

---

Putting the “x” into the cell will trigger the worksheet change event and possibly the worksheet calculate event which could result in a cascading event loop. If you do that, then you would also need to force a recalculate after reenableing events if there are formulas that are dependent on the cell where the “x” is being placed.

---

[/indent]

regards, francis

Reply | Quote

As written, the code is confined to having cells in the range C4:C13 selected. However, the code assumes that only one cell will be selected, and that no cells outside of that range will be included in the selection. If the cells in the range C4:C13 can be selected by clicking in them, then they probably can be selected by tabbing into them. In other words, I think there are a number of possible problems with the code as written. You will have to more precisely specify how you want this to work (see my other reply) before code can be written without these problems.

Reply | Quote

Hi Legare,

You are correct that tabbing and other means will put an “x” in the focus cell/s. On your other suggestion, I will need to test this out with the users and get
back later on. The users are suppose the click one cell at a time as they confirm the matching status in the InterBank money market.
Thank for alerting on this. I would be very keen to understand the potential shorfalls of this code.

regards, francis

Reply | Quote

Hi Rudi,

Thanks for the codes, Its work great. Is there’s any formula that can do this?

[indent]

---

But, if you select a range of cells, it debugs??!!

---

[/indent]

I think using the error handler will handle this, ie On Error Resume Next

cheers, francis

Reply | Quote

You are correct. Inserting On Error Resume Next just after the Else statement works well. However, Legare’s reply has me concerned about all the problems he mentioned!!

Reply | Quote

No, using On Error will not “Handle this”, it will just hide the problem. Using the Worksheet BeforeDoubleClick event will prevent the problem from happening. If you really do want to use the Worksheet SelectionChange event, then you will need to define what you want to happen if the user selects more than one cell in the given range. Do you want all of the selected cells to get the “x”? None of the cells? One particular cell, if so which one? Something else.

Reply | Quote