如何从excel获取userform texbox文本取决于条件 [英] how to get userform texbox text from excel depends on condition

问题描述

大家好，



我创建了一个与销售相关的表格。

字段就像Item一样，将所有这些数据定价存放在Excel工作表中。

当在userform中选择Item时，相关价格文本框值需要默认获取价格怎么办这个。

Hi All,

I have created one sales related form.
the fields are like Item, price all these data stored in excel sheet.
When select Item in userform the relavent price textbox value need to get price by default how to do this.

推荐答案

要做的步骤：

Steps to do:

1. 添加新的shee并更改其名称为数据
2. 让 A1：B1 范围为标题：项目和价格分别
3. 添加样本数据
4. 转到VBA代码编辑（ALT + F11）
5. 添加模块（工具 - >插入 - >模块）
6. 复制并粘贴下面的代码

1. Add new shee and change its name to "Data"
2. Lets A1:B1 range will be the headers: Item and Price respectively
3. Add sample data
4. Go to VBA code editor (ALT+F11)
5. Add module (Tools->Insert->Module)
6. Copy and paste below code

Option Explicit
'loads data into 2 columns listbox from desired range
Sub Load2Columns(ByRef oLst As MSForms.ListBox, ByRef wsh As Worksheet, Optional ByVal sFirstColumn As String = "A", Optional ByVal iFirstRow As Integer = 2)
Dim i As Integer

With oLst
    Do While wsh.Range(sFirstColumn & iFirstRow) <> ""
        .AddItem ""
        i = .ListCount - 1
        .Column(0, i) = wsh.Range(sFirstColumn & iFirstRow)
        .Column(1, i) = wsh.Range(sFirstColumn & iFirstRow).Offset(ColumnOffset:=1)
        iFirstRow = iFirstRow + 1
    Loop
End With

End Sub

添加UserForm（工具 - >插入 - >用户窗体

从工具箱添加

< il>

• 一个ListBox




• Add UserForm (Tools->Insert->UserForm
• From ToolBox add
<il>

• one ListBox
• 一个TextBox
• 两个标签
• 一个按钮

Option Explicit

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ListBox1_Change()
Me.TextBox1 = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
End Sub

Private Sub UserForm_Initialize()

Load2Columns Me.ListBox1, ThisWorkbook.Worksheets("Data")
With Me.ListBox1
    .ColumnCount = 2
    .ColumnWidths = ";0"
    .ListIndex = 0
End With

End Sub





• 这就是全部！现在，当您更改ListBox的选择时，价格将加载到TextBox





这里 [^] 你可以下载一个例子。



Here[^] you can download an example.




这篇关于如何从excel获取userform texbox文本取决于条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！