Checkboxes move around on their own...are they haunted?

I've seen this issue posted elsewhere, my spreadsheet has a series of checkboxes, and after saving and reopening the workbook, they will sometimes move around. I saw one possible solution was to anchor them in Worksheet_Open:

PHP Code: 

With Sheets("sheet1").Shapes("CheckBox01")
    .Top = Range("S4").Top
    .Left = Range("S4").Left
End With 


This would be great, except I have a wrinkle. When the user clicks the checkbox, they can either submit their data, or create a new row, to add more information to their record. So in the optimal worksheet, they have 24 rows of data, but if there are issues, they can add as many as are needed. So I can't anchor them like this, or they run the risk of appearing in the wrong row.

I have tried changing the property to "Don't Move or Size With Cells", this has had no effect. Any other suggestions for busting the checkbox ghosts?

Hi fenfool,

When I create CheckBoxes, I often add the Cell Address to the name e.g. ('CheckBoxS4'). I can then strip out the 'CheckBox' text and get the address. This technique might suit your adjustments in Workbook_Open(). You could loop through all Shapes, and adjust the location of the CheckBoxes, based on the cell address in the name.

The code associated with post #3 in the following thread may help you: http://www.excelforum.com/excel-prog...ml#post4019179

Lewis

I ain't afraid of no...ok, I am afraid of Excel ghosts.

I see what you are doing, but I think I have a problem. When a new row is added by the user, the rest of the rows get bumped down one, so:
S4 - Checkbox01
S5 - Checkbox02
S6 - Checkbox03

becomes:
S4 -Checkbox01
S5 - Additional row related to Checkbox01
S6 - Checkbox02
S7 - Checkbox03

So I can always count on the column being S for these checkboxes, but the row will change...the first box will always be S4, but if they need to add an issue related to this data, the rest of the checkboxes will all be bumped down one (there are always 24 boxes, new rows do no get a new checkbox). Multiple issues could be reported for each record, so I can't establish a permanent row value for them. The issue has never caused them to float up and down, it's always side to side, so I was wondering if there was a way to set that position for all of the boxes. Maybe set the .left value at something reasonable...I'm not entirely sure how to do this, and I'm not even sure if this is even possible.

I was doing:
.Left = Range("S4").Left

Before...can this be used to ONLY set the left value, like .Left = 1230; no Top value? I've been trying to get something like this to work, I can't quite figure it out.

I also tried grouping, and a few other things, this part where the row is added is becoming a thorn in my side, throwing everything else off.

Can't you swap them out for forms controls? On the assumption that theyre not

You're better off not using ActiveX controls on worksheets at all. They're incredibly unstable have a look here https://www.google.co.uk/webhp?sourc...eet%20controls and by using them, you're asking for trouble.

Forms controls are much more reliable - they're in the same dropdown as the ActiveX ones, but are designed for use on worksheets - so you don't run into any stability issues, you can do most of the same things with them too and you can use them without using code.

I would agree, but this is an inherited project, and there is a lot attached to these. Clicking on them first gives the user the option to add a row; if they say yes, a row is added. If no, it runs an extensive error check to make sure all the cells necessary have been filled out correctly. Then it runs the data transfer. I've been trying to use a form control, but it's not letting me attach the macro. I have a dozen or so of these to work with, so I think I'm stuck. Honestly, if they would stay put, they would work fine.

To assign a Macro to a Forms Control use code like the following in an Ordinary Code Module, Not in a Sheet Module.

Please Login or Register  to view this content.

To assign a Macro in a Sheet Module use code like the following where Sheet1 is the Sheet 'Code Name', which may or may not be the same as the Sheet 'Tab Name' that is visible to the user.

Please Login or Register  to view this content.

Lewis

Thanks LJMetzger, I'm going to give this a shot. If those boxes would just stay where I put them, I could have this done by now. Anyway, the next step is to have a button that checks the data...it will search the data collection Workbook for the record identifiers, if it finds them, a radio button, or maybe something else will be checked, if not, they will remain empty. This will let the user know what has been done, and what is left to do; if they see an unchecked button where they thought it was already processed, they will know to contact the administrator, or if a button shows as already having been processed when it shouldn't be. We'll see how it goes. Thanks again for the help!

Just as a late follow-up...there was code in a module that I did not see that was placing them, so everything I did was fighting it. I killed the code, and everything has been fine. So the answer was I'm an idiot. Thanks all!