Macro does not work in a duplicate worksheet

Good morning. I have a Microsoft Excel Macro Enabled Workbook with two worksheets: let's say Sheet1 and Sheet2. In Sheet2 I have a combo box (form control) that works as a table sorter. This table will be also in Sheet2. The combo uses the following code:

Option Explicit

Sub DropDown4_Change()
    Dim comboValue As String
    Dim Key1ColumnIndex As Integer
    Dim Key2ColumnIndex As Integer

    'You can get the name by doing something like this in the immediate window:  "? ActiveSheet.Shapes(1).OLEFormat.Object.Name"
    comboValue = ActiveSheet.Shapes("Drop Down 4").ControlFormat.List(ActiveSheet.Shapes("Drop Down 4").ControlFormat.ListIndex)

    Select Case comboValue

        Case "By Keyphrase"
            Key1ColumnIndex = 18
            Key2ColumnIndex = 19
        Case "By Region"
            Key1ColumnIndex = 19
            Key2ColumnIndex = 18
        Case "Default"
            Key1ColumnIndex = 1
            Key2ColumnIndex = 1
    End Select


   Range("DataValues").sort Key1:=Range("DataValues").Cells(1, Key1ColumnIndex), _
                            Order1:=xlDescending, Header:=xlNo, DataOption1:=xlSortNormal, _
                            Key2:=Range("DataValues").Cells(1, Key2ColumnIndex), order2:=xlDescending
End Sub

This code works like charm in this worksheet.

I'm using Aspose Cells for Java to use this Excel Workbook as a template to generate new workbooks with multiple datasheets based in Sheet2 copies (which contains my combo) but the problem is that when I do this, the combo doesn't work anymore as it did in the template.

In this line:

comboValue = ActiveSheet.Shapes("Drop Down 4").ControlFormat.List(ActiveSheet.Shapes("Drop Down 4").ControlFormat.ListIndex)

I get this error:

Run-time error '438' Object doesn't support this property or method

It looks like ControlFormat is not being recognized as a valid method for the combo shape. This happens whether you use the combo name or the combo index (in this case is always 6). "Drop Down 4" is the right name. I have alerted the name several times in each worksheet and both the index and the name are right.

So I hope you guys can help me out. Thank you for your patien开发者_高级运维ce and sorry if my English is not clear enough. Feel free to ask questions.

I figured it out by myself that hard coding the combo name (in this case "Drop Down 4") is a terrible way to do this since Excel assigns new names every time a copy of Sheet2 is added. Although Excel does this, combo names always starts with the word "Drop" (from Drop Down). I modified a little bit the code and made it work:

Option Explicit

Sub DropDown4_Change()
    Dim comboValue As String
    Dim Key1ColumnIndex As Integer
    Dim Key2ColumnIndex As Integer
    Dim Index As Integer
    Dim comboName As String
    Dim comboName2 As String
    Dim comboID As Integer

    'You can get the name by doing something like this in the immediate window:  "? Sheet1.Shapes(1).OLEFormat.Object.Name"

     For Index = 1 To ActiveSheet.Shapes.Count
        comboName = ActiveSheet.Shapes(Index).OLEFormat.Object.Name
        If InStr(comboName, "Drop") > 0 Then
            'MsgBox InStr(comboName, "Drop")
            comboName2 = comboName
            comboID = Index
        End If
     Next


    comboValue = ActiveSheet.Shapes(comboID).ControlFormat.List(ActiveSheet.Shapes(comboID).ControlFormat.ListIndex)

    Select Case comboValue

        Case "By Keyphrase"
            Key1ColumnIndex = 18
            Key2ColumnIndex = 19
        Case "By Region"
            Key1ColumnIndex = 19
            Key2ColumnIndex = 18
        Case "Default"
            Key1ColumnIndex = 1
            Key2ColumnIndex = 1
    End Select


   Range("DataValues").sort Key1:=Range("DataValues").Cells(1, Key1ColumnIndex), _
                            Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortNormal, _
                            Key2:=Range("DataValues").Cells(1, Key2ColumnIndex), order2:=xlAscending
End Sub

Where is your code located?

This worked for me (in a general module)...

Sub DoSorting()
Dim dd, val

    Set dd = ActiveSheet.Shapes(Application.Caller)
    val = dd.ControlFormat.List(dd.ControlFormat.ListIndex)

    MsgBox val

End Sub

Copying the sheet did not break it.