When inserting or deleting a row the form controls and ActiveX controls are moved.

Copper Contributor

Hello;

 

Until today I have been using form controls and ActiveX controls in an Excel file where rows are added from a connection without any problem. But today it has started to show a different behaviour. I will try to explain:

 

When inserting or deleting a row above the form control or ActiveX control, it appears as duplicated when clicked (while holding down the cursor), showing the duplicate higher or lower than the original depending on whether we have added or deleted rows. Also, the more rows added or deleted, the further away from the original the duplicate appears. I attach the initial screenshot and the behaviour when holding down the cursor after inserting a row:

 

1.png

 

2.png

 

To make the behaviour even weirder, if I save the document, when I open it again, the form control or ActiveX control appears only in the place where the duplicate appeared before, and not in the original spot. I attach a screenshot of when I open the document again after saving and closing it:

 

3.png

 

I have tried in a new document and the behaviour is exactly the same. I have repaired the Office installation and even reinstalled it... nothing changed.

 

I repeat, until yesterday it was working perfectly. Whether I added or deleted some rows the control moved along with the original row without this strange behaviour.

 

I have looked in forums and can't find anything.... Can anyone help me?

 

Thanks in advance

4 Replies

@AlexBosch 

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.

  1. Control element, right mouse button, "Format control element ..."
  2. Click on "Properties"
  3. Object positioning

Click on the selection point "Dependent on cell position and size".

  1. 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.

See Show the Developer tab.

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

@AlexBosch 

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 ...

  1. Control element, right mouse button, "Format control element ..."

2Click on "Properties"

Object positioning

  1. 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.5

 

This 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