Error 2015 message with InputBox

[joe_sans]

Hello I am having trouble with the inputbox function in my macro. In the prompt of the inputbox I have a string that was created by adding all of the sheets in a workbook to it and separates with vbNewLine. But when the document is larger and has many sheets I receive the error: Error 2015. Any thoughts below is the code that I am using.

Public Sub UserForm_Initialize1(SheetTitle, selectedFile)
Dim ws As Worksheet
Dim wkb As Workbook
Dim Sht() As Variant
Dim shtlist As String
Dim prompt As String
Dim col As New Collection

Set wkb = Workbooks.Open(selectedFile)

For Each ws In wkb.Worksheets
'ReDim Preserve Sht(1 To UBound(Sht) + 1) As Variant
col.Add ws.Name
Next ws

Sht = toArray(col)

shtlist = Join(Sht, ", ")
prompt = "Select Sheet to run macro on" & vbNewLine & vbNewLine & shtlist
On Error Resume Next
SheetTitle = Application.InputBox(prompt:=prompt)
If SheetTitle = "Error 2015" Then

SheetTitle = Application.InputBox(prompt:="Select sheet to run macro on" & shtlist)
End If



End Sub

[Keebellah]

Probably the limit of elements in a InputBox
I would suggest a small userform with a drowpdown list

[joe_sans]

how do I do a dropdown list?

[Keebellah]

Your macro is already in a user form.
Just add a dropdownlist (ComboBox) and populate it

[joe_sans]

I am unsure how to add a combobox with code

[Keebellah]

I have no idea what you userform looks like but something like this (after you add a ComboBox to the userform

Public Sub UserForm_Initialize1(SheetTitle, selectedFile)
Dim ws As Worksheet
Dim wkb As Workbook
Dim Sht() As Variant
Dim shtlist As String
Dim prompt As String
Dim col As New Collection
Set wkb = Workbooks.Open(selectedFile)
With Me.ComboBox1
    .Clear
    For Each ws In wkb.Worksheets
        .AddItem ws.Name
    Next ws
End With
End Sub

Private Sub ComboBox1_Change()
If Len(Trim(Me.ComboBox1.Value)) = 0 Then Exit Sub
' do the macro you have to do
Unload Me
End Sub

If not sore attach a file with some dummy data but with the macro and userform if it is a userform

[joe_sans]

My userform is basic and just has the ComboBox1 so that is what I am working with. I used the code you entered above and it says invalid use of me?

[bakerman2]

To fill combobox.

For Each sh In Sheets
c00 = c00 & "|" & sh.Name
Next
ComboBox1.List = Split(Mid(c00, 2), "|")

What is the 1 doing after Userform_Initialize1

[Keebellah]

Me addresses the Userform but yes, I was also wondering about the 1

[joe_sans]

I got the Combobox to populate, how can I use a button to save a value to a variable. Note the sub is in my general module not attached to the UserForm i'm not sure if this makes a difference

[Norie]

What sub is in a general module?

If it's the sub with the code to populate the combobox on the userform you should really move the code to the userform's Initialize event.

[joe_sans]

Public Sub UserForm_Initialize1(SheetTitle, selectedFile)
    Dim ws As Variant
    Dim wkb As Workbook

        Set wkb = Workbooks.Open(selectedFile)
        Set ComboBox1 = UserForm1.ComboBox1
        Set Submit = UserForm1.CommandButton1
        Set Cancel = UserForm1.CommandButton2
            
            For Each ws In wkb.Sheets
                c00 = c00 & "|" & ws.Name
            Next
            ComboBox1.List = Split(Mid(c00, 2), "|")
        
                
        UserForm1.Show
        If Submit = True Then
            SheetTitle = ComboBox1.Value
        End If
End Sub

this is what i have it is a public sub in the general area

[Norie]

What exactly are you trying to do with the code?

Where do SheetTitle and selectedFile come from?

[Keebellah]

I have no idea how you've built your macros.
Here's and example to select a worksheet from the active workbook
The code is basically the same for all, but this is to give you an idea.

[joe_sans]

My goal with this is to have the end user be able to select a workbook in a different sub (selectedFile), then in that workbook select a sheet and have the code put that sheet's name into a string variable for later use in the code (sheetTitle)

[Norie]

How are they selecting the workbook?

[joe_sans]

With a filedialog filepicker the code line is

 Set fileSelect = Application.FileDialog(msoFileDialogFilePicker) 'create the file picker object
    
    If fileSelect.Show = -1 Then 'pop the file picker, -1 is returned if the user clicks cancel
        'if they select more than 1 file notify them that we can only look at 1 file and quit
        If fileSelect.SelectedItems.count > 1 Then
            MsgBox "Please select a single file to analyze."
        Else
        'if they select a single file we proceed
            SelectedFile = fileSelect.SelectedItems.Item(1) 'get the file path

this being in a separate sub of course

[Keebellah]

All you need to do now is add the code to select the workbook and integrated this code with it which uses the 'ActiveWorkbook'

[joe_sans]

I am unsure as to what that means in terms of code?

[Norie]

I would suggest putting the code to select the workbook in the userform module.

How you call it is up to you, for example you could use the userform's Initialize event or you could have a button on the userform.

For example if you added a button to the form in the workbook Keebellah uploaded you could use this code to allow the user to select a workbook and populate the combobox with its sheet names.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim wkb As Workbook
Dim fileSelect As FileDialog
Dim selectedFile As String

    selectedFile = Application.GetOpenFilename(, , "Pick a workbook", False)

    If selectedFile = "False" Then
        MsgBox "Cancelled"
        Unload Me
        Exit Sub
    End If

    Set wkb = Workbooks.Open(selectedFile)

    With Me.ComboBox1
        For Each ws In wkb.Worksheets
            .AddItem ws.Name
        Next ws
    End With
    
End Sub

Private Sub ComboBox1_Change()
    If Len(Trim(Me.ComboBox1.Value)) = 0 Then Exit Sub
    MsgBox "Invoke the code to execute the macro on " & Me.ComboBox1.Value & " here!", vbInformation, "THIS IS IT!!!"
    ' do the macro you have to do
    Unload Me
End Sub

[joe_sans]

That would likely work, unfortunately it would be quite a task to rewrite my whole macro just so that I can have the macro on the userform. If there is an alternative way to simply pass through the variable have the userform change it and spit it back out into the general module that would be fantastic if not it looks like I have a lot of work to do.

[Norie]

If you mean pass the selected sheet name and workbook back to the main module that's straightforward.

[Keebellah]

Could you attach the controlling file that contains YOUR macro?

[Norie]

Here's an updated version of Keebellah's file.

[Keebellah]

Nice one Norie
Works

[joe_sans]

Sub initializeUF1(SheetTitle, SelectedFile)
    Dim ws As Variant
    Dim wkb As Workbook
  
        Set wkb = Workbooks.Open(SelectedFile)
                    
    For Each ws In wkb.Sheets
                c00 = c00 & "|" & ws.Name
            Next
            
If SelectedFile <> "" Then
    With Userform1
        .SelectedFile = SelectedFile
        .ComboBox1.List = Split(Mid(c00, 2), "|")
        .Show
     End With
End If

End Sub

With this above code I was able to accomplish what I wanted with the userform, all I need now is to know how to assign the combobox selection to a variable in the main module any thoughts.
This is what I have for in the Userform:

Private Sub CommandButton1_Click()
    Dim SheetTitle As String
    
    SheetTitle = Userform1.ComboBox1.Value
    Userform1.Hide
    
End Sub

Also side-note I really appreciate all the help!

[Keebellah]

That was all in my code, you just need to use it.

I mentioned it as comment and you 'see' the selected sheet in the msgbox, so I do not understand what else you need.
We've cut and diced it for you, all you need to do is swallow

A little personal effort will help you acquire VBA experience, it's nothing special, just logical thinking and a lot of imagination and ..... time .
Happy coding. I'm off on vacation, back in 3 weeks