Userform listbox not registering selection after upgrade to MS 2016

abking1992

New Member
Joined
Nov 13, 2015
Messages
7
Code:
Private Sub fruitList_Click()


End Sub



Private Sub UserForm_Initialize()
With fruitList
    .AddItem "BANANA"
    .AddItem "APPLE"
    .AddItem "ORANGE"
    .AddItem "PINEAPPLE"
    
End With
End Sub
wb1.Activate


'Dim xlApp As Excel.Application
'ReadOnly fruitdata16
'Set xlApp = CreateObject("Excel.Application")
'xlApp.Visible = True
'xlApp.Workbooks.Open fileName:=fruitdata16


'Identify fruit Workbook name'
'MsgBox ActiveWorkbook.name, vbInformation, "Workbook Name"
bprWb = ActiveWindow.Caption
Sheets("NA Fruit Data").Select
fruitChosen = fruitList.Value
lastrowFinal = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
'MsgBox lastrowFinalWB3
wb1.Activate
If fruitChosen = "APPLE" Then
    FilterAPPLE lastrowFinal, wb1
ElseIf fruitChosen = "BANANA" Then
    FilterBAN lastrowFinal, wb1
ElseIf fruitChosen = "ORANGE" Then
    FilterORANGE lastrowFinal, wb1
ElseIf fruitChosen = "PINEAPPLE" Then
    FilterPA lastrowFinal, wb1
Else
[B]    MsgBox "Please choose your fruit"[/B]
    End
End If


Above is the code used to choose the user's fruit of choice (changed to fruit for confidentiality). Basically, this userform will list all fruit and the user selects one. This works in MS 2010 package. Our company is rolling out updates to MS 2016 and now the macro will consistently produce a message box saying "Please choose your fruit" i.e. not registering the userform selection.

I was able to see on a computer it wasn't working, it references a MS Object Library 16.0 and I'm currently running 14.0 (macro still works for me).

I have tried clearing *.exd files from his C drive, but it still did not help.

Any ideas? Thanks in advance.
 
Last edited by a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Binding won't affect that code as far as I can see. I also can't see how you'd get anything other than that message when no value has been selected in the list, since it is in the Initialize event.
 
Upvote 0
Rory,

I'm not sure what you mean -- I'm obviously by no means an expert, I got the gist of the code from online resources and how-to's. The code certainly works to select the listbox item, and as a matter of fact, I upgraded my laptop over the weekend and it still works. Just not on some user's computers. I'm really stumped as to why. References are all the same.
 
Upvote 0
Ah, on closer inspection it looks like you've mangled the code while posting it, since the bulk of it is outside any subroutine so it wouldn't run at all as posted. I would suggest you replace this line:
Code:
fruitChosen = fruitList.Value
with this:
Code:
fruitChosen = fruitList.List(fruitList.Listindex)
and see if that fixes it on all machines.
 
Upvote 0
Rory,

I was able to make this change on a machine that was having the original error. Now, I get this error:
"Run-Time error '-2147417848 (80010108)
Automation error
The object invoked has disconnected from it's clients"

I've tried to understand this issue and it seems like something is corrupt with the Adins?

Thanks for any insight!
 
Upvote 0
Which line causes the error?
 
Upvote 0
Can you put the actual workbook somewhere for review - e.g. Dropbox / OneDrive? Also, it would be helpful to know any OS/Office differences with the machines that don't work.
 
Upvote 0
Yes, that would most likely work much better. Let me clean out the proprietary information (change it to fruit) and I'll upload it. Thanks again for taking a look
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top