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
 
That should be Ok, is the sheet protected?
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
Sorry I couldn't have been of more help.
 
Upvote 0
@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
VBA Code:
With Selection.Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Join(ArrayValues, ",")
 End With
 
Upvote 0
Solution
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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