list of worksheet and dialogbox to select

luckyearl

New Member
Joined
Nov 6, 2016
Messages
31
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
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
VBA Code:
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
 
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
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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?
 
Upvote 0
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
 
Upvote 0
Can you answer my other questions?
 
Upvote 0
Ok, try
VBA Code:
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
 
Upvote 0
Ok, try
VBA Code:
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
 
Upvote 0
Would a data validation dropdown do?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top