Allow Users to select A Sheet

[rwilfing]

Is it possible to create a listbox that populates the name of all the sheets in a workbook and allow the user to activate that sheet to finish running the macro?

Thanks in Advance

 

[RSXchin]

If someone can tell me how to have a listbox without creating a userform... I think I can help.

 

[mikerickson]

One workaround would be to use Application.InputBox with type:=8, to have the user navigate to that sheet normaly with the mouse.

 

[RSXchin]

But can you have a listbox or combobox show up without making a userform?
a la msgbox or inputbox?

 

[mikerickson]

You could use a CommandBarComboBox

Option Explicit
Const cbName As String = "myTemp"

Sub SelectASheet()
    Dim oneSheet As Worksheet
    
    On Error Resume Next
    Application.CommandBars(cbName).Delete
    On Error GoTo ErrorOut
    
    With Application.CommandBars.Add(Name:=cbName, Position:=msoBarFloating, temporary:=True)
        .Left = 300
        .Top = 200
        .Visible = True
        
        With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
            .Width = 150
            .Height = 30
            .Visible = True
            
            For Each oneSheet In ThisWorkbook.Worksheets
                .AddItem oneSheet.Name
                If oneSheet.Name = ActiveSheet.Name Then
                    .ListIndex = .ListCount
                End If
            Next oneSheet
            
            .OnAction = "GoToSelectedSheet"
        End With
        
    End With
    
ErrorOut:
    On Error GoTo 0
End Sub

Sub GoToSelectedSheet(Optional dummy As Boolean)
    On Error Resume Next
    With Application.CommandBars(cbName)
    
        With .Controls(1)
            ThisWorkbook.Sheets(.List(.ListIndex)).Activate
        End With
        
        '.Visible = False: Rem optional line
        
    End With
    On Error GoTo 0
End Sub

 

[RSXchin]

http://www.mrexcel.com/forum/showthread.php?t=563921