Worksheet Names (Revised)
 

I went ahead and included an updated code from a previous post with a comment
line above the problem area. I can't get the code to work. I need it to
populate a combobox list with the sheet names of a specified workbook.

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim fs, f, f1, s, sf, sh As Worksheet, wbkChart As Workbook
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("c:\SymmcoPrograms\" & TextBox1.Value & "\" &
ComboBox1.Value)
Set sf = f.Files

If fs.FileExists("c:\SymmcoPrograms\" & TextBox1.Value & "\" &
ComboBox1.Value & "\Charts.xls") = True Then
Set fa = CreateObject("Scripting.filesystemobject")

€˜This section is giving me troubles Type Mismatch
Set wbkChart = fa.getfile("c:\SymmcoPrograms\" & TextBox1.Value &
"\" _
& ComboBox1.Value & "\Charts.xls")

For Each sh In wbkChart
ComboBox12.AddItem sh.Name
Next
End If

For Each f1 In sf
ComboBox2.AddItem f1.Name
ComboBox3.AddItem f1.Name
ComboBox4.AddItem f1.Name
ComboBox5.AddItem f1.Name
ComboBox6.AddItem f1.Name
ComboBox7.AddItem f1.Name
Next

End Sub

Worksheet Names (Revised)
 

You don't say what problem you are getting, but shouldn't you open the
workbook?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aftamath" wrote in message
...
I went ahead and included an updated code from a previous post with a
comment
line above the problem area. I can't get the code to work. I need it to
populate a combobox list with the sheet names of a specified workbook.

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim fs, f, f1, s, sf, sh As Worksheet, wbkChart As Workbook
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("c:\SymmcoPrograms\" & TextBox1.Value & "\" &
ComboBox1.Value)
Set sf = f.Files

If fs.FileExists("c:\SymmcoPrograms\" & TextBox1.Value & "\" &
ComboBox1.Value & "\Charts.xls") = True Then
Set fa = CreateObject("Scripting.filesystemobject")

'This section is giving me troubles Type Mismatch
Set wbkChart = fa.getfile("c:\SymmcoPrograms\" & TextBox1.Value &
"\" _
& ComboBox1.Value & "\Charts.xls")

For Each sh In wbkChart
ComboBox12.AddItem sh.Name
Next
End If

For Each f1 In sf
ComboBox2.AddItem f1.Name
ComboBox3.AddItem f1.Name
ComboBox4.AddItem f1.Name
ComboBox5.AddItem f1.Name
ComboBox6.AddItem f1.Name
ComboBox7.AddItem f1.Name
Next

End Sub

Worksheet Names (Revised)
 

The problem is a coding error "Type Mismatch". I currently have similar code
working in the same Userform which populates combobox lists by looking up
what files are in certain folders. And the same, what folders are in the
folders above that in another combobox. I'm having trouble with doing the
same for names of worksheets in a specified workbook file. But nothing has
to be opened.

"Bob Phillips" wrote:

You don't say what problem you are getting, but shouldn't you open the
workbook?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aftamath" wrote in message
...
I went ahead and included an updated code from a previous post with a
comment
line above the problem area. I can't get the code to work. I need it to
populate a combobox list with the sheet names of a specified workbook.

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim fs, f, f1, s, sf, sh As Worksheet, wbkChart As Workbook
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("c:\SymmcoPrograms\" & TextBox1.Value & "\" &
ComboBox1.Value)
Set sf = f.Files

If fs.FileExists("c:\SymmcoPrograms\" & TextBox1.Value & "\" &
ComboBox1.Value & "\Charts.xls") = True Then
Set fa = CreateObject("Scripting.filesystemobject")

'This section is giving me troubles Type Mismatch
Set wbkChart = fa.getfile("c:\SymmcoPrograms\" & TextBox1.Value &
"\" _
& ComboBox1.Value & "\Charts.xls")

For Each sh In wbkChart
ComboBox12.AddItem sh.Name
Next
End If

For Each f1 In sf
ComboBox2.AddItem f1.Name
ComboBox3.AddItem f1.Name
ComboBox4.AddItem f1.Name
ComboBox5.AddItem f1.Name
ComboBox6.AddItem f1.Name
ComboBox7.AddItem f1.Name
Next

End Sub