How to copy combobox so cell link changes automatically
Is it possible to copy a created combobox and paste it in another cell so that cell link is changed too?
I do this work with Excel 2007.
Example: I have combobox in A5, cell link pointing to B5. I want to copy the combo box to cell A4 and the cell link to point to B4.
I need to copy more than 50 comboboxes. I tried to use it when I push the commandbutton which include the macro to copy a entire row and insert all of it to the new row.
I found one answer but VBA showed "Compile error: User defined type not defined!" and "TypeOf cbo.Object Is msforms.ComboBox" is bold.
Macro I found:
Sub Test() Dim cbo As OLEObject For Each cbo In ActiveSheet.OLEObjects If TypeOf cbo.Object Is msforms.ComboBox Then cbo.LinkedCell = cbo.TopLeftCell.Offset(, 1).Address End If Next End Sub
The original answer put me on the right track and works for the "ActiveX control". I needed to do the same thing with a standard Excel "Form control" combobox and it's pretty similar, except it's a shape object and it needs to be selected to be manipulated. Here's my code, which worked for me:
Sub AllocateLinkedCellsToComobBoxes() Dim myShape As Shape For Each myShape In ActiveSheet.Shapes If myShape.Type = msoFormControl Then If myShape.FormControlType = xlDropDown Then myShape.Select Selection.LinkedCell = Selection.TopLeftCell.Address End If End If Next End Sub
If you are using controls from the "Control Toolbox" toolbar, then the following should work. The only difference is that it's not an MSForms.Combobox. :
Sub Test() Dim cbo As OLEObject For Each cbo In ActiveSheet.OLEObjects If TypeOf cbo.Object Is ComboBox Then cbo.LinkedCell = cbo.TopLeftCell.Offset(, 1).Address End If Next End Sub
If you are using a combobox from the "Forms" toolbar, then I'm not sure what the best approach is. I'd try using the method above.