Userform Click event - can't set the listbox value

[Johnny C]

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

    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,

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

WB Click start |

so frmJump.lstWB.Value hasn't been set to ActiveWorkbook.Name, and it exits the sub

Control returns to the first bit of code, debug.prints the line

Load 1

so the lstWB value still hasn't been set.

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.

 

[RoryA]

Does this work?

    Dim wbIndex As Long
    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
            If workbookname = ActiveWorkbook.Name Then wbIndex = .ListCount - 1
        Next intCount
    End With
    x = ActiveWorkbook.Name
    frmJump.lstWB.ListIndex = wbIndex

 

[Johnny C]

Cheers, ListIndex is what I was looking for.

However ... when I replicate for the second listbox, and set the ListIndex to 0 if it's not the current workbook or the item# of the active sheet if it is, it's activating the click event for the second listbox. The click event for the second one runs some code then unloads the form. But setting the listIndex for the second box activates the click event before the user has had a chance to click anything which is unloading the form.

What I am trying to get to is a form where they don't need to click buttons, to speed things up, just select items in 2 lists. The user might need to do this 100 times so removing the need to click a button to indicate they have chosen the items speeds it up a lot.

Is there anyway the second listbox click event could detect whether the user had selected an item or it had been automatically run by setting the ListIndex in VBA?

I can set a public boolean to True before the initial pass and if that is set to True not unload the form and set the boolean to False so it works from the second iteration, I'm just trying to write something 'elegant' that avoids the need for Public variables. I prefer to keep things clean if I can, thanks to resorting to things like Public variables being beaten out of me in my programmer training. The same reason I have never used a VBA Goto outside of On Error Goto 0 and once in an error trap that I couldn't avoid. In fact the last time I used a Goto of any sort as a program control was a COBOL Goto in 1985. Feck, 32 years of being Goto Free. Dave Williams (my first Pascal programming lecturer), I have a lot to thank you for!

 

[RoryA]

The variable doesn't need to be public given that it's only used within the userform. That is the only real method of skipping events in a userform.

Personally, I'd use a double-click event of the listbox to do the final step.

 

[Johnny C]

haha, figured out the Dblclick, works a charm, thanks!
I reckoned you should be able to use a double click but (being lazy) double clicking the listbox only brought up the click event so I figured it probably wasn't possible.

I'd gotten close to it working with me.ActiveControl.Name and error trapping but this is far simpler and preferable