根据值查找单元格位置，然后执行某些操作-用户表格VBA Excel [英] Find cell location based on value then do something- User Forms VBA Excel

问题描述

我有编程方面的经验，但是，我是VBA的新手.我有一个正在处理的用户表单.此表单具有一个组合框，该组合框已初始化了一个列表.我想做的是:

i have experience in programing, however, I am new to VBA. I have a user form that i am working on. This form has a Combo Box that has a list initialized to it. What i am trying to do is:

*获取用户从组合框输入的ID号值 *获取用户输入的值，并使用工作表(即Worksheet.Range("ID_Number_List"))中的一系列值查找其匹配项 *一旦获得匹配，就获得它匹配的单元格的位置 *关闭将单元格的位置设置为一列，以获取与ID号(相同行)相关的名称，以将其设置为textBoxName.Value. *关闭将其设置为两列，以获取与ID号相关的电话号码并将其设置为textboxTele.value

*Get the ID Number value inputted by the user from the ComboBox *Take the value inputted by the user and find its match using a range of values from a worksheet (i.e. Worksheet.Range("ID_Number_List")) *Once it obtains it's match get the location of the cell that it matches * Off set the location of the cell by one column to get the Name that relates to the ID Number(Same Row) to set it to textBoxName.Value *Off set it two columns to get the telefone number that relates to the ID Number and set it to textboxTele.value

我希望从组合框中选择一个值后立即执行此操作，所以我的问题是我的代码是否进入了组合框，还是进入了下一个文本框?因此，只要此人切换到下一个文本框，代码就会自动执行.我希望代码能够完全执行而无需在下一个选项卡上切换.

I want this to happen as soon as a value is selected from the Combobox, so my question is does my code go in the combo box or does it go to the next text box? so as soon as the person tabs over to the next text box the code is automatically execute. i would like the code to fully execute without tabing over to the next box.

此代码不完整，但这是我所拥有的(我没有添加偏移部分，而只是执行了测试执行):

This code is not complete but here is what i have (i didnt add the off set part i just did a test execution):

Dim ORIValue As String 
'get value from combo_box Set 
ORIValue = COMBO_ORILIST.Value

Dim cLoc As Range 
Dim cORIVal As Range

'worksheet with the ID information Dim ORISheetList As Worksheet 
Set ORISheetList = Worksheets("ORI_LIST")

'
For Each cLoc In ORISheetList.Range("ORI_LIST")
'compare the input string from list- considering using Match function for this
If StrComp(cLoc, ORIValue,  vbTextCompare) Then TextBAgencyName.Value = "test"
Else: Next cLoc
End If

让我知道您的想法.如果我必须重写所有内容，我会的.

Let me know what you think. If i have to rewrite everything i will.

推荐答案

您的代码无法编译.

如果您的用户窗体带有一个名为ComboBox1的组合框，则需要将单元格查找代码放入窗体代码中，如下所示:

If you have a userform with a single combobox called ComboBox1, you need to put your cell-finding code in the form code as follows:

Private Sub ComboBox1_Change()
    MsgBox "yep, this is where the code should go"
End Sub

我怀疑结合使用组合框的rowsource属性和所选值的索引，您可能实际上不需要搜索所选值.这样的事情可能会起作用:

I suspect using the rowsource property of the combobox in combination with the index of the selected value you probably don't need to actually perform a search for the selected value. Something like this might work:

Private Sub ComboBox1_Change()
    MsgBox Range(ComboBox1.RowSource).Cells(ComboBox1.ListIndex + 1)
End Sub

希望有帮助.

这篇关于根据值查找单元格位置，然后执行某些操作-用户表格VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！