Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Question, Excel 2000 & 2003
Hello,
I created a form in workbook1 that will access all open workbooks. This is my problem I have 2 listboxes; listbox1 is in the UserForm_Initialize() event which adds all open workbooks to it. Now I need, when a workbook in ListBox1 is clicked it adds all worksheets in listbox2. Below is the following code I've tried and does not work: '==== This adds the workbook names to listbox1 Private Sub UserForm_Initialize() Dim wb As Workbook With ListBox1 For Each wb In Workbooks .AddItem (wb.Name) Next wb .ListIndex = 1 End With End Sub '<<==== '==== This does not add worksheets to listbox2 when workbook is clicked in listbox1 'Tried Code1 also with the Change_event: Private Sub ListBox1_Click() Dim wks As Worksheet With ListBox2 For Each wks In ActiveWorkbook.Worksheets .AddItem (wks.Name) Next End With End Sub 'Tried Code2 also with the Change_Event: Private Sub ListBox1_Click() Dim wks As Worksheet ListBox2.AddItem(wks.Name) = ListBox1.Value End Sub Thank you for your help, jfcby |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Question, Excel 2000 & 2003
The listIndex property is 0 based, so I assumed you wanted the first (0) WB
selected not the second (1). Also you need to .Clear listbox2 before you populate it each time Try this: Private Sub UserForm_Initialize() Dim wb As Workbook With ListBox1 For Each wb In Workbooks .AddItem wb.Name Next wb .ListIndex = 0 End With End Sub Private Sub ListBox1_Click() Dim wks As Worksheet Dim WKS_Name As String WKS_Name = ListBox1.List(ListBox1.ListIndex) With ListBox2 .Clear For Each wks In Workbooks(WKS_Name).Worksheets .AddItem wks.Name Next End With End Sub NickHK "jfcby" wrote in message oups.com... Hello, I created a form in workbook1 that will access all open workbooks. This is my problem I have 2 listboxes; listbox1 is in the UserForm_Initialize() event which adds all open workbooks to it. Now I need, when a workbook in ListBox1 is clicked it adds all worksheets in listbox2. Below is the following code I've tried and does not work: '==== This adds the workbook names to listbox1 Private Sub UserForm_Initialize() Dim wb As Workbook With ListBox1 For Each wb In Workbooks .AddItem (wb.Name) Next wb .ListIndex = 1 End With End Sub '<<==== '==== This does not add worksheets to listbox2 when workbook is clicked in listbox1 'Tried Code1 also with the Change_event: Private Sub ListBox1_Click() Dim wks As Worksheet With ListBox2 For Each wks In ActiveWorkbook.Worksheets .AddItem (wks.Name) Next End With End Sub 'Tried Code2 also with the Change_Event: Private Sub ListBox1_Click() Dim wks As Worksheet ListBox2.AddItem(wks.Name) = ListBox1.Value End Sub Thank you for your help, jfcby |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Question, Excel 2000 & 2003
On Feb 9, 12:48 am, "NickHK" wrote:
The listIndex property is 0 based, so I assumed you wanted the first (0) WB selected not the second (1). Also you need to .Clear listbox2 before you populate it each time Try this: Private Sub UserForm_Initialize() Dim wb As Workbook With ListBox1 For Each wb In Workbooks .AddItem wb.Name Next wb .ListIndex = 0 End With End Sub Private Sub ListBox1_Click() Dim wks As Worksheet Dim WKS_Name As String WKS_Name = ListBox1.List(ListBox1.ListIndex) With ListBox2 .Clear For Each wks In Workbooks(WKS_Name).Worksheets .AddItem wks.Name Next End With End Sub NickHK "jfcby" wrote in message oups.com... Hello, I created a form in workbook1 that will access all open workbooks. This is my problem I have 2 listboxes; listbox1 is in the UserForm_Initialize() event which adds all open workbooks to it. Now I need, when a workbook in ListBox1 is clicked it adds all worksheets in listbox2. Below is the following code I've tried and does not work: '==== This adds the workbook names to listbox1 Private Sub UserForm_Initialize() Dim wb As Workbook With ListBox1 For Each wb In Workbooks .AddItem (wb.Name) Next wb .ListIndex = 1 End With End Sub '<<==== '==== This does not add worksheets to listbox2 when workbook is clicked in listbox1 'Tried Code1 also with the Change_event: Private Sub ListBox1_Click() Dim wks As Worksheet With ListBox2 For Each wks In ActiveWorkbook.Worksheets .AddItem (wks.Name) Next End With End Sub 'Tried Code2 also with the Change_Event: Private Sub ListBox1_Click() Dim wks As Worksheet ListBox2.AddItem(wks.Name) = ListBox1.Value End Sub Thank you for your help, jfcby- Hide quoted text - - Show quoted text - Hello NickHK, The code works great! Thank you for your help, jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform Enabled property behavior changes between excel 97 and 2000/3 | Excel Programming | |||
2003 to 2000 security question | Excel Discussion (Misc queries) | |||
Linking userform to userform in Excel 2003 | Excel Programming | |||
Excel 2000 Load Userform when clicking cell | Excel Programming | |||
load userform from other VBProject (Excel 2000) | Excel Programming |