Excel ActiveX Listbox在文件打开时未启用 [英] Excel ActiveX Listbox not enabled on file open

问题描述

我正在想出一个奇怪的情况。

I am trying to figure out a bizarre situation.

我有一张包含许多工作表的工作簿。在一张表上，我有一个ActiveX列表框（CTOverview.Listbox1）。在第二张表上，我共有三个列表框（CTSelected.Listbox1通过Listbox3）。我正在使用一个查询来填充具有相同数据的两张表上的Listbox1。代码如下：

I have a workbook with many sheets. On one sheet, I have one ActiveX listbox (CTOverview.Listbox1). On a second sheet, I have a total of three listboxes (CTSelected.Listbox1 thru Listbox3). I am using a query to populate Listbox1 on both sheets with the same data. The code for this is below:

strSQL = "Select Distinct [Region] From [UniqueCTList$] Order by [Region]"
closeRS
OpenDB

' initialize listboxes
CTSelect.ListBox1.Clear
CTSelect.ListBox2.Clear
CTSelect.ListBox3.Clear
CTOverview.ListBox1.Clear

' initialize with entire division value
CTSelect.ListBox1.AddItem "Entire Division"
CTOverview.ListBox1.AddItem "Entire Division"

' initialize selected Tech
CTData.Range("CT_Selected") = ""

' populate listboxes using recordset
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

If rs.RecordCount > 0 Then
    Do While Not rs.EOF
        CTOverview.ListBox1.AddItem rs.Fields(0)
        CTSelect.ListBox1.AddItem rs.Fields(0)
        rs.MoveNext
    Loop
Else
    MsgBox "I was not able to find any unique Regions.", vbCritical + vbOKOnly
    Exit Sub
End If

这段代码工作很好（不是我的代码 - 我从交换器获得）。两张纸上的LIstbox1都与不同区域的列表加载。但是，我没有得到CTOverview.Listbox1来响应任何输入，直到我从CTSelected.Listbox1中选择了一些。一旦我这样做，Listbox1的工作正常，没有以其他方式连接，至少我可以告诉。

This code works beautifully (not my code - I got it from the intertubes). Both LIstbox1's on both sheets load with the list of distinct regions. However, I cannot get CTOverview.Listbox1 to respond to any input until I've selected something from CTSelected.Listbox1. Once I do that, both Listbox1's work normally and are not otherwise connected, at least as far as I can tell.

我已经尝试关闭记录集，两个Listbox1的（没有效果）。
我已经尝试在CTOverview.Listbox1中选择一个默认项目（无效果）。

I have tried closing the recordset after I've populated the two Listbox1's (no effect). I have tried selecting a default item in CTOverview.Listbox1 (no effect).

如果重要，这里是我的代码打开/关闭记录集：

Just in case it matters, here is my code to open/close the recordset:

Public Sub OpenDB()
    If cnn.State = adStateOpen Then cnn.Close

    cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; _
        DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

    cnn.Open
End Sub


Public Sub closeRS()
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
End Sub

尽可能告诉我，我必须关注CTOverview表。如果我点击任何其他工作表，然后单击回到CTOverview，Listbox1似乎工作。这是一个问题，因为CTOverview应该是我的发布表。

As far as I can tell, I have to take focus off of the CTOverview sheet. If I click onto any other sheet, then click back to CTOverview, Listbox1 seems to work. This is a problem because CTOverview is supposed to be my launch sheet.

任何想法为什么会发生这种情况？我拉出我的头发试图弄清楚这一点。任何见解将不胜感激。

Any idea why this might be happening? I'm pulling out my hair trying to figure this out. Any insights would be much appreciated.

推荐答案

Siddharth Rout在评论中提供了答案。在填充列表框后，在最后（刚刚结束之前）激活sheet2，在下一行激活启动表。使用 Application.Screenupdating = false 确保没有屏幕闪烁。

Siddharth Rout provided the answer in a comment. After you have populated the listbox, right at the end (just before end sub) activate sheet2 and in the next line activate the launch sheet. Use Application.Screenupdating =false to ensure that there is no screen flickering.

这篇关于Excel ActiveX Listbox在文件打开时未启用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！