时间戳

首页 > 解决方案 > VBA 用户表单列表框条件逻辑未按预期工作

excel - VBA 用户表单列表框条件逻辑未按预期工作

问题描述

我有一个带有列表框的用户表单，我正在使用条件逻辑来确定列表框中选定或未选定项目表的输出值。问题是当文本框 ( Tbl_AliasName) 为空白时，代码会执行以下操作：

ElseIf .Selected(k) = True And Tbl_AliasName = vbNullString Then
            lstbxRow = lstbxRow + 1

                Worksheets("New TRAX").Cells(lstbxRow, 3) = Trim(Cells(2, 1).Value2) & "." & .Column(1, k)

但是如果Tbl_AliasName不是空白，那么代码什么也不做，但它应该这样做：

 ElseIf .Selected(k) = True And Tbl_AliasName <> vbNullString Then
            lstbxRow = lstbxRow + 1

                Worksheets("New TRAX").Cells(lstbxRow, 3) = Tbl_AliasName & "." & .Column(1, k)

我使用了几种 If 语句的变体，但没有一个有效。
以下是我的代码：

Option Explicit
Public Tbl_AliasName As String
Tbl_AliasName = Trim(UserForm_Finder.txtConcat.Value)

Private Sub BtnConcat_Click()
Dim k As Long, lstbxRow As Long, LR As Long

lstbxRow = 1

 '****************
'This if statement works perfectly
If (Cells(2, 1).Value2 = vbNullString Or Cells(2, 2).Value2 = vbNullString) _
        And Tbl_AliasName = vbNullString Then

        MsgBox "You must Search for a Table or Column first.", _
        vbExclamation, "Error Encountered"

        Exit Sub

ElseIf (UserForm_Finder.ListBx_TblsCols.ListCount = 0 And Tbl_AliasName <> vbNullString) Then
        MsgBox "You must Search for a Table or Column first.", _
        vbExclamation, "Error Encountered"
        '(Cells(2, 1).Value2 = vbNullString Or Cells(2, 2).Value2 = vbNullString) And _

        Exit Sub

End If


With UserForm_Finder.ListBx_TblsCols
    For k = 0 To .ListCount - 1
        '****************
         This is where the problems begin
    If .Selected(k) = False Then
            MsgBox "You must Select 1 or more items from the list box.", _
            vbExclamation, "Error Encountered"
            Exit Sub

        ElseIf .Selected(k) = True And Tbl_AliasName <> vbNullString Then
            lstbxRow = lstbxRow + 1

                Worksheets("New TRAX").Cells(lstbxRow, 3) = Tbl_AliasName & "." & .Column(1, k)

        ElseIf .Selected(k) = True And Tbl_AliasName = vbNullString Then
            lstbxRow = lstbxRow + 1

                Worksheets("New TRAX").Cells(lstbxRow, 3) = Trim(Cells(2, 1).Value2) & "." & .Column(1, k)

        End If
    Next k
  End With
End Sub

我的目标是做到以下几点：

  1. 如果文本框 (Tbl_AliasName) 不为空并且用户在列表框中选择了一个或多个项目 (ListBx_TbleCols)，则将 Tbl_AliasName 连接到列表框中的选定项目
  2. 如果 Tbl_AliasName 为空，则使用 Cells(2,1) 中的值连接到列表框中的选定项。

我尝试了以下添加：

Dim LstBxItemSelected As Boolean

'This was placed in the for loop
LstBxItemSelected = True

'this was placed outside the for loop
If LstBxItemSelected = False Then
      MsgBox "You must Select 1 or more items from the list box.", _
             vbExclamation, "Error Encountered"
      Exit Sub       
End If

有没有更好的方法来判断是否选择了项目，因为我觉得我在循环中构建它的方式，如果没有选择所有内容，代码会抛出错误？提前感谢您的任何想法、答案或建议！

注意：列表框是通过单击用户窗体上的另一个按钮来填充的，该按钮调用以下子：

Sub FillLstBxCols()  

    Dim ListBx_Target As MSForms.ListBox
    Dim rngSource As Range
    Dim LR As Long

    If Cells(2, 1).Value2 <> vbNullString Then
        LR = Worksheets("New TRAX").Cells(Rows.Count, 2).End(xlUp).Row


        'Set reference to the range of data to be filled
        Set rngSource = Worksheets("New Trax").Range("A" & 2 & ":" & "B" & LR)

        'Fill the listbox
        Set ListBx_Target = UserForm_Finder.ListBx_TblsCols
        With ListBx_Target
            .RowSource = rngSource.Address
        End With
    End If
End Sub

标签： excelvbalistboxuserform

解决方案

没有样本数据和预期结果很难说，但我认为这就是你要找的：

Private Sub btnConcat_Click()

    Dim ws As Worksheet
    Dim bSelected As Boolean
    Dim sConcat As String
    Dim i As Long, lRowIndex As Long

    Set ws = ActiveWorkbook.Sheets("New TRAX")
    lRowIndex = 1
    bSelected = False
    sConcat = Trim(Me.txtConcat.Text)
    If Len(sConcat) = 0 Then sConcat = Trim(ws.Cells(2, "A").Value)
    If Len(sConcat) = 0 Then
        MsgBox "You must Search for a Table or Column first.", vbExclamation, "Error Encountered"
        Exit Sub
    End If

    For i = 0 To Me.ListBx_TblsCols.ListCount - 1
        If Me.ListBx_TblsCols.Selected(i) Then
            If bSelected = False Then
                bSelected = True
                ws.Range("C2", ws.Cells(ws.Rows.Count, "C")).Clear  'clear previous concat results (delete this line if not needed)
            End If
            lRowIndex = lRowIndex + 1
            ws.Cells(lRowIndex, "C").Value = sConcat & "." & Me.ListBx_TblsCols.List(i)
        End If
    Next i

    If bSelected = False Then MsgBox "Must select at least one item from the list"

End Sub

推荐阅读