Userform Click event - can't set the listbox value

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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
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
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
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Does this work?

Code:
    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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,480
Members
414,143
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top