Easier way to have user select workbook and then a sheet in the workbook selected?

[johnnyL]

Code I have now asks the user to select a workbook, Open the workbook selected, and then ask the user to select a number of the worksheet to use for later calculations. The problem I forsee is the user has to know the number of the worksheet. I would much rather have the user be able to click on a worksheet tab or be given an option of the the worksheet names available in the opened workbook.

The current code converts the sheet number entered to a sheet name, but like I said, the current code assumes the user knows the sheet number desired, which could present a problem if that sheet number is not known by the user.

This is the code I have come up with thus far:

Sub ChooseWorkbookAndSheetNumberToUse()
'
    Dim fileBrowse              As FileDialog
    Dim UserSheetNumber         As Long
    Dim UserSheetConvertedName  As String
'
    Set fileBrowse = Application.FileDialog(msoFileDialogOpen)
'
    If fileBrowse.Show = True Then wbPath = fileBrowse.SelectedItems(1)
'
    With Workbooks.Open(wbPath)
        UserSheetNumber = InputBox("Enter the number of the sheet you want to use.")    ' Ask the user which sheet number to use
'
        UserSheetConvertedName = Sheets(CLng(UserSheetNumber)).Name                     ' Convert sheet # to the actual sheet name ... TY @Fluff ;)
'
        MsgBox "Sheet # Chosen by user = " & UserSheetNumber & vbCrLf & vbCrLf & "Sheet number selected by user converted to the corresponding sheet name = " & UserSheetConvertedName
        Workbooks.Open(wbPath).Close                                                    ' Close the user chosen workbook
    End With
End Sub

 

[richh]

Hey johnny,

just an idea:

1. Create a new Userform with a combobox.
2. When the workbook they select is opened, write a loop that collects the names of the worksheets and adds them to the combobox
3. The user can then simple select the worksheet they want to use.
4. Write some code that opens the worksheet that the user selected
   

 

[EXCEL MAX]

Hello JohnnyL,
try this...
Insert class module and call it "Class1".
Open this class in the VBA editor and insert this code...

Public WithEvents vApp As Application

Private Sub vApp_SheetActivate(ByVal Sh As Object)
   
    MsgBox "Active sheet is " & Sh.Name
   
End Sub

Change your procedure to look something like this...

Dim vClass As New Class1

Sub ChooseWorkbookAndSheetNumberToUse()

    Dim fileBrowse As FileDialog
    Dim UserSheetNumber As Long
    Dim UserSheetConvertedName As String
   
    Set vClass.vApp = Application
    Set fileBrowse = Application.FileDialog(msoFileDialogOpen)
    If fileBrowse.Show = True Then wbPath = fileBrowse.SelectedItems(1)
    Workbooks.Open (wbPath)

End Sub

 

[johnnyL]

Thank you @richh & @EXCEL MAX for the suggestions. I was looking for a more straight forward approach.

So today I came up with a new version that seems to be more user friendly, ie. idiot proof:

Sub ChooseWorkbookAndSheetNumberToUseV2()                                                           ' Open a workbook & select a sheet to make changes to
'
    Dim UserSelectedSheet       As String
    Dim UserSelectedFile        As String
    Dim SourceWorkbook          As Workbook
'
    UserSelectedFile = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", Title:="Select the Excel Workbook")

    If UserSelectedFile = "False" Then Exit Sub                                                     ' If user cancels then exit
'
    Set SourceWorkbook = Workbooks.Open(UserSelectedFile)                                           ' Open the selected file
'
    Worksheets(1).Activate                                                                          ' Had to add this to allow the sheet selected to work
'
    MsgBox "In the next popup window, Click the sheet name " & vbCrLf & "that you would like to make changes to." ' Advise user what to do next
'
    If Application.CommandBars("workbook tabs").Controls(16).Caption Like "More Sheets*" Then       ' This CommandBars only shows up to 16 sheets, after that it
'                                                                                                       will display the first 15 sheets and the 16th item will be
'                                                                                                       an option of "More Sheets..." which will give full list
        Application.CommandBars("workbook tabs").Controls(16).Execute                               ' If more than 16 sheets are found, click the "More Sheets ..."
    Else                                                                                            ' Otherwise ...
        Application.CommandBars("Workbook Tabs").ShowPopup 800, 300                                 '   Display the sheets found
    End If
'
    UserSelectedSheet = ActiveSheet.Name                                                            ' Save the sheet name that the user selected
    Debug.Print UserSelectedSheet                                                                   ' Test print, for verification, name of user selected sheet
'
'   Add desired additional coding here ...
'
    SourceWorkbook.Close savechanges:=True                                                          ' Save changes made to SourceWorkbook and close it
End Sub

Again, I am not sure if there is an easier/shorter way to do it, but that is what I have for now.

This version allows the user to see the available sheet names and then select one.