Need Advice on how to Increment ALL checkboxes at once or a better formula for my project

[Kapi187]

I have a spreadsheet for ordering my IT equipment.
I have it so when I check a box off it Dashes out the columns to the right of the item this indicates to me that I did not order that item.

My issue is when I had to add a new item into a section I right click and select to add line.. it moves all my columns down and all formulas increment with the exception of my CHECKboxes.
I have to go into VBA and manually increment each line after the line I added... I have a few hundred items so it can get a little tedious updating each line everytime I add an item.

Is there any way to increment checkboxes globally? Or is there a better formula someone can suggest?

I'm attaching a sample of my worksheet with my formula im using.

Please help!

[cytop]

Don't use Checkboxes... Have a read of http://www.ozgrid.com/forum/showthre...389#post653389

[Kapi187]

Thanks ... that definitely looks like something I could use... however I tried using it and when I click on a line and added the whole line the entire process crashes and I can no longer double click on the cell to insert the checkboxes. Any thoughts?

[cytop]

There's nothing in that to crash an entire process... any further details?

[Kapi187]

cytop,

Not really... I'll try to explain again...
So I followed his advise.. went to Column E (highlighted whole column) right clicked... Formatted as Wingding, justified and centered... Font 14 size
Opened up VBA... pasted his VBA code in Worksheet and it worked.... I then wanted to add a line to see if it still worked... So i clicked on a line number.. then right clicked on the number and selected "Insert"... in my VBA window I get a "Run-Time error '1004': Application-defined or object-defined error. I select Debug and it highlights the line in the code that reads " If Target.Column = 1 And Target.Offset(0, 4) = vbNullString Then "
If I hit cancel and undo when I go back to my spreadsheet I can no longer click on column E to put a checkmark...when I doubleclick on a cell it just selects it where before the script would put a checkmark in the cell.

[cytop]

OK, so it bugged with EnableEvents turned off... Type

Please Login or Register  to view this content.

in the Immediate Window in the VBA dev environment and press Enter. That should cure that

A couple of other things:

The code is meant as an example. Tuening Event handling off means Error Handling should be added if it is going to be used in a production environment. Apologies for not pointing that out.

If you're using 2007+, don't format an entire column; just the likely range you're going to use. Reason for this is to prevent file bloat where unused (and unlikely to be used) cells are formatted.

The link was meant as an example of how to simulate check boxes using a standard font - let me have a look at your workbook and see if I can incorporate it.

[Kapi187]

cytop... thanks.. Yeah I know it was meant as an exampled.. I just wanted to figure out how the example worked before I actually tried to implement it into my workbook.
I have an example of my spreadsheet in my first post. I'm going to play around with the VBA code a little more with your advice and see if I can figure anything out. If you can incorporate it into my example than I would appreciate it... Thanks cytop!

[cytop]

Attached...

[Kapi187]

Thats awesome Cytop! Thanks!... It definitely helps point me in the right direction...I need to figure out how it will let me add a line in a particular section without deleting it.... also, do you know if using this script, theres a way for me to add a check mark next to the section "title" so it "--" (Dashes) out the entire section and vice/versa?

[cytop]

... add a line in a particular section without deleting it

Not sure what you mean by that.

...so it "--" (Dashes) out the entire section

Wouldn't be difficult - If you can't, I'll knock something up later.

[Kapi187]

Hey Cytop... So I'm trying to add another line in section 2 but as soon as I add a line it deletes it ...not sure how to stop this from happening and still allow me to add a line and have the "check" mark portion work with that new line.

[cytop]

Comment out the line

Please Login or Register  to view this content.

in the WorkSheet_Change Event handler...

This was added to prevent the users 'deleting' a check box in Column 1 simply by typing something - it should be added back in if this makes it to a release.

[Kapi187]

Cytop , this is working great!... Thanks so much... I'm still having a little issue figuring out how to make it so when they check off the section it dashes out the whole section .... I think I would need to make a if/then statement that would clear the check for the range of cells I want to dash out but do I have to enter in each line? I'm a little confused with that..