Popup list to select worksheet names

[sparky2205]

I have a macro that allows users to rename a worksheet on a locked workbook.
The first step is to enter the name of the worksheet to be renamed.
Currently the user must manually enter the name.
I once came across a piece of code that popped up a list of worksheet names that allowed the user select one.
Alas, that was years ago, and I no longer have that code.
Does anyone know how I might achieve this?

 

[rlv01]

There are many ways to do this, but they all involve looping through the worksheets in a workbook to collect the names. One example:

'Create an array of worksheet names
Sub MakeSheetListExampleArr()
    Dim WS As Worksheet, I As Long, arrWS As Variant
    
    With ActiveWorkbook
        ReDim arrWS(1 To .Worksheets.Count)
        For Each WS In .Worksheets
            Select Case WS.Name
                Case "Sheet2", "Sheet4", "Sheet6"     'any worksheets to be excluded go here
                Case Else
                    I = I + 1
                    arrWS(I) = WS.Name
            End Select
        Next WS
        
        If I < .Worksheets.Count Then
            ReDim Preserve arrWS(1 To I) 'account for any ignored worksheets
        End If
    End With
    Debug.Print Join(arrWS, ",")
End Sub

Another:

'Create a delimited string with worksheet names
Sub MakeSheetListExampleCSV()
    Dim WS As Worksheet, I As Long, strWS As String
    
    With ActiveWorkbook
        ReDim arrWS(1 To .Worksheets.Count)
        For Each WS In .Worksheets
            Select Case WS.Name
                Case "Sheet2", "Sheet4", "Sheet6"     'any worksheets to be excluded go here"
                Case Else
                    I = I + 1
                    strWS = strWS & WS.Name & ","
            End Select
        Next WS
        
        strWS = Left(strWS, Len(strWS) - 1)
    End With
    
    Debug.Print strWS
End Sub

As to the "popping up" part - or how you use the worksheet list to allow the user to select depends on a lot on what design choices you have made. The more sophisticated method use some kind of control like a ListBox or ComboBox, either on the worksheet or in a UserForm to display the list and then route the user's choice to your other code. Code to create a cell validation list is another way to go. Or if there are not too many sheets, you can set up an InputBox to display a list and allow the user to select a number instead of typing the full worksheet name. And probably 10 more ways that I am not thinking of right now.

 

[sparky2205]

Hey, thanks for your time on this.
I was able to achieve what I wanted by replacing:

sName = InputBox("Enter the worksheet name to copy")

with

Application.CommandBars("Workbook tabs").ShowPopup
sName = ActiveSheet.Name