list of worksheet and dialogbox to select

[luckyearl]

Hi
I am trying to get list of all worksheets, popup Dialog box to select a worksheet, then work with it.
I thought cud store the sheetname in array, but does not work, please suggest. Thank you

Sub ListSheets()
Dim ws As Worksheet
Dim i As Integer
Dim ArrayValues As ArrayList         ' This does not work'
Set ArrayValues = New ArrayList        ' This does not work'
 
 For i = 1 To ThisWorkbook.Worksheets.Count
         ArrayValues.Add ThisWorkbook.Worksheets(i).name
    Next sht
ListBox1.List = ArrayValues
End Sub

 

[Fluff]

Do you want the list sorted?

 

[luckyearl]

yes please, i gues it would be easier to look for particular worksheet
Thank you

 

[Fluff]

Ok, how about

Sub ListSheets()
Dim ws As Worksheet
Dim i As Integer
Dim ArrayValues As Object
Set ArrayValues = CreateObject("system.collections.arraylist")
 
 For i = 1 To ThisWorkbook.Worksheets.Count
         ArrayValues.Add ThisWorkbook.Worksheets(i).Name
    Next i
    ArrayValues.Sort
ListBox1.List = ArrayValues.toarray
End Sub

 

[luckyearl]

Thank you for help

Got an Run-time error '429', ActiveX component cant create object ? is it due to Macos
Cheers

 

[Fluff]

Yes it is, you cannot use activex components on a Mac.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[luckyearl]

Thank you, just updated the profile

 

[Fluff]

Thanks for that, how about

Sub ListSheets()
Dim ws As Worksheet
Dim i As Integer
Dim ArrayValues As Variant
ReDim ArrayValues(1 To ThisWorkbook.Worksheets.Count)
 For i = 1 To ThisWorkbook.Worksheets.Count
         ArrayValues(i) = ThisWorkbook.Worksheets(i).Name
    Next i
ListBox1.List = ArrayValues
End Sub

 

[luckyearl]

Thanks for that, how about

Sub ListSheets()
Dim ws As Worksheet
Dim i As Integer
Dim ArrayValues As Variant
ReDim ArrayValues(1 To ThisWorkbook.Worksheets.Count)
 For i = 1 To ThisWorkbook.Worksheets.Count
         ArrayValues(i) = ThisWorkbook.Worksheets(i).Name
    Next i
ListBox1.List = ArrayValues
End Sub

Thank you, tells me → Object required

 

[Fluff]

On which line?