ControlFormat.ListIndex runtime error Excel 2007

问题:

I'm trying to research an issue with a listbox (Form Control) on a worksheet. I need to know if it is handled different in office 2007 and 2010 and later.

In Excel 2007, add a FormControl ListBox to sheet1, and fill it with some random values. Verify that the selection type is set to single and run the following code in a standard module:

Sub TestListIndexExcel2007()
    Debug.Print ThisWorkbook.Worksheets(1).Shapes(1).ControlFormat.ListIndex
End Sub

The immediate window should return the index of the selected item in the listbox, so far no problem.

Now set the selectiontype of the listbox to Multi or Extend but only select 1 item in the listbox and run the sub above again. I need to know if it generates the error below or if it still spits out a listindex in Excel 2007.

Run-time Error '1004':

Unable to get the ListIndex property of the ListBox class

I would test this myself but I don't have access to Office 2007.

回答1:

No it doesn't. The first error you will get is a 438 error since a Shape doesn't have a FormControl property. If you replace that with ControlFormat then yes you'll get a 1004 error.