How to attach a "checkbox" to individual rows of a dynarange table?

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • How to attach a "checkbox" to individual rows of a dynarange table?

    I've created a dynarange table looking something like this (in cells C6-I8 :(

    Source Code

    1. Date Region Subsidiary Product Quantity Cost <blank col>
    2. | {Date} | {Region} | {Subsidiary} | {Product} | =PALO.DATA() | =PALO.DATA() | <empty> |
    3. | | | |
    4. V V V V



    What I'd like to do is have a way for a user to select which rows he/she is interested in using something like a checkbox in column I.

    The problem is checkboxes can't be included in cells so I can't embed one in cell I8.

    I've come up with an ugly workaround for user selection, but was wondering if anyone has any tips/tricks they'd be willing to share for creating user selectable rows.

    SPOILER: My bad implementation of "checkboxes" for dynaranges follows:

    I created a custom format for cell I8.
    If a user puts any text in the cell, the format changes color and the user considers the cell as selected.
    I can give visual feedback by using conditional formatting to change the cell color/font color when text is present.
    Custom formats are copied when the dynarange is expanded (this is why this solution works.)

    This solution feels a bit hokey so I'm reaching out to the community to find out if someone's got a better way to do this.

    One way I can think of doing this requires attaching an OnClick() callback to cell I8(). I can have the macro do something like set the value of clicked cell to 'V' if it was originally ' ' or ' ' if originally 'V' and use conditional formatting to update the display. That that assumes I can create such a call back...

    The post was edited 2 times, last by byoshimi99999 ().

  • How do I iterate over a named range? (Palo web spreadsheets)

    I'm using a named range inside my dynarange table and it seems to be working fine.

    It expands correctly when I ask for activeworkbook()->names()->item('MyNamedRange')->refers_to()
    (Just used a __msgbox() to verify that.)

    The output is a string like 'WorkbookName'!$A$1,$A$2,$A$3..."

    I can write my own parser but it seems that there should be some built-in way to iterate over the cells in a range....
  • Hi,
    a common solution for this is using the HYPERLINK() function in combination with SHOWPICT and IF. Basically, depending on some control cell, as "display text" of the hyperlink you either show the image of a checked or unchecked checkbox. Then, when the hyperlink is clicked, you can use the transfer function of it to change the state of the control cell and thus the shown checkbox image via IF.
    Since HYPERLINK is a formula like any other, it's arguments (cell references) will be changed dynamically in the DynaRange.

    For version 6, we plan to have form elements be copied inside of dynaranges when the dynarange expands.
  • Excellent! I'll definitely give the hyperlink() and showpict a try. (Didn't see that function described.)

    When I get a chance, I'll post a snippet so that others can cut and paste.

    (And thank Laloune, I was going down the Jquery route but ran out of time... I guess its just not that rainy here... :whistling:
  • Directions for creating a simple checkbox inside a dynarange.

    Follow the directions above to create a dynarange with TWO additional cells for the checkbox. e.g. In the example above, I defined vertical dynaranges from C6:I8. You need to create a dynarange from C6:J8 (columns I and J are empty and reserved for the checkbox.
    The first cell will host the check box, the second cell will contain a string indicating is the row was selected or not.)

    Second, take 2 cells that won't be used by anything, e.g. cells F1 and F2. These will be the source for our checkbox values.
    F1 = "selected"
    F2 = ""

    Next insert the checkbox in cell I7 by typing in this formula:
    I7 =HYPERLINK("SELF",IF(J7<>"selected",SHOWPICT("/ui/lib/ext/resources/images/ux/unchecked.gif"),SHOWPICT("/ui/lib/ext/resources/images/ux/checked.gif")),"click to select this line",IF(J7<>"selected",$F$1,$F$2),J7,"","","","","","","","","","","","","","","","","","","","","","","","")

    Change the formatting for cell J7 so that the text is white (alternatively, you change the column width so its 1-2px).

    You now have functional checkboxes, one per entry in your dynarange table.

    Thanks again to Dominik for the pointer!
  • For those of you looking for a macro function to iterate over a named range, try this:

    Source Code

    1. function namedrange_to_array($namedrange){ // Given a named range // Returns an array of cells inside that range $aw = activeworkbook(); $range = $aw->names()->item($namedrange)->refers_to(); preg_match('/=([^!]*)!(.*)/',$range,$matches); $celllist = explode(",",$matches[2]); $return_cells = array(); foreach($celllist as $cell) { //array_push($return_cells, $matches[1]."!".$cell); array_push($return_cells, $cell); } return $return_cells;
    2. }



    Apologies for the ugliness, there are 2 array_pushes depending on whether not you need the fully qualified cell name or not. (By default, activesheet()->range() won't take fully qualified cell names.

    You can use the macro like this:

    Source Code

    1. function foo(){ foreach (namedrange_to_array('my named range') as $cell) { activesheet()->range($cell)->value ... }}


    Hope you guys don't mind that I'm using this forum as an informal twiki to store these little tips.
  • For those of you looking for a macro function to iterate over a named range, try this:

    PHP Source Code

    1. function namedrange_to_array($namedrange){ // Given a named range // Returns an array of cells inside that range $aw = activeworkbook(); $range = $aw->names()->item($namedrange)->refers_to(); preg_match('/=([^!]*)!(.*)/',$range,$matches); $celllist = explode(",",$matches[2]); $return_cells = array(); foreach($celllist as $cell) { //array_push($return_cells, $matches[1]."!".$cell); array_push($return_cells, $cell); } return $return_cells;}


    Apologies for the ugliness, there are 2 array_pushes depending on whether not you need the fully qualified cell name or not. (By default, activesheet()->range() won't take fully qualified cell names.

    You can use the macro like this:

    PHP Source Code

    1. function foo(){ foreach (namedrange_to_array('my named range') as $cell) { activesheet()->range($cell)->value ... }}


    Hope you guys don't mind that I'm using this forum as an informal twiki to store these little tips.
  • Hi!

    Solution with ...=HYPERLINK.... work almost perfectly! Maybe you can help?

    The problem is — if list with elements is long, I need to scroll to bottom of list, and if checking any of boxes at bottom of list, list jumps back to top.

    Very uncomfortable, If I need to check few boxes at the bottom of report. After every box checket I need to scroll back to bottom.


    Thank you!
  • Hyperlink to "SELF" without further target specification will jump to A1 cell. You can specify a target cell, though: =HYPERLINK("[SELF]B2",.....)

    In a Dynarange, you can then dynamically construct the target as some cell in the "current" row, e.g. like this:
    =HYPERLINK("[SELF]B"&ROW(),"test","test")

    If the DynaRange expands, and the link is "copied" e.g. to row 20, that means that the target of the link is B20. That way, viewport should stay in the current row.