+ Reply to Thread
Results 1 to 6 of 6

How to detect Null value from a ListBox

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    How to detect Null value from a ListBox

    I have a listBox on a UserForm. Its RowSource is a list of Budget Years.
    It is important that the correct year is selected by the user. I want to get a message box to warn a user that he has not selected a year.
    I have tried versions of the following code without success.
    Please Login or Register  to view this content.
    This does not work, yet the unselected value of the Listbox is null.
    How should I refer to it to make my code work?
    John

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    561

    Re: How to detect Null value from a ListBox

    What happens if you try If Me.LBYear.Value = ""?

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: How to detect Null value from a ListBox

    John

    You can check if a user has selected an item from the list in a single select listbox by looking at its ListIndex property.

    If that is -1 then they haven't selected an item, if it's 0 they've selected the first item, if it's 1 they selected the second item etc.
    If posting code please use code tags, see here.

  4. #4
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: How to detect Null value from a ListBox

    Norie and Bellygas,
    Many thanks for your help. Finding Me.LBYear.ListIndex=-1 did the job perfectly. I am still in trouble though.
    The code I have written just runs past the MsgBox without giving the chance of selecting a year from the ListBox
    So I end with an error in Sub ChooseMonth which needs a proper value in LBYear.
    How do I get round this?
    Please Login or Register  to view this content.
    Hoping for more help
    John

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: How to detect Null value from a ListBox

    John

    What do you want to happen if a year hasn't been selected?

    If you want to return the user to the userform add an Exit Sub.
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: How to detect Null value from a ListBox

    Thank you very much for your help. Those two items are going to be very useful in the future as well.
    John

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel vba - listbox displays 12/30/99 when value is null
    By terriertrip in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-27-2017, 09:52 AM
  2. [SOLVED] Code failing to detect a null MsgBox value
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2014, 06:55 PM
  3. [SOLVED] Why are there Empty/Null/Blank Rows in my ListBox?
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-02-2014, 09:25 PM
  4. [SOLVED] Listbox.value is null
    By Hammer_757 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-31-2012, 11:41 AM
  5. [SOLVED] Macro: Listbox value null
    By jomili in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2009, 04:47 PM
  6. What event to detect click on UserForm ListBox?
    By Don Wiss in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2005, 06:05 PM
  7. [SOLVED] Detect NULL (??) range?
    By Ed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2005, 01:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1