Macro to create multiple checkbox cell links on second sheet

[nicholasprs]

Hi there.

I have approx 800 rows with checkboxes in columns C through I on Sheet1, although some rows have no checkboxes. Currently the checkboxes have no cell links assigned. I would like some help with writing a macro that assigns a cell link reference for each checkbox, but in a second worksheet.i.e the checkbox in C1 Sheet1 will end up with cell link "Sheet2!$C$1" etc. The checkboxes are already created.

I found a useful starting point for the macro in this post http://www.excelforum.com/excel-gene...check-box.html although this is a single column solution.

Thanks in advance.

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[nicholasprs]

Thanks AlphaFrog that worked for the problem that I described

For your code to create the cell reference, the checkboxes need to be already checked, however I found another macro to do that. Once the cell links are created I can manipulate sheet1 easily via sheet2.

The only issue I forgot to mention was that once in every 15 rows I have cells with 5 checkboxes in each.... Currently these cells are ignored, Is there any easy way to include cells with multiple checkboxes?

[AlphaFrog]

Thanks AlphaFrog that worked for the problem that I described

You're welcome.

For your code to create the cell reference, the checkboxes need to be already checked

That's not exactly what happens. The linked cell's don't get their first True\False value until the checkbox is changed. The code below now fills the initial value in the linked cell when creating the link.

The only issue I forgot to mention was that once in every 15 rows I have cells with 5 checkboxes in each.... Currently these cells are ignored, Is there any easy way to include cells with multiple checkboxes?

The original code tested if the checkbox was in column 3 (column C). The code below creates a link for all checkboxes on Sheet1 regardless of what column the checkbox is in. Would that work for you?

Please Login or Register  to view this content.

[nicholasprs]

Thanks AlphaFrog

Yes each column is now being correctly assigned where there is a single checkbox in each cell.

My issue above was the case where I have multiple checkboxes (5) within an individual cell.

The code ignores such cases.

[AlphaFrog]

My issue above was the case where I have multiple checkboxes (5) within an individual cell.

The code ignores such cases.

It worked for me. Where I placed multiple checkboxes in one cell, all of those checkboxes were linked to the same cell on Sheet2.

[nicholasprs]

Ok now I see it does create the reference, but in my sheet it skips a row and puts the references to Sheet1 row 15 Cb into row 16 sheet 2. I will need to invesigate further.
Also now if I check any of the 5 boxes in the cell, all are checked/unchecked. My requirement would be to keep them seperate, if possible.
It may be easier to use something other than checkboxes for that row as everything else is working well.


Thanks for all your help

[AlphaFrog]

Ok now I see it does create the reference, but in my sheet it skips a row and puts the references to Sheet1 row 15 Cb into row 16 sheet 2. I will need to invesigate further.

The cell at the very top left corner of the checkbox's outline is the linked cell. I suspect you have a checkbox that slightly overlaps the previous row.

Also now if I check any of the 5 boxes in the cell, all are checked/unchecked. My requirement would be to keep them seperate, if possible.
It may be easier to use something other than checkboxes for that row as everything else is working well.

I don't know what you're doing. So I can't help you design it.

[nicholasprs]

Hi AlphaFrog

The issue with my sheet turned out to be some rogue checkboxes hidden due to use of a table. Once I redid everything your macro worked fine.

The issue I have is that for those rows which contain cells with multiple checkboxes, instead of each checkbox having the same link reference eg C1 cell link Sheet2!$C$1, I would like to end up with a seperate reference for each checkbox with the cell. So, for example if cell C1 contains 3 checkboxes:

C1(1) - Sheet2!$C$1
C1(2) - Sheet2!$D$1
C1(3) - Sheet2!$E$1

D1 checkboxes would end up with referenced $F$1 $GH$1 and $H$1 etc. I can then do necessary calculations on a messy sheet2 and bring back into a tidy table on Sheet1.

[AlphaFrog]

You want all the references on sheet 2 row 1 starting at column C ?

[nicholasprs]

For cells that have a single check box, your macro works exactly how I need it to. My spreadsheet has data in columns c to i, incorporating a total of 7 checkboxes in each row, one per cell.

The issue is that every 15th row out of the 800 total has cells with multiple checkboxes - 3 in each cell in columns c through i i.e. a total of 21 checkboxes in that row.

Where there is more than 1 checkbox in a cell currently the macro assigns the same cell link to each of the checkboxes within that cell. I would like a seperate cell link for each checkbox.

i.e. On sheet1 c15 I have 3 checkboxes. Currently the macro assigns cell link Sheet2!$C$15 for each of the 3 checkboxes. What I would like, and was attempting to explain above, is that the first checkbox in sheet1 c15 is assigned cell link Sheet2!$C$15. The second checkbox in sheet1 c15 is assigned cell link Sheet2!$D$15, and the third checkbox in sheet1 c15 is assigned cell link Sheet2!$E$15.

On sheet 2 I will therefore end up with 7 cell links in each row (from column c to i) but once in every 15 rows with the multiple checkboxes I will end up with 21 cell links in that row (from column c to w).

I can send you a file if that is better?

[AlphaFrog]

Yes perhaps attach a file with a few examples and results.

[nicholasprs]

AlphaFrog

I have cretaed a sample at https://www.dropbox.com/s/uvvhcfn7ow...oxes.xlsm?dl=0

Sheet 1 and 2 are the result of the existing macro.

On sheet 3 I have copied a small number of rows and manually linked the cells in row 15 to sheet 4 row 15 showing what I require for the multi checkbox cells.

I think this will demonstrate what I am looking to do.

Thanks.

[AlphaFrog]

You can upload and attach a file to your reply. See the paperclip icon in the advanced forum editor.

[nicholasprs]

Stripped down version to meet file size requirement attached.

[AlphaFrog]

This could be done but it's not easy or pretty.

The biggest issue is that while there is a checkbox property that returns what cell the checkbox is in, there is no checkbox property that indicates where within that cell a given checkbox is located (as far as I know). So for your multi-checkbox cells, there is no easy way to determine which is the left-most checkbox within its cell and which is the right-most checkbox. And you want to link to cells on Sheet2 by position left to right to match the checkbox positions in its cell.

The way the code loops through checkboxes is in the order they were created. For single checkbox cells that order doesn't matter. But for multi-checkbox cells, there is no way to tell which is the left-most checkbox. And the first checkbox the code loops through is just the first one you created in that cell. It could be any one of the three regardless of its left-right position in that cell. In fact, with all your multi-checkbox cells in the example file, you created the checkboxes from right to left; backwards of how you want them linked to Sheet2.

It would actually be easier to have the code create both the checkboxes and links at the same time. At least that way the order, positions, and links are controlled and coordinated by the code.

I can't offer any real alternatives to do this because you have not explained what this is all for. Perhaps three optionbuttons could replace the three checkboxes. The optionbuttons would return a value of 1,2,3 to its linked cell depending on which option is selected. The difference is that only one of the three options can be chosen. Or maybe a dropdown list with three choices might work?

[nicholasprs]

Thanks for looking into this AlphaFrog.

I think I have a workaround. I have simply merged 3 columns into a single cell for the first 14 rows and left the 15th with 3 cells. Having run your macro, each checkbox is now assigned correctly, with an individual reference for all checkboxes on row 15.

It was staring me in the face all along! I appreciate all your help with this.