Results 1 to 10 of 10

Userform listbox not registering selection after upgrade to MS 2016

This is a discussion on Userform listbox not registering selection after upgrade to MS 2016 within the Excel Questions forums, part of the Question Forums category; Code: Private Sub fruitList_Click() End Sub Private Sub UserForm_Initialize() With fruitList .AddItem "BANANA" .AddItem "APPLE" .AddItem "ORANGE" .AddItem "PINEAPPLE" End ...

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    7

    Question Userform listbox not registering selection after upgrade to MS 2016

    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
        MsgBox "Please choose your fruit"
        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 RoryA; Dec 12th, 2016 at 08:21 AM. Reason: Code tags not quote tags

  2. #2
    Board Regular
    Join Date
    May 2015
    Posts
    709

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    Try changing to late binding and see if that helps, more reading here: VBA references and early binding vs late binding | Excel Matters
    Tab indent for sanity.

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    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.

  4. #4
    New Member
    Join Date
    Nov 2015
    Posts
    7

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    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.

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    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.

  6. #6
    New Member
    Join Date
    Nov 2015
    Posts
    7

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    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!

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    Which line causes the error?

  8. #8
    New Member
    Join Date
    Nov 2015
    Posts
    7

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    So I had a couple users click "Debug" on that error and it completely closes excel.

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    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.

  10. #10
    New Member
    Join Date
    Nov 2015
    Posts
    7

    Default Re: Userform listbox not registering selection after upgrade to MS 2016

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com