最小化工作簿时查询表单组合时出错 [英] Error querying forms combo when workbook is minimized

问题描述

我目前正在考虑更新现有的基于Excel的应用程序，以便在2016年运行。目前我正在尝试管理应用程序中不同工作簿的可见性和更新，我遇到了这个问题。

我在工作表上有一个表单列表框。在初始化期间，我需要获取此控件的当前ListIndex值。 

当工作簿处于正常状态时，这不会有任何问题。当我将其设置为最小化时，它会抛出1004错误（"无法获取Dropdown类的ListIndex属性"），当我在Locals窗口中查看时，我可以看到
中没有属性可以使用ControlFormat对象。

这是我用来检查的代码：Test_window_states在两个视图之间切换。

这种行为是期望的还是在这种情况下有另一种获取此属性的方法？ FWIW，在更新期间设置wdwActive.visible = FALSE不会导致问题，并且当我使用
ActiveX组合框时我没有收到错误。不幸的是，这两种解决方案都会导致其他问题！ 

 Sub Check_Forms_Combo（）
 
 Dim wksMain As Worksheet 
 Dim shpCombo As Shape 
 
 
设置wksMain = ThisWorkbook.Sheets（gsC_SHEET_MAIN）
设置shpCombo = wksMain.Shapes （gsC_FORMS_COMBO）
 
如果不是shpCombo则没有那么
 
 MsgBox shpCombo.ControlFormat.ListIndex 
 
结束如果
 
设置wksMain = Nothing 
设置shpCombo = Nothing 
 
 
结束Sub 
 
 Sub Test_Window_States（）
 
 Dim wdwActive As Window 
 
设置wdwActive = Application.Windows（ThisWorkbook.Name）
 
 wdwActive.WindowState = xlMaximized 
 
 Call Check_Forms_Combo 
 
 wdwActive .WindowState = xlMinimized 
 
调用Check_Forms_Combo 
 
 wdwActive.WindowState = xlMaximized 
 
设置wdwActive =无
 
结束子

解决方案

嗨The_Cosh，

根据我的测试，你可以尝试以下代码来避免这个错误：

 Option Explicit 
 Sub Check_Forms_Combo（）
 Dim wdwActive As Window 
设置wdwActive = Application.Windows（ThisWorkbook.Name）
 wdwActive.WindowState = xlMaximized 
 Dim shpCB As Shape 
 Dim sVal As String 
 Set shpCB = ActiveSheet.Shapes（" myCombo"）
 sVal = shpCB.ControlFormat.List（shpCB.ControlFormat.ListIndex）
 Range（" L1"）。 Value = sVal 
 MsgBox sVal 
 End Sub 
 Sub Test_Window_States（）
 Dim wdwActive As Window 
 Set wdwActive = Application.Windows（ThisWorkbook.Name）
 wdwActive.WindowState = xlMaximized 
 Call Che ck_Forms_Combo 
 wdwActive.WindowState = xlMinimized 
 Call Check_Forms_Combo 
 wdwActive.WindowState = xlMaximized 
 Set wdwActive = Nothing 
 End Sub 

但这可能无法解决您的问题。

所以我们转载了您的问题，并将其报告给相关团队。

希望它可以帮到你。

最好的问候，

纪

I am currently looking at updating an existing Excel based application to run in 2016. At the moment I'm trying to manage the visibility and update of different workbooks within the application and I've come across this problem.

I have a Forms Listbox on a worksheet. During initialisation, I need to get the current ListIndex value of this control. 

This works no problem when the workbook is in an normal state. When I set it to be minimized, it throws a 1004 error ("Unable to get ListIndex property of the Dropdown class") and when I have a look in the Locals window, I can see that none of the properties of the ControlFormat object are available.

Here's the code I'm using to check this: Test_window_states switches between the two views.

Is this kind of behaviour expected or is there another way of getting this property in this situation? FWIW, setting wdwActive.visible = FALSE during the update does not cause a problem and I don't get an error when I use an ActiveX combobox. Unfortunately, both these solutions cause other problems! 

Sub Check_Forms_Combo()

    Dim wksMain As Worksheet
    Dim shpCombo As Shape
    
    
    Set wksMain = ThisWorkbook.Sheets(gsC_SHEET_MAIN)
    Set shpCombo = wksMain.Shapes(gsC_FORMS_COMBO)
    
    If Not shpCombo Is Nothing Then
    
        MsgBox shpCombo.ControlFormat.ListIndex
    
    End If
    
    Set wksMain = Nothing
    Set shpCombo = Nothing
    
    
End Sub

Sub Test_Window_States()

    Dim wdwActive As Window
    
    Set wdwActive = Application.Windows(ThisWorkbook.Name)
    
    wdwActive.WindowState = xlMaximized
    
    Call Check_Forms_Combo
    
    wdwActive.WindowState = xlMinimized
    
    Call Check_Forms_Combo
    
    wdwActive.WindowState = xlMaximized
    
    Set wdwActive = Nothing
    
End Sub

解决方案

Hi The_Cosh,

Base on my test, you can try the following code to avoid this error:

Option Explicit
Sub Check_Forms_Combo()
    Dim wdwActive As Window
    Set wdwActive = Application.Windows(ThisWorkbook.Name)
    wdwActive.WindowState = xlMaximized
    Dim shpCB As Shape
    Dim sVal As String
    Set shpCB = ActiveSheet.Shapes("myCombo")
    sVal = shpCB.ControlFormat.List(shpCB.ControlFormat.ListIndex)
    Range("L1").Value = sVal
    MsgBox sVal
End Sub
Sub Test_Window_States()
    Dim wdwActive As Window    
    Set wdwActive = Application.Windows(ThisWorkbook.Name)    
    wdwActive.WindowState = xlMaximized    
    Call Check_Forms_Combo    
    wdwActive.WindowState = xlMinimized    
    Call Check_Forms_Combo    
    wdwActive.WindowState = xlMaximized    
    Set wdwActive = Nothing
End Sub

But that may not really solve your issue.

So we reproduced your problem, and will report it to the related team.

Hopefully it helps you.

Best Regards,

Yuki

这篇关于最小化工作簿时查询表单组合时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！