Forum Discussion
When inserting or deleting a row the form controls and ActiveX controls are moved.
If I have understood it correctly, you would like to be able to delete and move previous / existing ActiveX checkboxes in the existing worksheet / workbook.
Here is my little suggestion for a solution
To be able to work with controls, you have to enable the Developer tab on the ribbon.
Here is a small proposal for a solution, the cell reference must be specified for each box individually.
Example: If the check box should always be displayed in line 8, then also place it in line 8.
- Control element, right mouse button, "Format control element ..."
- Click on "Properties"
- Object positioning
Click on the selection point "Dependent on cell position and size".
- Press "OK".
Now you can delete the lines or cells and the check box is deleted / shifted with.
Delete controls on a worksheet
To be able to work with controls, you have to enable the Developer tab on the ribbon.
If one or more controls is an ActiveX control, do the following:
Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
Select the control or controls that you want to delete.
For more information, see Select or deselect controls on a worksheet.
Press DELETE.
Additional Information:
Group, copy, move, or align controls on a worksheet
After you add Form and ActiveX controls to a worksheet form, you may want to group, copy, move, or align controls to create a well designed, user friendly form.
Hope I was able to help you with these information / links.
NikolinoDE
I know that I know nothing (Socrates)
Hi NikolinoDE
First of all, thanks for your efforts to help me.
But no, this is not what I'm trying... I don't want to delete anything. The problem is that now, when you add or delete a row that it is previous to the one that has the form control, the form control behaviour has changed. I'll try to explain myself a little bit more...
The problem is that in addition to moving down a row (desired behaviour when inserting a row above), the form control moves down one more row. In the example I put in the images, the checkbox and the linked cell were in row 4, when inserting a row above, they go down to row 5 (so far, correct, and is the behaviour it had before), but the checkbox, actually goes down to row 6 (which is only observed when you click on it or when you save and reopen the document), as seen in the 3rd image.
Please, can anyone figure out what might have changed to make it behave differently?
Thanks
In this case you want the ActiveX element to adjust the check box to the height of the picture and not to the line. At the same time, these should remain in the same line number.
Maybe it could be done with VBA code, but it is time consuming. Time that I cannot provide at the moment.
What I could also recommend you make the same settings ...
- Control element, right mouse button, "Format control element ..."
2Click on "Properties"
Object positioning
- Click on the selection point "Dependent on cell position and size". Press "OK".
... also with the pictures regardless of the check boxes, so pictures and check boxes will move at the same time.
Thank you for your patience and understanding
Hi, NikolinoDE
Thank you for your efforts. I already have the "Dependent on cell position and size" configuration on properties...
It is very strange because, without changing anything, the excel behaviour changed from one day to another. I even tried to roll back windows actualizations and nothing changed... weird.Finally, I found how to hack this strange behaviour with a VBA macro. I'll share here for the ones that have the same problem:
1) First of all, I found that if I selected one form control on design mode and I simply moved it up and down, when I left design mode it had recovered its functionality and no longer appeared duplicated or moved after saving the Excel archive. It makes the behaviour even weird, I know... but it solves that.
2) To make it easier to move all the form controls that were affected by the strange behaviour at once, I selected them and with right click, I had chosen to group them. Doing this, Excel, automatically creates a name for the group of items. In this case the name was "Group 22".
3) To automate the process, I added this to a macro:
ActiveSheet.Shapes.Range(Array("Group 22")).Select
Selection.ShapeRange.IncrementTop -0.5
Selection.ShapeRange.IncrementTop 0.5This code selects the group of form controls, and move them to the same position (the previous one, disappearing the duplicate form control on the new position).
I would prefer to avoid that, but it's the only solution I had found. If anyone have a better option, please, share it.
Thank you
