Sub createcombo()
Dim obj As OLEObject
Set obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=ActiveCell.Width, Height:=ActiveCell.Height)
obj.Visible = True
Dim num As Integer
On Error Resume Next
For Each obj In ActiveSheet.OLEObjects
If InStr(obj.Name, "ComboBox") > 0 Then num = num + 1
Next
ActiveSheet.OLEObjects("ComboBox" & num).Object.List = Array(0, 1, 2)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
createcombo
End Sub
而要满足你的要求,这就够了:
Private Sub Worksheet_Activate()
Dim obj As OLEObject
Set obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=ActiveCell.Width, Height:=ActiveCell.Height)
obj.Visible = True
ActiveSheet.OLEObjects("ComboBox1").Object.List = Array(0, 1, 2)
End Sub