Johnny C
Well-known Member
- Joined
- Nov 7, 2006
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
I've got a userform which populates 2 list boxes. The first is open visible workbooks. The second lists visible sheets in that workbook, and is populated when the user clicks on a workbook in the first listbox by a click event.
When the form is loaded is populates the first listbox. The VBA then sets the first listbox value to the active workbook name so that the click event can populate the second list box with sheets in the active workbook.
However, for some reason the value in the first listbox isn't being set. Here's the code for the load
The line in bold initiates the lstWB click event,
This executes, and debug.prints the line
Control returns to the first bit of code, debug.prints the line
What am I doing wrong? probably something really dumb but I can't see it
Before adding the Workbooks option, I just had the list of sheets in the active workbook in the second listbox and it worked fine, I could set the value to the active sheet name.
When the form is loaded is populates the first listbox. The VBA then sets the first listbox value to the active workbook name so that the click event can populate the second list box with sheets in the active workbook.
However, for some reason the value in the first listbox isn't being set. Here's the code for the load
Code:
Load frmJump
' Populate combobox with visible workbooks
intIndex = 1
With frmJump.lstWB
.Clear
For intCount = 1 To Workbooks.Count
WorkbookName = Workbooks(intCount).Name
If Windows(WorkbookName).Visible = True Then .AddItem Workbooks(intCount).Name
Next intCount
End With
x = ActiveWorkbook.Name
[B] frmJump.lstWB.Value = ActiveWorkbook.Name
[/B]Debug.Print "Load 1 " & frmJump.lstWB.Value
The line in bold initiates the lstWB click event,
Code:
Private Sub lstWB_Click()
Debug.Print "WB Click start " & frmJump.lstWB.Value & "|" & frmJump.lstJump.Value
If frmJump.lstWB.Value = "" Then Exit Sub
This executes, and debug.prints the line
so frmJump.lstWB.Value hasn't been set to ActiveWorkbook.Name, and it exits the subWB Click start |
Control returns to the first bit of code, debug.prints the line
so the lstWB value still hasn't been set.Load 1
What am I doing wrong? probably something really dumb but I can't see it
Before adding the Workbooks option, I just had the list of sheets in the active workbook in the second listbox and it worked fine, I could set the value to the active sheet name.