Get checkboxes value based on row

**didinside** · 10-15-2012, 09:06 AM

Hello everyone,

I'm trying to find a way to check the value of several checkboxes on the same row without using their names ?

Description of what I have and want to do :
I have a excel (2010) sheet with several columns (5 max in general) and a lot of rows.
- First column is a task description (text format)
- Second column and next are equipments (servers) and cells are filled with a checkbox.
Goal of this is to tick a checkbox when the task has been done on the specified equipment.
- when a checkbox is ticked, color of the task description's cell will change from white to orange => at least the task has been done on 1 equipment
- when all checkbox (on the same row) are ticked, task description will change from orange to green => task has been done on ALL equipment

I know how to change the color of cell based on the row where I ticked the checkbox.
What I don't know is how can I check the value of others checkboxex on the same row ?

Exemple : I tick the checkbox on C3, but I also have others checkboxes on D3, E3, F3, G3. For this, I must know the status of all these checkboxes in order to assign a color on my "Task description's" cell (in B3).

As I can have a lot of rows with hundred of checkboxes, I would like to have a macro that works for everything.

Many thanks for your idea.

Didier

PS : exemple of my install_guide.xls has been attached.

**AdLoki** · 10-15-2012, 10:12 AM

You could try calling something like this when a checkbox is clicked:

Please Login or Register  to view this content.

It assumes that all rows containing checkboxes will be the same height and that the first such row is row 4

**didinside** · 10-15-2012, 11:51 AM

Hi,

Thanks for the reply.

I'll check deeper this evening. I've quickly run your code but it doesn't work as it should.
One row is change from white to orange but not on the same row as the checkbox ticked.

**AdLoki** · 10-15-2012, 12:24 PM

My mistake. Try replacing int(<...>) with round(<...> ,0), as follows:

Please Login or Register  to view this content.

I just tested this and it worked.

**didinside** · 10-15-2012, 01:48 PM

Indeed it works but the cell that is colored is not on the same row

ok1.jpg
ok2.jpg
ko1.jpg
ko2.jpg

If I tick the checkbox on C13, cell on B25 will become orange (with checkbox on D13 untick)
If I tick the checkbox on D13, cell on B25 will become green (so indeed you procedure works fine to checks all checkbox on one row).

But instead of coloring the cell on B25, it should be B13 (same row as the checkbox).

I'll dig into your code to see why it doesn't work as it should. Maybe something wrong on my side.

Anyway, big big thanks for your help, I'm moving forward.

Didier

**AdLoki** · 10-16-2012, 05:31 AM

Hi Didier

I've only just had a chance to take another look at this. I took your original file and deleted all but one of the checkboxes. I then pasted that checkbox into many rows, selected them all and allocated the macro from my previous message. It works fine in Excel 2010 - see attached: Install_guide_test(AdLoki).xls

I don't know if you are aware but if you copy a checkbox into a cell, you can then copy and paste the cell and the checkbox will be copied. That way, the position of each checkbox will always be the same relative to the cell it is in. If you use the Selection Pane you can then select all of the checkboxes and allocate the macro to them in one go.

Hope that helps!

**didinside** · 10-16-2012, 07:00 AM

Hi AdLoki,

Thanks for the tips

.

About your file, yes it works fine for everyrow with the original row height.
But if you change the row height of one row, then there is a problem for all the row after that one.

**AdLoki** · 10-16-2012, 08:15 AM

Hi Didier,

Yes, one of the assumptions is that all of the rows containing the checkboxes are the same height. If you are going to have variable row heights, use the following macro instead. It will work for any row, regardless of height. You must ensure that there is text in the Remark column for the last row with a checkbox in it.

Please Login or Register  to view this content.

Get checkboxes value based on row

LinkBack

Thread Tools

Rate This Thread

Display

Get checkboxes value based on row

Re: Get checkboxes value based on row

Re: Get checkboxes value based on row

Re: Get checkboxes value based on row

Re: Get checkboxes value based on row

Re: Get checkboxes value based on row

Re: Get checkboxes value based on row

Re: Get checkboxes value based on row

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions