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]

That should be Ok, is the sheet protected?

 

[luckyearl]

That should be Ok, is the sheet protected?

Nope, Unprotected

 

[Fluff]

In that case I don't know why you get the error. It may just be one of the differences between VBA on a Mac & on a PC.
As I don't have a Mac, there's not much more I can do to help.

 

[luckyearl]

Thank you Fluff for helping me
Have gud day

 

[Fluff]

Sorry I couldn't have been of more help.

 

[luckyearl]

@Fluff Thank you for time you gave me, Thanks Again
This worked, not sure why Sheet"Index" did not work
I selected Index sheet →ran the code

With Selection.Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Join(ArrayValues, ",")
 End With

 

[Fluff]

Glad you sorted it & thanks for the feedback.