+ Reply to Thread
Results 1 to 5 of 5

Do not add item from a list to a list box if it is formatted in bold

  1. #1
    Registered User
    Join Date
    04-23-2019
    Location
    Atlanta, Ga.
    MS-Off Ver
    Office 365
    Posts
    16

    Do not add item from a list to a list box if it is formatted in bold

    Thank you in advance for any help

    I am trying to exclude items from the range "Item" that are in bold from being added to the list box upon initializing, the list box is a multi-select type.

    I know the code below is adding items based on the count of items the range, but how can i check the formatting at that location for being bold and exclude it from being added to the listbox?

    Please Login or Register  to view this content.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Do not add item from a list to a list box if it is formatted in bold

    You have to explicitly loop through the cells in the range.

    It is not necessary to activate the sheet, unless you need your user to see it.

    The second part of the original code is unnecessary. Even if it were necessary, it would not need loop through all controls and check for one particular control; it would just have code for that one control.

    I cannot test this without your file.

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 04-14-2020 at 02:55 PM. Reason: Added a missing Not to the If logic
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Do not add item from a list to a list box if it is formatted in bold

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Do not add item from a list to a list box if it is formatted in bold

    Just a quick comment about the two event procedures that have been posted (note that they are identical except for variable names**). If you have cells with part (but not all) of the text in the cell bolded, that text will be added to ListBox... the code only filters cells that are entirely bold. If you need to exclude partially bolded text as well as fully bolded text, you can modify the code this way...
    Please Login or Register  to view this content.
    **Edit Note: As the OP points out in Message #5, there is a difference between the two codes that I missed initially... one uses ListBox1.Add and the other uses ListBox1.AddItem with the latter being the correct property name.
    Last edited by Rick Rothstein; 04-15-2020 at 08:17 AM.

  5. #5
    Registered User
    Join Date
    04-23-2019
    Location
    Atlanta, Ga.
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Do not add item from a list to a list box if it is formatted in bold

    Changed ListBox1.Add Cell.Value to ListBox1.Additem Cell.Value and it worked perfectly

    Thank you

+ 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. Replies: 6
    Last Post: 06-23-2019, 11:07 PM
  2. [SOLVED] Active X drop down list - always return to the item in the list that was selected.
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-14-2017, 09:41 AM
  3. [SOLVED] SUM if Bold UDF to Ignore Bold Formatted Text
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2015, 01:15 PM
  4. Replies: 5
    Last Post: 04-11-2014, 03:13 PM
  5. [SOLVED] autofill vertical list horizontally with 2 blank cells in-between each list item
    By Phily915 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2014, 08:15 PM
  6. Problem getting conditional formatted dropdown list items to keep their formatting in list
    By scottatbuckman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2014, 02:39 PM
  7. How to create drop down list in Excel where each list item is based on two columns?
    By matkiros in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2012, 08:50 AM

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