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

 

[luckyearl]

Thanks @Fluff

I think its at line→ ListBox1.List = ArrayValues

I am not sure how to use ListBox to Select a worksheet from listbox, assign it to a variable

Cheers

 

[Fluff]

Is the listbox on a userform, or a sheet?
If the latter what is the sheet name & what is the exact name of the listbox?

Also does this just need to work in xl365, or does it also need to work in earlier versions?

 

[luckyearl]

Is the listbox on a userform, or a sheet?
If the latter what is the sheet name & what is the exact name of the listbox?

Also does this just need to work in xl365, or does it also need to work in earlier versions?

Hi Fluff
I cud not find userform on mac version of excel, I use 365 version more often, 365 wud do for me. Thank you

 

[Fluff]

Can you answer my other questions?

 

[luckyearl]

Can you answer my other questions?

Apologies, working on sheet"Index", name workSheetBox
Thank you

 

[Fluff]

Ok, try

Sub ListSheets()
   Dim ws As Worksheet
   Dim i As Long, j As Long
   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
   Sheets("Index").Shapes("workSheetBox").ControlFormat.List = Application.Sort(ArrayValues, 1, 1, 1)
End Sub

 

[luckyearl]

Ok, try

Sub ListSheets()
   Dim ws As Worksheet
   Dim i As Long, j As Long
   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
   Sheets("Index").Shapes("workSheetBox").ControlFormat.List = Application.Sort(ArrayValues, 1, 1, 1)
End Sub

Hi Fluff
tried it → Application-defined or object-defined error
I am not hung up on listBox, all i wud like is a dialog to select from list of worksheets, and set up variable to selected worksheet to work with it
Cheers

 

[Fluff]

Which line?

 

[luckyearl]

Which line?

Sheets("Index").Shapes("workSheetBox").ControlFormat.List = Application.Sort(ArrayValues, 1, 1, 1)

I think I do not know how to do ListBox

 

[Fluff]

Would a data validation dropdown do?