Checkboxes and Cell Linking - for a Macro newbie

[ariehc]

Hello,
Thank you for taking the time to potentially assist me with my dilemma.

I thought I found a post on here that would assist me (in post http://www.excelforum.com/excel-prog...eck-boxes.html)

But, when I applied it -- the file is still not behaving as desired.

I have a file with multiple worksheets that will all need to behave the similarly. Each worksheet has 2500 rows. I will just provide two for simplification. The worksheets are: 1) MakeItWork and 2) MakeItWorkCalcs. The MakeItWork worksheet is for data entry (text fields and multiple checkboxes). The MakeItWorkCalcs is for the values of the checkboxes as well a couple of other calculations.

On the MakeItWork sheet Column E and Column F each have a checkbox to indicate a different week. This checkbox needs to be in all 2500 rows. The value of the checkbox needs to be on MakeItWorkCalcs! in rows B3:B2502.

The instructions from the other post (answers provided by user Leith Ross) were extremely helpful and basic/straightforward (which is what I need). When I tried to run the macro it correctly populated all 2500 rows with a checkbox. However, only the "true" "false" indicator for the checkbox did not appear on the other worksheet in each subsequent row (B3:B2502). The resulting "true" and "false" would appear in different columns, even though I had indicated in the macro that the answers should appear in MakeItWorkCalcs!B3:B2502.

I need to be able to use this same macro in additional locations throughout my workbook. Which I don't think is a problem if I understand (roughly what/how) it is functioning in this one example.

[patel45]

a sample file with comments is better then 1000 words

[Norie]

How exactly did you use the code Leith Ross posted?

[ariehc]

Here is the file that I am working with.

[ariehc]

How exactly did you use the code Leith Ross posted?

I followed the steps provided in the post. I did not modify any of the code. In other words, in the post Leith explains how to copy the source code and paste into the macro builder and then how to apply it. I did that. The macro appeared to work in that it created a checkbox in each of the 2500 rows of the initial worksheet. However, the linked cell on the other worksheet did not appear to work as intended.

[Norie]

Do you really want 10,0000 checkboxes?

Have you considered using something like this?

Please Login or Register  to view this content.

This code, which goes in a worksheet module, will toggle between a tick mark and a blank when you double click any cell in E3:F2500.

PS If there are 2 checkboxes in each row how can they both be linked to the same cell?

[ariehc]

I don't really want 10,000+ checkboxes. I will look into your suggestion. The 2 or more checkboxes in each row would be solved by having the linked data go to separate cells (at least that is what I was thinking.) I am learning this on the fly as the staff I am working with are asking me and my boss is directing me to make work.

[Norie]

I just tried the code in the link you posted to add 2500 checkboxes for column E, it took about 10 minutes for the code to run.

Also, when it was done the checkboxes seemed pretty lethargic.

[ariehc]

I think I will go this path... Marlett looks much easier and definitely more responsive than all of those checkboxes. Thank you Norie