VBA：ListBox 更改事件触发两次

huangapple
go评论101阅读模式
英文:

VBA: ListBox Change event firing twice

问题

我在Excel中有一个用户表单，在其中问题在一个Listbox控件中进行了索引。单击Listbox中的项目会调用Change事件，根据所选的项目填充其他控件的值。

用户可以更改文本框中的值。在更改它们后，会为该问题设置一个"已保存"标志为False。然后，用户可以将问题保存到内存中，或导航离开该问题。

如果用户在不保存的情况下导航（通过单击Listbox中的不同项目），我想向他们显示警告，提供以下选项：要么放弃未保存的更改，要么保留当前选择，并还原刚刚单击的Listbox选择。

如果选择"放弃更改"，那么一切正常。但是当我尝试还原Listbox选择时，它会遇到问题。我使用一个"EventsOn"布尔值来处理何时应该继续执行Change过程，以避免它调用自身。这似乎在代码中的正确点起作用。 但是在EventsOn被恢复后，在Exit Sub之后，似乎Change事件再次被调用。

我不知道为什么事件会再次触发。这导致用户第二次看到这个选项。

以下是我代码的相关部分：

  1. Option Explicit
  2. Dim NumberOfQuestions As Long
  3. Dim EventsOn As Boolean
  4. Dim SelectedListIndex As Long, CurrentQuestion As Long, QuestionSaved As Variant
  6. Private Sub UserForm_Initialize()
  7.     ' 省略了大量代码。基本上打开记录集并加载值
  8.     ReDim QuestionSaved(1 To NumberOfQuestions) As Boolean
  9.     '
  10.     For X = 1 To NumberOfQuestions
  11.         lbox_QuestionList.AddItem "Question " & X ' Populate the listbox with items
  12.         QuestionSaved(X) = True ' Flag the initial state as saved, for each question
  13.         If Not X = rst.RecordCount Then rst.MoveNext
  14.     Next X
  15.     '
  16.     ' 选择第一个问题为默认。请注意，Listbox ListIndex从0开始，而问题从1开始
  17.     SelectedListIndex = 0
  18.     CurrentQuestion = 1
  19.     EventsOn = True
  20.     lbox_QuestionList.ListIndex = SelectedListIndex
  21. End Sub
  23. Private Sub lbox_QuestionList_Change()
  24.     ' 确保此事件在以编程方式更改时不会不断触发
  25.     If Not EventsOn Then Exit Sub
  26.     '
  27.     If Not QuestionSaved(CurrentQuestion) Then
  28.         If MsgBox(Prompt:="Abandon changes to current question?", Title:="Current question not saved", Buttons:=vbYesNo + vbDefaultButton2) = vbYes Then
  29.             ' 放弃更改 = 是
  30.             ' 标记为已保存
  31.             QuestionSaved(CurrentQuestion) = True
  32.             ' 然后继续正常更改
  33.             ' (如果用户回到这个问题，它将以原始形式重新加载到内存中)
  34.             ' 这部分正常工作
  35.         Else
  36.             ' 放弃更改 = 否
  37.             EventsOn = False ' 所以这个子过程不会再次被调用
  38.             ' 还原ListBox选择。通过重新调用当前问题编号并应用于ListIndex来实现
  39.             SelectedListIndex = CurrentQuestion - 1 ' 请记住索引将减1，因为索引从0开始
  40.             lbox_QuestionList.ListIndex = SelectedListIndex
  41.             EventsOn = True
  42.             Exit Sub ' 这应该是结束。但不知何故，它并没有...
  43.         End If
  44.     End If
  45.     ' 根据新选择的ListIndex继续加载新问题
  46.     SelectedListIndex = lbox_QuestionList.ListIndex ' 认识当前选择
  47.     ' ListIndex从零开始，所以我们需要加1
  48.     CurrentQuestion = SelectedListIndex + 1
  49.     ShowQuestion CurrentQuestion
  50. End Sub
  52. Private Sub ShowQuestion(QuestionNumber As Long)
  53.     ' 为简洁起见，省略了详细信息。基本上从类的字典中加载详细信息，并填充到文本框中
  54. End Sub
  56. Private Sub cb_Save_Click()
  57.     ' 省略了代码。获取当前文本框的值并将其保存到字典中的类中
  58.     ' 将当前问题标记为已保存：
  59.     QuestionSaved(CurrentQuestion) = True
  60. End Sub
  62. ' 事件处理程序
  63. Private Sub tb_Question_Change()
  64.     DoChange
  65. End Sub
  67. ' 几个其他表单控件有类似的事件：都调用下面的"DoChange"
  68. Private Sub DoChange()
  69.     If Not EventsOn Then Exit Sub
  70.     QuestionSaved(CurrentQuestion) = False ' 如果对表单值进行任何更改，将当前问题标记为未保存
  71. End Sub

我自然而然地搜索了这个问题，但目前还没有答案可以帮助我：

我的代码逻辑似乎是正确的。谜底是为什么Change事件会再次被调用，即使在Exit Sub之后。

英文:

I have a User Form in Excel in which questions are indexed in a Listbox control. Clicking on an item in the Listbox calls a Change event which populates other controls' values according to which item has been selected.

The user may change values within the text boxes. Upon changing them, a "Saved" flag gets set to False for that question. The user may then save the question into memory; or navigate away from the question.

If the user navigates away without saving (by means of clicking a different item in the Listbox), I want to present them with a warning - giving the option to either abandon their unsaved changes; or to remain with the current selection, and revert the Listbox selection which they just clicked.

If "Abandon changes" is selected, it works fine. However it runs into trouble when I try to revert the Listbox selection. I use an "EventsOn" Boolean to handle when the Change procedure should proceed, to avoid it calling itself. This seems to work, at the correct point in the code. However after EventsOn is reinstated, and after Exit Sub, it seems that the Change event is called again.

I do not know why the event is firing again. This results in the user being presented with the option a second time.

A lot of the following code has been stripped out because it relates to details of other form controls; loading/saving data from a database; and handling classes and dictionaries. However I have retained the relevant logic of the form controls:

  1. Option Explicit
  2. Dim NumberOfQuestions As Long
  3. Dim EventsOn As Boolean
  4. Dim SelectedListIndex As Long, CurrentQuestion As Long, QuestionSaved As Variant
  5. Private Sub UserForm_Initialize()
  6. ' Stripped out lots of code here. Basically opens a recordset and loads values
  7. ReDim QuestionSaved(1 To NumberOfQuestions) As Boolean
  8. '
  9. For X = 1 To NumberOfQuestions
  10. lbox_QuestionList.AddItem "Question " & X ' Populate the listbox with items
  11. QuestionSaved(X) = True ' Flag the initial state as saved, for each question
  12. If Not X = rst.RecordCount Then rst.MoveNext
  13. Next X
  14. '
  15. ' Select the first question by default. Note that the Listbox ListIndex starts at 0, whereas the questions start at 1
  16. SelectedListIndex = 0
  17. CurrentQuestion = 1
  18. EventsOn = True
  19. lbox_QuestionList.ListIndex = SelectedListIndex
  20. End Sub
  21. Private Sub lbox_QuestionList_Change()
  22. ' Ensure this event does NOT keep firing in a loop, when changed programmatically
  23. If Not EventsOn Then Exit Sub
  24. '
  25. If Not QuestionSaved(CurrentQuestion) Then
  26. If MsgBox(Prompt:="Abandon changes to current question?", Title:="Current question not saved", Buttons:=vbYesNo + vbDefaultButton2) = vbYes Then
  27. ' Abandon changes = Yes
  28. ' Mark as saved
  29. QuestionSaved(CurrentQuestion) = True
  30. ' Then proceed to change as normal
  31. ' (If the user comes back to this question, it will be re-loaded from memory in its original form)
  32. ' This works okay
  33. Else
  34. ' Abandon changes = No
  35. EventsOn = False ' So this sub is not called again
  36. ' REVERT the ListBox selection. Do this by recalling the current question number, and applying it to the ListIndex
  37. SelectedListIndex = CurrentQuestion - 1 ' Remember that the index will be minus 1, due to indexing starting at 0
  38. lbox_QuestionList.ListIndex = SelectedListIndex
  39. EventsOn = True
  40. Exit Sub ' This should be the end of it. But somehow, it's not...
  41. End If
  42. End If
  43. ' Proceed with loading a new question according to the new selected ListIndex
  44. SelectedListIndex = lbox_QuestionList.ListIndex ' Recognise the current selection
  45. ' ListIndex starts at zero, so we need to add 1
  46. CurrentQuestion = SelectedListIndex + 1
  47. ShowQuestion CurrentQuestion
  48. End Sub
  49. Private Sub ShowQuestion(QuestionNumber As Long)
  50. ' Stripped out code for brevity. Basically loads details from a dictionary of classes, and populates into textboxes
  51. End Sub
  52. Private Sub cb_Save_Click()
  53. ' Stipped out code. Takes values of current text boxes and saves them into a class in a dictionary
  54. ' Mark the current question as saved:
  55. QuestionSaved(CurrentQuestion) = True
  56. End Sub
  57. ''''''''''' Event handlers ''''''''''''''
  58. Private Sub tb_Question_Change()
  59. DoChange
  60. End Sub
  61. ' Several other form controls have similar events: all calling "DoChange" as below
  62. Private Sub DoChange()
  63. If Not EventsOn Then Exit Sub
  64. QuestionSaved(CurrentQuestion) = False ' Flag the current question as NOT saved, if any changes are made to form values
  65. End Sub

Naturally, I have searched for this problem - but there are no answers so far which have assisted me:

The logic of my code seems sound. The mystery is why the Change event is being called a second time, even after Exit Sub.

答案1

得分: 2

在查看了一段时间后，似乎从列表框的更改事件中设置其自己的列表索引（实际上是递归调用它）会引发一些奇怪的后端问题。幸运的是，通过将这部分移到自己的函数中进行迁移，可以轻松解决这个问题。经过一些实验，最好的方法是创建一个函数来清除并重新填充列表框，因此请在您的UserForm代码中创建这个函数：

  1. Private Function PopulateListbox(Optional ByVal arg_lSelected As Long = -1)
  2.     
  3.     Me.lbox_QuestionList.Clear
  4.     
  5.     Dim X As Long '
  6.     For X = 1 To NumberofQuestions
  7.         lbox_QuestionList.AddItem "Question " & X ' 填充列表框项
  8.         QuestionSaved(X) = True ' 对于每个问题，标记初始状态为已保存
  9.         ' 如果不是X = rst.RecordCount Then rst.MoveNext
  10.     Next X
  11.     
  12.     Me.lbox_QuestionList.ListIndex = arg_lSelected
  13.     
  14. End Function

现在调整您的初始化事件如下所示（请注意，您需要在此处定义NumberofQuestions，然后在最后调用新函数以填充列表框并选择第一个条目）：

  1. Private Sub UserForm_Initialize()
  2.     ' 在这里剥离了大量的代码。基本上是打开记录集并加载值
  3.     
  4.     NumberofQuestions = 3 ' 这是NumberofQuestions的定义位置
  5.     ReDim QuestionSaved(1 To NumberofQuestions)
  6.     ReDim aAnswers(1 To NumberofQuestions)
  7.     
  8.     '
  9.     ' 默认情况下选择第一个问题。请注意，列表框的ListIndex从0开始，而问题从1开始
  10.     SelectedListIndex = 0
  11.     CurrentQuestion = 1
  12.     EventsOn = True
  13.     PopulateListbox SelectedListIndex ' 调用新函数并设置第一个选择
  14.     
  15. End Sub

最后，将您的列表框更改事件更新为如下所示（基本上只是将列表框条目的设置外包给新函数）：

  1. Private Sub lbox_QuestionList_Change()
  2.     
  3.     ' 确保此事件在以编程方式更改时不会不断触发
  4.     If Not EventsOn Then Exit Sub
  5.     '
  6.     If Not QuestionSaved(CurrentQuestion) Or aAnswers(CurrentQuestion) <> Me.tb_Question.Text Then   ' 我添加了第二个条件用于测试目的，可能在您的完整代码中不必要
  7.         If MsgBox(Prompt:="放弃对当前问题的更改吗？", Title:="当前问题未保存", Buttons:=vbYesNo + vbDefaultButton2) = vbYes Then
  8.             ' 放弃更改 = 是
  9.             ' 标记为已保存
  10.             QuestionSaved(CurrentQuestion) = True
  11.             ' 然后正常进行更改
  12.             ' （如果用户返回到此问题，它将以其原始形式从内存中重新加载）
  13.             ' 这个方法可以正常工作
  14.         Else
  15.             ' 放弃更改 = 否
  16.             EventsOn = False ' 因此不会再次调用此子程序
  17.             ' 恢复ListBox选择。通过重新调用当前问题编号并将其应用于ListIndex来执行此操作
  18.             SelectedListIndex = CurrentQuestion - 1 ' 请记住索引将减去1，因为索引从0开始
  19.             PopulateListbox SelectedListIndex ' 在此处调用您的新函数
  20.             
  21.             EventsOn = True
  22.             Exit Sub ' 这应该是它的结尾。但不知何故它不是...
  23.         End If
  24.     End If
  25.     ' 继续根据新选择的ListIndex加载新问题
  26.     SelectedListIndex = lbox_QuestionList.ListIndex ' 识别当前选择
  27.     ' ListIndex从零开始，所以我们需要加1
  28.     CurrentQuestion = SelectedListIndex + 1
  29.     ShowQuestion CurrentQuestion
  30. End Sub
英文:

After looking into it for awhile, it appears that having the listbox set its own listindex from within its own change event (effectively recursively calling it) causes some weird backend issues. Fortunately, it's easy enough to deal with by migrating that bit out to its own function. After some experimenting, the best way to do it would be to create a function that clears and repopulates the listbox, so create this function in your UserForm code:

  1. Private Function PopulateListbox(Optional ByVal arg_lSelected As Long = -1)
  2. Me.lbox_QuestionList.Clear
  3. Dim X As Long &#39;
  4. For X = 1 To NumberofQuestions
  5. lbox_QuestionList.AddItem &quot;Question &quot; &amp; X &#39; Populate the listbox with items
  6. QuestionSaved(X) = True &#39; Flag the initial state as saved, for each question
  7. &#39;If Not X = rst.RecordCount Then rst.MoveNext
  8. Next X
  9. Me.lbox_QuestionList.ListIndex = arg_lSelected
  10. End Function

Now adjust your Initialize event to look like this (note that you need to define NumberofQuestions here, and then call the new function at the end to populate the listbox and select the first entry):

  1. Private Sub UserForm_Initialize()
  2. &#39; Stripped out lots of code here. Basically opens a recordset and loads values
  3. NumberofQuestions = 3  &#39;This is where NumberofQuestions gets defined
  4. ReDim QuestionSaved(1 To NumberofQuestions)
  5. ReDim aAnswers(1 To NumberofQuestions)
  6. &#39;
  7. &#39; Select the first question by default. Note that the Listbox ListIndex starts at 0, whereas the questions start at 1
  8. SelectedListIndex = 0
  9. CurrentQuestion = 1
  10. EventsOn = True
  11. PopulateListbox SelectedListIndex  &#39;Call the new function and set the 1st selection
  12. End Sub

Lastly, update your listbox_change event to look like this (basically just outsourcing the setting of the listbox entry to the new function):

  1. Private Sub lbox_QuestionList_Change()
  2. &#39; Ensure this event does NOT keep firing in a loop, when changed programmatically
  3. If Not EventsOn Then Exit Sub
  4. &#39;
  5. If Not QuestionSaved(CurrentQuestion) Or aAnswers(CurrentQuestion) &lt;&gt; Me.tb_Question.Text Then   &#39;I added the second condition for testing purposes, may not be necessary in your full code
  6. If MsgBox(Prompt:=&quot;Abandon changes to current question?&quot;, Title:=&quot;Current question not saved&quot;, Buttons:=vbYesNo + vbDefaultButton2) = vbYes Then
  7. &#39; Abandon changes = Yes
  8. &#39; Mark as saved
  9. QuestionSaved(CurrentQuestion) = True
  10. &#39; Then proceed to change as normal
  11. &#39; (If the user comes back to this question, it will be re-loaded from memory in its original form)
  12. &#39; This works okay
  13. Else
  14. &#39; Abandon changes = No
  15. EventsOn = False &#39; So this sub is not called again
  16. &#39; REVERT the ListBox selection. Do this by recalling the current question number, and applying it to the ListIndex
  17. SelectedListIndex = CurrentQuestion - 1 &#39; Remember that the index will be minus 1, due to indexing starting at 0
  18. PopulateListbox SelectedListIndex &#39;Call your new function here
  19. EventsOn = True
  20. Exit Sub &#39; This should be the end of it. But somehow, it&#39;s not...
  21. End If
  22. End If
  23. &#39; Proceed with loading a new question according to the new selected ListIndex
  24. SelectedListIndex = lbox_QuestionList.ListIndex &#39; Recognise the current selection
  25. &#39; ListIndex starts at zero, so we need to add 1
  26. CurrentQuestion = SelectedListIndex + 1
  27. ShowQuestion CurrentQuestion
  28. End Sub

答案2

得分: 2

以下是您提供的代码的翻译部分：

  1. *(谴责OP让这个问题困扰我的脑子！)*
  3. 在我的测试中，我使用了以下UserForm：
  5. [![输入图像描述][1]][1]
  7. 下面的代码使用了`ListBox1_AfterUpdate`事件，我相信它可能适用于您。
  9. ```vb
  10. Option Explicit
  12. Private Const TOTAL_QUESTIONS As Long = 3
  13. Private qSaved As Variant
  14. Private selectedDuringTextboxChange As Long
  15. Private eventsInProgress As Long
  17. Private Sub ListBox1_AfterUpdate()
  18.     Debug.Print "列表框被点击，项目 " & (ListItemSelected() + 1) & " 被选中"
  19.     If eventsInProgress > 0 Then
  20.         Debug.Print "   ... 事件正在进行中，退出"
  21.         eventsInProgress = eventsInProgress - 1
  22.         Exit Sub
  23.     End If
  24.     
  25.     If Not qSaved(selectedDuringTextboxChange) Then
  26.         Dim answer As VbMsgBoxResult
  27.         answer = MsgBox("放弃更改吗？", vbYesNo + vbDefaultButton2)
  28.         If answer = vbYes Then
  29.             Debug.Print "是的，放弃更改"
  30.             qSaved(selectedDuringTextboxChange) = True
  31.         Else
  32.             Debug.Print "不，保留更改"
  33.             '--- 返回到之前选择的列表项
  34.             eventsInProgress = eventsInProgress + 1
  35.             UnselectAll
  36.             ListBox1.Selected(selectedDuringTextboxChange - 1) = True
  37.             ListBox1.ListIndex = selectedDuringTextboxChange - 1
  38.         End If
  39.     End If
  40. End Sub
  42. Private Sub QuitButton_Click()
  43.     Me.Hide
  44. End Sub
  46. Private Sub SaveButton_Click()
  47.     qSaved(ListBox1.ListIndex + 1) = True
  48. End Sub
  50. Private Sub TextBox1_Change()
  51.     selectedDuringTextboxChange = ListBox1.ListIndex + 1
  52.     qSaved(selectedDuringTextboxChange) = False
  53.     Debug.Print "更改了问题 " & selectedDuringTextboxChange & " 的文本"
  54. End Sub
  56. Private Sub UserForm_Initialize()
  57.     ReDim qSaved(1 To TOTAL_QUESTIONS)
  58.     
  59.     selectedDuringTextboxChange = 1
  60.     With ListBox1
  61.         Dim i As Long
  62.         For i = 1 To TOTAL_QUESTIONS
  63.             .AddItem "问题 " & i
  64.             qSaved(i) = True
  65.         Next i
  66.         .Selected(0) = True
  67.     End With
  68.     eventsInProgress = False
  69. End Sub
  71. Private Sub UnselectAll()
  72.     eventsInProgress = eventsInProgress + 1
  73.     With ListBox1
  74.         Dim i As Long
  75.         For i = 0 To .ListCount - 1
  76.             .Selected(i) = False
  77.         Next i
  78.     End With
  79.     eventsInProgress = eventsInProgress - 1
  80. End Sub
  82. Private Function ListItemSelected() As Long
  83.     ListItemSelected = -1
  84.     With ListBox1
  85.         Dim i As Long
  86.         For i = 0 To .ListCount - 1
  87.             If .Selected(i) Then
  88.                 ListItemSelected = i
  89.             End If
  90.         Next i
  91.     End With
  92. End Function
  94. Private Sub WhichListItem_Click()
  95.     With ListBox1
  96.         Dim i As Long
  97.         For i = 0 To .ListCount - 1
  98.             Debug.Print "列表框项目(" & i & ") = " & .Selected(i)
  99.         Next i
  100.     End With
  101.     Debug.Print "eventsInProgress = " & eventsInProgress
  102. End Sub
  2. <details>
  3. <summary>英文:</summary>
  4. *(curses to OP for getting this problem in my brain!)*
  5. In my testing, I used the following UserForm:
  6. [![enter image description here][1]][1]
  7. The code below uses the `ListBox1_AfterUpdate` event, and I believe it may work for you.
  8. Option Explicit
  9. Private Const TOTAL_QUESTIONS As Long = 3
  10. Private qSaved As Variant
  11. Private selectedDuringTextboxChange As Long
  12. Private eventsInProgress As Long
  13. Private Sub ListBox1_AfterUpdate()
  14. Debug.Print &quot;listbox clicked, item &quot; &amp; (ListItemSelected() + 1) &amp; &quot; selected&quot;
  15. If eventsInProgress &gt; 0 Then
  16. Debug.Print &quot;   ... event in progress, exiting&quot;
  17. eventsInProgress = eventsInProgress - 1
  18. Exit Sub
  19. End If
  20. If Not qSaved(selectedDuringTextboxChange) Then
  21. Dim answer As VbMsgBoxResult
  22. answer = MsgBox(&quot;Abandon changes?&quot;, vbYesNo + vbDefaultButton2)
  23. If answer = vbYes Then
  24. Debug.Print &quot;yes, abandon the changes&quot;
  25. qSaved(selectedDuringTextboxChange) = True
  26. Else
  27. Debug.Print &quot;nope, keep the changes&quot;
  28. &#39;--- return to the previously selected list item
  29. eventsInProgress = eventsInProgress + 1
  30. UnselectAll
  31. ListBox1.Selected(selectedDuringTextboxChange - 1) = True
  32. ListBox1.ListIndex = selectedDuringTextboxChange - 1
  33. End If
  34. End If
  35. End Sub
  36. Private Sub QuitButton_Click()
  37. Me.Hide
  38. End Sub
  39. Private Sub SaveButton_Click()
  40. qSaved(ListBox1.ListIndex + 1) = True
  41. End Sub
  42. Private Sub TextBox1_Change()
  43. selectedDuringTextboxChange = ListBox1.ListIndex + 1
  44. qSaved(selectedDuringTextboxChange) = False
  45. Debug.Print &quot;changed text for question &quot; &amp; selectedDuringTextboxChange
  46. End Sub
  47. Private Sub UserForm_Initialize()
  48. ReDim qSaved(1 To TOTAL_QUESTIONS)
  49. selectedDuringTextboxChange = 1
  50. With ListBox1
  51. Dim i As Long
  52. For i = 1 To TOTAL_QUESTIONS
  53. .AddItem &quot;Question &quot; &amp; i
  54. qSaved(i) = True
  55. Next i
  56. .Selected(0) = True
  57. End With
  58. eventsInProgress = False
  59. End Sub
  60. Private Sub UnselectAll()
  61. eventsInProgress = eventsInProgress + 1
  62. With ListBox1
  63. Dim i As Long
  64. For i = 0 To .ListCount - 1
  65. .Selected(i) = False
  66. Next i
  67. End With
  68. eventsInProgress = eventsInProgress - 1
  69. End Sub
  70. Private Function ListItemSelected() As Long
  71. ListItemSelected = -1
  72. With ListBox1
  73. Dim i As Long
  74. For i = 0 To .ListCount - 1
  75. If .Selected(i) Then
  76. ListItemSelected = i
  77. End If
  78. Next i
  79. End With
  80. End Function
  81. Private Sub WhichListItem_Click()
  82. With ListBox1
  83. Dim i As Long
  84. For i = 0 To .ListCount - 1
  85. Debug.Print &quot;listbox item(&quot; &amp; i &amp; &quot;) = &quot; &amp; .Selected(i)
  86. Next i
  87. End With
  88. Debug.Print &quot;eventsInProgress = &quot; &amp; eventsInProgress
  89. End Sub
  90. [1]: https://i.stack.imgur.com/pXyh2.png
  91. </details>
  92. # 答案3
  93. **得分**: 0
  94. Private Sub ListBox_Click() 函数运行两次的问题。
  95. 当我在列表框属性中清除了 ControlSource 后，问题就解决了。我不得不添加一行代码，以明确将列表框中的值写入工作表中的单元格。起初，单元格不会显示数据，所以我将范围名称设置为另一个单元格，那就没问题了。然后，我将新单元格拖放到原始位置。
  96. 我不明白问题从何而来，但修复方法有效。
  97. <details>
  98. <summary>英文:</summary>
  99. I had a problem with a **Private Sub ListBox_Click()** running twice.
  100. When I cleared the *ControlSource* in the list box properties it fixed the problem. I had to add a line of code to specifically write the value from the *listbox* to the cell in the worksheet. At first the cell would not display the data so I set the range name to another cell and that was OK. So, I then dragged and dropped the new cell into the original location. 
  101. I don&#39;t understand where the problem originated, but the fix worked. 
  102. </details>
  103. # 答案4
  104. **得分**: 0
  105. I had a similar unexpected issue, so maybe someone will find this result helpful.
  106. Within a multi-selection-enabled Listbox_Change event, I checked the value of the currently-selected item to see whether it had been checked or unchecked.
  107. ``` Private Sub lstBox_Change()
  108. With lstBox
  109. If .Selected(.ListIndex) Then
  110. ' Call Method A.
  111. Else
  112. ' Call Method B.
  113. End If
  114. End With
  115. End Sub

当列表被选中时，它会正确检测到选择并调用 A，但是，当代码执行到 Change 事件的 End Sub 时，复选框会自动取消选择，并再次触发 Change 事件。请注意，我没有在 ListBox 中设置任何值；我只是检查当前项目是否被选中或取消选中。但不知何故，这会触发它自动取消选择。 （此外，这似乎只发生在对 Change 事件的第一次调用上。之后它表现正常。）

我尝试了一些其他修复方法，但 BeforeUpdate 和 AfterUpdate 似乎根本不会触发。当我将选择测试移出 If 语句并将结果放入布尔变量中时，问题消失了：

  1.         With lstBox
  2. BooleanResult = (.Selected(.ListIndex) = True)
  3. If BooleanResult Then
  4. ' Call Method A.
  5. Else
  6. ' Call Method B.
  7. End If
  8. End With
  9. End Sub

之后，ListBox 一直按预期行为。

英文:

I had a similar unexpected issue, so maybe someone will find this result helpful.
Within a multi-selection-enabled Listbox_Change event, I checked the value of the currently-selected item to see whether it had been checked or unchecked.

  1.     Private Sub lstBox_Change()
  2. With lstBox
  3. If .Selected(.ListIndex) Then
  4. &#39; Call Method A.
  5. Else
  6. &#39; Call Method B.
  7. End If
  8. End With
  9. End Sub

When the list was checked, it would properly detect the selection and call A--but then, when stepping through the code and reaching the Change event's End Sub, the checkbox would automatically become unselected, and the Change event would fire again. Note that I wasn't setting any value in the ListBox itself; I was only checking to see whether the current item was checked or unchecked. But, somehow, that triggered it to unselect itself. (Also, this only seemed to happen on the first call to the Change event. Thereafter it behaved normally.)</p>
I tried some of the other fixes, but BeforeUpdate and AfterUpdate never seemed to fire at all. The problem went away when I moved the selection test outside of the If statement and put the result into a Boolean instead:

  1.     Private Sub lstBox_Change()
  2. With lstBox
  3. BooleanResult = (.Selected(.ListIndex) = True)
  4. If BooleanResult Then
  5. &#39; Call Method A.
  6. Else
  7. &#39; Call Method B.
  8. End If
  9. End With
  10. End Sub

After that, the ListBox consistently behaved as expected.

huangapple
  • 本文由 发表于 2020年1月3日 23:15:59
  • 转载请务必保留本文链接：https://go.coder-hub.com/59581009.html
  • excel
  • listbox
  • userform
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定