CODEWITHSUNDEEP
vbaexcel
MGP
MGP

Reputation: 2551

Prompt user to select a worksheet

I have a Macro, that prompts a user to select an Excel file, like this:

Dim thisBook As Workbook, newBook As Workbook
Dim fd As FileDialog
Dim oFD As Variant
Dim fileName As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xls", 1
        .Title = "Choose the Report"
        .InitialView = msoFileDialogViewDetails
        .Show
        For Each oFD In .SelectedItems
            fileName = oFD
        Next oFD
        On Error GoTo 0
 End With

 If fd.SelectedItems.Count = 0 Then

     Exit Sub

 End If

 Set thisBook = ActiveWorkbook
 Set newBook = Workbooks.Open(fileName)

This works fine, what I want to do now, and what I failed to find in the internet is the following:

I want to prompt the user to select a worksheet from the newbook, since the sheet name could not be the same in the future.

I came up with this, but I am not very happy with it, since it is rather inconvenient, to let the user type the sheet name:

Function WorksheetExists(WSName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Worksheets(WSName).Name = WSName
    On Error GoTo 0
End Function

Function q() As String
Dim shname As String
Do Until WorksheetExists(shname)
shname = InputBox("Enter sheet name")
If Not WorksheetExists(shname) Then MsgBox shname & " doesn't exist!", vbExclamation
Loop
q = shname
End Sub

Is there a way, to maybe let the user select the sheet name, from all the sheet names? (I am not using an Userform, the Macro starts, if the user clicks a Button)

Upvotes: 1

Views: 16502

Answers (2)

Cherif Diallo
Cherif Diallo

Reputation: 341

Use an InputBox and have the user select any cell inside the desired/target sheet. Setting the type to 8 returns a Range. From this range, you can get the worksheet then its name.

Dim desiredSheetName as String
desiredSheetName = Application.InputBox("Select any cell inside the target sheet: ", "Prompt for selecting target sheet name", Type:=8).Worksheet.Name
Debug.Print desiredSheetName

Upvotes: 2

Davesexcel
Davesexcel

Reputation: 6984

Make a userform with a blank ListBox and use this code in the userform module

Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Sheets
        ListBox1.AddItem sh.Name
    Next sh
End Sub

Private Sub ListBox1_Click()
    Sheets(ListBox1.Value).Activate
    Unload Me
End Sub

Upvotes: 1

Related Questions