当前位置：文江博客话题详情

使用VBA，如何根据Excel表格中的特定列填充ComboBox值

发布于 2024-12-09 03:25:39 字数 112 浏览 1 评论 0原文

我正在尝试使用 VBA 构建这个复杂的表单以将新行添加到现有表中。我想要这个组合框列出表的一列中已经存在的值。

我是 VBA 新手。我尝试了一些范围/选择和排序组合，到目前为止不乏......

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

[浮城] 2024-12-16 03:25:40

这就是您正在寻找的内容..它应该可以帮助您开始，只需根据您的需要调整工作表和范围即可。

  Dim cmb as ComboBox
  Dim rng as Range

  Set cmb = Worksheets("Sheet1").ComboBox1

  'To fill based on range
  For Each rng in Worksheets("Sheet2").Range("C2:C300")
  Cmb.AddItem Rng.Value
  Next

  'To fill from table where ListColumns(N) is the specific column
  Set rng = Sheet2.ListObject(1).ListColumns(3).Range
  For Each rng in rng
  Cmb.AddItem Rng.Value
  Next

  Cmb.ListIndex = 0

*编辑：*克里斯是对的，我原来的代码有错误。在上班路上发布的答案没有时间检查。上面的代码工作正常。 Chris 建议仅使用 .value 来填充会更快。老实说，我不知道你可以这样做。

Here's what you're looking for.. It should get you started, Just adapt the Sheets and Range to your needs.

  Dim cmb as ComboBox
  Dim rng as Range

  Set cmb = Worksheets("Sheet1").ComboBox1

  'To fill based on range
  For Each rng in Worksheets("Sheet2").Range("C2:C300")
  Cmb.AddItem Rng.Value
  Next

  'To fill from table where ListColumns(N) is the specific column
  Set rng = Sheet2.ListObject(1).ListColumns(3).Range
  For Each rng in rng
  Cmb.AddItem Rng.Value
  Next

  Cmb.ListIndex = 0

*EDITED:*Chris is right, my original code had errors. Posted answer on way to work didn't have time to check. The code above works fine. Chris suggestion on just using .value to fill is quicker. I honestly didn't know you could do it like that.

回复收藏 0 原文

残龙傲雪 2024-12-16 03:25:40

您需要创建表格列范围，
您可以在范围内插入行
或者您需要首先向表中添加行并调整范围大小
并将该范围传递给 SetRng 参数，
userFormName 是用户表单名称，
ControlName 是组合框名称

Public Function FillRangeComboBox(userFormName As String, ControlName As String, SetRng As Range) As Boolean
    Dim ObjFormName As Object: Set ObjFormName = ThisWorkbook.VBProject.VBComponents(userFormName)
    Dim ObjControlName As MSForms.ComboBox: Set ObjControlName = ObjFormName.Designer.Controls(ControlName)

    ''set combobox value
    With ObjControlName
         .RowSource = SetRng.Address

    End With

End Function

you need to create your table column range,
Either you can insert your row in side the range
Or you need to first add row to table and resize your range
and pass that range pass to SetRng parameter,
userFormName is user form name,
ControlName is combobox name

Public Function FillRangeComboBox(userFormName As String, ControlName As String, SetRng As Range) As Boolean
    Dim ObjFormName As Object: Set ObjFormName = ThisWorkbook.VBProject.VBComponents(userFormName)
    Dim ObjControlName As MSForms.ComboBox: Set ObjControlName = ObjFormName.Designer.Controls(ControlName)

    ''set combobox value
    With ObjControlName
         .RowSource = SetRng.Address

    End With

End Function

回复收藏 0 原文

~没有更多了~