Excel Forum

Code: Select all

Option Explicit
Dim combolist() As String

Sub addComboList()
Dim countObj As Integer, i As Integer
Dim j As Integer, k As Integer
Dim sheetName As String
ReDim combolist(1, 1)
For k = 1 To Sheets.Count
            sheetName = ActiveWorkbook.Sheets(k).Name
            Sheets(k).Select
            countObj = ActiveSheet.OLEObjects.Count
        For i = 1 To countObj
                If i = 1 Then
                    combolist(0, 0) = sheetName
                    combolist(1, 0) = ActiveSheet.OLEObjects(i).Name
                ElseIf i = 2 Then
                    combolist(0, 1) = sheetName
                    combolist(1, 1) = ActiveSheet.OLEObjects(i).Name
                ElseIf i >= 3 Then
                ReDim Preserve combolist(1, i)'<== เปลี่ยนได้เฉพาะ Dimension ที่สอง
                    combolist(0, i) = ActiveSheet.Name
                    combolist(1, i) = ActiveSheet.OLEObjects(i).Name
                End If
        Next i
Next k
End Sub