在VBA中填充带有值的组合框 [英] Filling a combobox with values in VBA

问题描述

用户使用refedit选择范围， ComboBox

然后必须使用所选单元格的值填充code>。如果用户更改参考，则必须删除旧数据并重新填充新数据。

以下是我当前的代码。编译权利，但不行。

我没有附加到 ComboBox 本身，但是我需要填充一个列表，其值来自一个列，所以用户可以选择他们想要用作键的第一组是一行中的一个示例。我想要提供这些选项作为下拉菜单的选择。

您可以下载我在 http://ge.tt/2dbV5Yt/v/0?c

商店＃地址City ST Zip Market Radius 

  Private Sub rngHeader_Exit（ByVal Cancel As MSForms.ReturnBoolean ）
 Dim selRng As Range 
 Set selRng = Range（rngHeader.Value）
 
'//清除任何在其中的项
对于I = 1到cmbKeyCol .ListCount 
 cmbKeyCol.RemoveItem 0'每次删除顶部项目
下一个我
 
 
'下面是我遇到的问题。这是我的一个尝试，但
'我已经改变了这个事情大概20次，因为提出问题
'//从标题行构建项目的新列表。 
对于每个单元格在selRng.Cells 
 cmbKeyCol.AddItem cell.Value 
下一个
 End Sub 
  

解决方案

为什么不用这个代替：

  cmbkeyCol.RowSource = selRng.Address'假设你已经有你的范围了。 
  

这将添加指定范围内的所有项目，而不是通过它们迭代1。 >

编辑1：

  Dim list as variant 
 
 list = selRng.Value 
 list = Application.Transpose（list）
 
 cmbkeyCol.List = list 
  / pre> 
 
 
希望这样工作。
 
 
 
 编辑2： 
  Dim list as variant 
 
 list = rngHeader.value 
 list = Application.Transpose（list）
 
 cmbkeyCol.List = list 
  
我假设selRng是源范围实际上编辑1好，它是rngHeader。 
希望这样工作。
 
I am having trouble filling a combobox with options from a range.

The user selects the range with a refedit, the ComboBox must then be populated with the values of the selected cells. If the user changes the ref the old data must be removed and repopulated with the new data. 

Below is my current code. Compiles right, but doesn't work. 

I'm not attached to a ComboBox per se, but I need to populate a list with the values from a column so the user can select the one they want to use as "key" The first set is a sample of what is in a row. I would want these options offered as the choices for the dropdown.

You can download a copy of what I'm working on at http://ge.tt/2dbV5Yt/v/0?c
Store #    Address City    ST  Zip Market  Radius


Private Sub rngHeader_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim selRng As Range
    Set selRng = Range(rngHeader.Value)

    '//Erase any items that are in there
    For I = 1 To cmbKeyCol.ListCount
        cmbKeyCol.RemoveItem 0 'Remove the top item each time
    Next I


    'Below here is the part that I'm having trouble with. This is one of my attempts, but
       'I've changed this thing probably 20 times since asking the question
    '//Build  new list of items from the header row.
    For Each cell In selRng.Cells
        cmbKeyCol.AddItem cell.Value
    Next
End Sub

解决方案
why don't you use this instead:
cmbkeyCol.RowSource = selRng.Address 'Assuming you already got your range right.
This adds all the items in the range specified instead of iterating through them 1 by 1.

Edit 1:
Dim list as variant

list = selRng.Value
list = Application.Transpose(list)

cmbkeyCol.List = list
Hope this works.

Edit 2:
Dim list as variant

list = rngHeader.value
list = Application.Transpose(list)

cmbkeyCol.List = list
I assumed that selRng is the source range in Edit 1 well in fact it is rngHeader.
Hopefully this works now.

                        
这篇关于在VBA中填充带有值的组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！