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

Answers


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

Reima,

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.


Need Your Help

OpenJPA Syntax error: Encountered "optimize"

java jpa optimization websphere openjpa

When using OpenJPA to execute a select statement in in-memory database org.apache.derby, I encounter this error:

Android: Provide a resource if width is smaller than specific DP

android android-resources

How do I use the resource qualifiers system in Android to specify that I want a resource to apply only if the width is smaller than a particular value?