vba 如何复制组合框以便单元格链接自动更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5016232/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy combobox so cell link changes automatically
提问by Reima K?lvi?inen
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.
我使用 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.
示例：我在 A5 中有组合框，单元格链接指向 B5。我想将组合框复制到单元格 A4，并将单元格链接复制到 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.
我需要复制 50 多个组合框。当我按下包含宏的命令按钮以复制整行并将其全部插入新行时，我尝试使用它。
I found one answer but VBA showed "Compile error: User defined type not defined!" and "TypeOf cbo.Object Is msforms.ComboBox" is bold.
我找到了一个答案，但 VBA 显示“编译错误：未定义用户定义的类型！” “TypeOf cbo.Object Is msforms.ComboBox”是粗体。
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
回答by Lee
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:
原始答案使我走上了正确的轨道，并适用于“ActiveX 控件”。我需要用标准的 Excel“表单控件”组合框做同样的事情，它非常相似，除了它是一个形状对象并且需要选择它进行操作。这是我的代码，它对我有用：
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
回答by Doug Glancy
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. :
如果您使用“控件工具箱”工具栏中的控件，则以下操作应该有效。唯一的区别是它不是 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.
如果您使用“表单”工具栏中的组合框，那么我不确定最好的方法是什么。我会尝试使用上面的方法。
