Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automating Worksheet Names | Excel Worksheet Functions | |||
Lookups using worksheet names | Excel Discussion (Misc queries) | |||
Dynamic Worksheet Names | Links and Linking in Excel | |||
Defining non worksheet specific names... | Excel Worksheet Functions | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) |