时间戳

首页 > 解决方案 > 使用 VBA 在用户窗体上生成组合框和编辑文本框

excel - 使用 VBA 在用户窗体上生成组合框和编辑文本框

问题描述

基本上，我有一个我想要制作的对话框

在此处输入图像描述

如果您可以在上面看到，我想将组合框应用于 BDI Industry Group 和 CIF 的搜索。

然后，每当我点击 Search by Industry Group 组合框并选择一个时，CIF 字段就会为您提供另一个下拉列表供您选择，然后，其余其他字段将根据以下数据自动填充

在此处输入图像描述

上面的截图只是我数据库的一小部分。总共有 7504 行，表头从 A2 开始。

假设我选择交通

然后我希望对话框显示 CIF 13039099 和 12901262 的下拉列表以供选择。

接下来以我选择CIF 13039099为例，其余其他字段将根据数据库直接自动显示信息，即：

我希望 Client Name、  RM Name和 Segment 是 可编辑的。

之后，当我单击保存时，它将根据最新更改自动更新。这可以执行吗？下面是我制作的 VBA 代码

Private Sub ComboBox1_Change()

Me.ComboBox2 = ""
 
Select Case Me.ComboBox1

    Case "BDIIndustryGroup"

        Me.ComboBox2.RowSource = "BDIIndustryGroup"

   
    Case "CIF"

        Me.ComboBox2.RowSource = "CIF"


    Case "Client Name"

        Me.ComboBox2.RowSource = "CLientName"

   
    Case "RM Name"

        Me.ComboBox2.RowSource = "RM Name"
   

    Case "Segment"

        Me.ComboBox2.RowSource = "Segment"

End Select

End Sub


Private Sub UserForm_Initialize()

Dim lastrow As Long

Dim lastcolumn As Long

lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column

With Worksheets("sheet2")


    For I = 1 To lastcolumn

        With .Columns(I)

        lastrow = Sheet2.Cells(Rows.Count, I).End(xlUp).Row

            With Range(Cells(1, I), Cells(lastrow, I))

            Range(Cells(1, I), Cells(lastrow, I)).Select

            Selection.CreateNames Top:=True

            End With

        End With

    Next I

End With


Me.ComboBox1.RowSource = "BDIIndustryGroup"
      

End Sub

 

Private Sub CommandButton2_Click()

Unload Me

End Sub

我尝试了这段代码，但它没有按我的意愿工作。

我还从@Harun24HR 获得了第二个替代代码，但无法基于 BDI 行业组生成 CIF 下拉列表。

'Codes to form

Option Explicit

Private matchRow As Long

Private Sub Combobox1_Change()

Dim rng As Range, cel As Range

Dim lstrow As Long

Dim strBDI As String

 

    strBDI = Me.ComboBox1

   

    lstrow = Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row 'Change column E with BDI Industry Group column in case of you.

    Set rng = ActiveSheet.Range("E3:E" & lstrow)

   

    Me.ComboBox2.Clear

    For Each cel In rng

        If cel = strBDI Then

            Me.ComboBox2.AddItem cel.Offset(0, -4) '-4 need to adjust with CIF column left from BDI column

        End If

    Next

 

End Sub

Private Sub Combobox2_Change()

Dim rng As Range, cel As Range

Dim lstrow As Long

Dim strBDI As String

Dim strCIF As String

 

    strBDI = Me.ComboBox1

    strCIF = Me.ComboBox2

   

    lstrow = Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row 'Change column E with BDI Group Industry column in case of you.

    Set rng = ActiveSheet.Range("E3:E" & lstrow)

   

    For Each cel In rng

        If cel = strBDI And cel.Offset(0, -4) = strCIF Then

            matchRow = cel.Row

            Exit For

        End If

    Next

   

    Me.TextBox1 = ActiveSheet.Cells(matchRow, 2)

    Me.TextBox2 = ActiveSheet.Cells(matchRow, 3)

    Me.TextBox3 = ActiveSheet.Cells(matchRow, 4)

   

End Sub

Private Sub cmdSave_Click()

    ActiveSheet.Cells(matchRow, 2) = Me.TextBox1

    ActiveSheet.Cells(matchRow, 3) = Me.TextBox2

    ActiveSheet.Cells(matchRow, 4) = Me.TextBox3

   

    MsgBox "Data Saved Succesfully!", vbInformation, "Save"

   

End Sub

Private Sub UserForm_Initialize()

    'Add items manually or use sub to add unique items from BDI Industry Group column

    Me.ComboBox1.AddItem "AUTOMOTIVE ATPM & DEALER"

    Me.ComboBox1.AddItem "AUTOMOTIVE COMPONENT"

    Me.ComboBox1.AddItem "CABLE"

    Me.ComboBox1.AddItem "CEMENT"

    Me.ComboBox1.AddItem "CHEMICAL"

    Me.ComboBox1.AddItem "chemical & PLASTICS"

    Me.ComboBox1.AddItem "COAL INDUSTRY"

    Me.ComboBox1.AddItem "COMPUTER & RELATED"

    Me.ComboBox1.AddItem "CONSTRUCTION"

    Me.ComboBox1.AddItem "COSMETICS & TOILETRIES"

    Me.ComboBox1.AddItem "CPO INDUSTRY"

    Me.ComboBox1.AddItem "ELECTRICITY & POWER PLANT"

    Me.ComboBox1.AddItem "ENGINE MACHINERIES & TOOLS"

    Me.ComboBox1.AddItem "FARMING & ANIMAL FEED"

    Me.ComboBox1.AddItem "FINANCIAL SERVICES"

    Me.ComboBox1.AddItem "FISHERIES"

    Me.ComboBox1.AddItem "FOOD & BEVERAGE"

    Me.ComboBox1.AddItem "FORESTRY & WOOD PRODUCT"

    Me.ComboBox1.AddItem "FURNITURE"

    Me.ComboBox1.AddItem "GARMENT"

    Me.ComboBox1.AddItem "HOME APPLIANCES"

    Me.ComboBox1.AddItem "HOSPITAL & HEALTHCARE"

    Me.ComboBox1.AddItem "HOTEL & ACCOMMODATION SERVICE"

    Me.ComboBox1.AddItem "INFRASTRUCTURE"

    Me.ComboBox1.AddItem "MEDICAL EQUIPMENT"

    Me.ComboBox1.AddItem "METAL NON STEEL"

    Me.ComboBox1.AddItem "MINING & QUARRING"

    Me.ComboBox1.AddItem "OIL & GAS - DOWNSTREAM"

    Me.ComboBox1.AddItem "OIL & GAS - UPSTREAM"

    Me.ComboBox1.AddItem "OTHERS"

    Me.ComboBox1.AddItem "PACKAGING & CORRUGATED"

    Me.ComboBox1.AddItem "PETROCHEMICAL & PLASTICS"

    Me.ComboBox1.AddItem "PHARMACEUTICAL"

    Me.ComboBox1.AddItem "PRINTING"

    Me.ComboBox1.AddItem "PROPERTIES & REAL ESTATE"

    Me.ComboBox1.AddItem "PULP & PAPER"

    Me.ComboBox1.AddItem "RENTAL SERVICES"

    Me.ComboBox1.AddItem "RETAILER"

    Me.ComboBox1.AddItem "RUBBER INDUSTRY"

    Me.ComboBox1.AddItem "SERVICE INDUSTRY"

    Me.ComboBox1.AddItem "SHIPPING"

    Me.ComboBox1.AddItem "STEEL"

    Me.ComboBox1.AddItem "SUGAR INDUSTRY"

    Me.ComboBox1.AddItem "TELECOMMUNICATION"

    Me.ComboBox1.AddItem "TEXTILE"

    Me.ComboBox1.AddItem "TOBACCO & CIGARETTE"

    Me.ComboBox1.AddItem "TRANSPORTATION"

    Me.ComboBox1.AddItem "WHOLESALES TRADING"

End Sub

任何帮助将不胜感激。

谢谢你。

标签： excelvbacomboboxtextboxuserform

解决方案

按照下面的屏幕截图控件名称设置尝试以下代码。

在此处输入图像描述

'Codes to form.
Option Explicit
Private matchRow As Long

Private Sub ComboBox1_Change()
Dim rng As Range, cel As Range
Dim lstRow As Long
Dim strBDI As String

    strBDI = Me.ComboBox1
    
    lstRow = Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row 'Change column E with BDI column in case of you.
    Set rng = ActiveSheet.Range("E2:E" & lstRow)
    
    Me.ComboBox2.Clear
    For Each cel In rng
        If cel = strBDI Then
            Me.ComboBox2.AddItem cel.Offset(0, -4) '-4 need to adjust with CIF column left from BDI column
        End If
    Next
    
End Sub

Private Sub ComboBox2_Change()
Dim rng As Range, cel As Range
Dim lstRow As Long
Dim strBDI As String
Dim strCIF As String

    strBDI = Me.ComboBox1
    strCIF = Me.ComboBox2
    
    lstRow = Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row 'Change column E with BDI column in case of you.
    Set rng = ActiveSheet.Range("E2:E" & lstRow)
    
    For Each cel In rng
        If cel = strBDI And cel.Offset(0, -4) = strCIF Then
            matchRow = cel.Row
            Exit For
        End If
    Next
    
    Me.TextBox1 = ActiveSheet.Cells(matchRow, 2)
    Me.TextBox2 = ActiveSheet.Cells(matchRow, 3)
    Me.TextBox3 = ActiveSheet.Cells(matchRow, 4)
End Sub

Private Sub cmdSave_Click()
     ActiveSheet.Cells(matchRow, 2) = Me.TextBox1
     ActiveSheet.Cells(matchRow, 3) = Me.TextBox2
     ActiveSheet.Cells(matchRow, 4) = Me.TextBox3
     
     MsgBox "Data Saved Successfully!", vbInformation, "Save"
End Sub

Private Sub UserForm_Initialize()
    'Add items manually or use sub to add unique items from BDI column.
    Me.ComboBox1.AddItem "Transport"
    Me.ComboBox1.AddItem "Food"
End Sub

推荐阅读