Thank you for writing back.Yes, the behavior of the combo box is exactly what you described if it is created as a Form Control
. It is a floating object and you cannot control its behavior using the Format Control dialog. That is, you cannot lock it within a cell or range since it will only float. You cannot hide it when the rows or columns are hidden and you cannot delete it when rows and columns are deleted. This is because in the Format Control dialog box, "Move and size with cells
" is NOT available for Form Controls. It is only available for ActiveX controls."Move and size with cells
" setting is the one controlling how a control like a combo box behaves along with the cells. If it is an ActiveX control, you can set that option (referring to Move and size with cells
) ON (just check it) and then the combo box will hide when rows or columns where it is placed is hidden. It will also be deleted when rows or columns are deleted. This is because the combo box as an ActiveX control is an "embedded" object, not a floating object.
Thus, if you need to have combo boxes that behaves in this way, you need to create the combo boxes as an ActiveX control NOT a Form control. However, you need to VBA to work with ActiveX controls unlike Form Controls where you can use the Format Dialog box to assign the data source for the control.
Here is my screen shot:
You can give it a try with a combo box from ActiveX controls. Set the option "Move and size with cells" checked and see if it is what you want as a combo box. If you find it the suited control for your Excel file, from there, you can start coding in order to make use of the ActiveX combo box.
I hope that helped.