Set Listbox Value based on Cell Content

  • My userform initializes with several statements that look like this:
    [VBA]ListBox1.Value = Range("A" & Spinbutton1.Value).Value[/VBA]
    For some reason, even though the listbox item always appears to be highlighted correctly, the listbox's value isn't always actually set properly by that command. This is evident when I click a command button with code along the lines of:
    [VBA]Range("A" & Spinbutton1.Value).Value = ListBox1.Value[/VBA]
    Doing that often results in blank values on the worksheet.


    There is an exception to this problem: once I have actually clicked into the listbox and manually made selections, the listbox value seems to set properly. But if I just click my command button without actually manually making selections in my listboxes, the worksheet cell(s) will often attain a value of "", corresponding with the blank actual value of the listbox.


    I have attached a simplified version of the workbook producing the error I've described. Hopefully you will be easily able to reproduce the error.


    There is a similar post at https://www.ozgrid.com/forum/forum/h...-listbox-value, where the issue apparently resolved spontaneously, so no general solution was reached. I apologize if I've broken etiquette by referencing that post in my new post rather than commenting on that old one; if so, please attribute my mistake to callowness rather than inconsiderateness.


    Thanks for any insight you can provide.

  • No need to apologise. Can you try this for writing the values to the sheet?


    This seems to work for reasons which are not apparent to me. Got from https://stackoverflow.com/ques…ter-setting-the-listindex

  • [USER="20913"]StephenR[/USER]


    Thanks a lot for sharing this solution ... to this rather strange problem ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!