View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
aftamath
 
Posts: n/a
Default 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