Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use checkboxes to sum total (EXcel 2003)

    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
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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"

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use checkboxes to sum total (EXcel 2003)

    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.
    Regards,
    Rudi

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    <hr>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)<hr>
    . 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
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use checkboxes to sum total (EXcel 2003)

    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
    Regards,
    Rudi

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    Hi Rudi,

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

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

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

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use checkboxes to sum total (EXcel 2003)

    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!!
    Regards,
    Rudi

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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?
    <hr>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.
    <hr>

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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.
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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.
    Legare Coleman

  15. #15
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use checkboxes to sum total (EXcel 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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