我是VBA for excel的新手，目前正试图在我的工作簿中实现一个组合框 .

我有一个包含许多工作表的大工作表，其中一个有一个指南名称列表，我在那里选择它们（“A2：A80”）并将其命名为“Guides_names” . 我还添加了一个vba宏（在模块中），可以从工作簿中的任何位置激活，以显示列表：

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Guides_names"
    .IgnoreBlank = True
    .InCellDropdown = False
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

它运行良好：它在我选择的任何单元格中实现列表并打开它，然后选择将显示在单元格中 . 但是：1 . lise中的名字字体非常小; 2.没有自动补充 .

所以我决定使用它并隐藏验证列表并添加组合框 . 我找到了很多例子，并设法在其中一个工作表中添加一个漂亮的组合框，它完全打开，同时双击任何单元格我在工作表内单击但是当我在模块中实现类似的代码时：我无法自动打开列表（问题出在Me.TempCombo.DropDown：它不知道ME），并且从列表中选择一个值后，组合框会一直显示，当我尝试从另一张表中激活时，它赢了根本不工作......

感谢您的任何帮助或建议！

Sub Select_Guide()

Dim str As String
Dim cboTemp As OLEObject
Dim rngSelectedRange As Range
Dim ws As Worksheet
Set ws = ActiveSheet

' Here i use the original code:
 With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Guides_Names"
    .IgnoreBlank = True
    .InCellDropdown = False
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
 End With

  Set rngSelectedRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols))

  Set cboTemp = Worksheets("dbdb").OLEObjects("TempCombo")

  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = "Guides_Names"
    .LinkedCell = ""
    .Visible = False
  End With
  On Error GoTo errHandler

  If rngSelectedRange.Validation.Type = 3 Then
    'if the cell contains a data validation list
     Application.EnableEvents = False
    'get the data validation formula
    str = rngSelectedRange.Validation.Formula1
    str = Right(str, Len(str) - 1)

    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = rngSelectedRange.Left
      .Top = rngSelectedRange.Top
      .Width = rngSelectedRange.Width + 5
      .Height = rngSelectedRange.Height + 5
      .ListFillRange = str
      .LinkedCell = rngSelectedRange.Address
    End With
    cboTemp.Activate

    'open the drop down list automatically
    ' Me.TempCombo.DropDown - worked only when called by double_click function
    TempCombo.DropDown  ' This doesn't really opens the combo box
  End If

  errHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub
excel-vba combobox macros vba excel