时间戳

首页 > 解决方案 > Excel VBA - 带有 If 语句的用户窗体组合框

excel - Excel VBA - 带有 If 语句的用户窗体组合框

问题描述

我创建了一个用户表单。代码尚未完成，但尝试在组合框上设置条件。这是代码：

Private Sub UserForm_Initialize()

    With SupText
        .AddItem "Sup1"
        .AddItem "Sup2"
        .AddItem "Sup3"
    End With
    
    With ProdText
        .AddItem "Prod1"
        .AddItem "Prod2"
        .AddItem "Prod3"
        .AddItem "Prod4"
        .AddItem "Prod5"
    End With
    
    With UnitText
        .AddItem "kL"
        .AddItem "T"
    End With
    
    With StaText
        .AddItem "In Progress"
        .AddItem "Awaiting"
        .AddItem ""
    End With
    
    With ProLText
        .AddItem "1"
        .AddItem "4"
        .AddItem "1&4"
        .AddItem "2"
        .AddItem "3"
        .AddItem "2&3"
        .AddItem "WOPL"
        .AddItem "BOPL"
        .AddItem "Industry Line"
    End With
    
    End Sub

所以，我想要做的是ProLText组合框根据SupText. 当我使用if语句时，它不起作用。例如，如果从 SupText 组合框中选择 Sup1，我只想给出 ProLText 组合框的前 6 个选项，则 Sup2 给出下一个 2，而 Sup3 给出最后一个项目。但是由于某种原因，它给出了 else 语句的结果，而不是 if 或 else if 的结果。

任何想法为什么它不起作用？

问候。

标签： excelvbacomboboxuserform

解决方案

  1. .Style将组合框的fmStyleDropDownList
  2. 不要将项目添加ProLText到UserForm_Initialize()
  3. 在SupText_Click()事件清除ProLText并重新添加相关项目。

代码

Private Sub SupText_Click()
    If SupText.ListIndex = -1 Then Exit Sub
    
    ProLText.Clear
    
    Select Case SupText.Text
    Case "Sup1"
        With ProLText
            .AddItem "1"
            .AddItem "4"
            .AddItem "1&4"
            .AddItem "2"
            .AddItem "3"
            .AddItem "2&3"
        End With
    Case "Sup2"
        With ProLText
            .AddItem "WOPL"
            .AddItem "BOPL"
        End With
    Case "Sup3"
        With ProLText
            .AddItem "Industry Line"
        End With
    End Select
End Sub

或者更短的版本

Private Sub SupText_Click()
    If SupText.ListIndex = -1 Then Exit Sub
        
    With ProLText
        .Clear
        Select Case SupText.Text
            Case "Sup1"
                .AddItem "1"
                .AddItem "4"
                .AddItem "1&4"
                .AddItem "2"
                .AddItem "3"
                .AddItem "2&3"
            Case "Sup2"
                .AddItem "WOPL"
                .AddItem "BOPL"
            Case "Sup3"
                .AddItem "Industry Line"
        End Select
    End With
End Sub

推荐阅读