Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Restrict Combobox Selection To List Only But Allow Deletion

  1. #1
    Join Date
    29th September 2005
    Location
    SLO
    Posts
    509

    Restrict Combobox Selection To List Only But Allow Deletion

    I have the following code which works but after the user gets an initial error says that they have to enter what is a ComboBox choice they get a second error when they press backspace to delete their entry. Is there a way to just give them the first error but not an error when they press backspace to erase their mistake in the form?

    This checks the user input...
    VB:
    Private Sub Combobox5_Change() 
        If ComboBox5.ListIndex < 0 Then 
            MsgBox "Please Only Pick From The List.  Use Admin Page to Add More to the List", vbCritical, "Error" 
        End If 
    End Sub 
    
    
    This is what is loaded as ComboBox choices. I only want them to pick what is on this list. Nothing else.
    VB:
    On Error Resume Next 
    lngNRows = Range("Admin_Bikes").Rows.Count 
    If lngNRows > 0 Then 
        ComboBox5.RowSource = "Admin_Bikes" 
    Else 
        ComboBox5.RowSource = "" 
        ComboBox5.AddItem "Empty" 
    End If 
    
    
    Thanks much,

    Guy

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Combobox Validation

    VB:
    Private Sub ComboBox1_Change() 
        If ComboBox1 <> vbNullString And ComboBox1.ListIndex < 0 Then 
             'CODE
        End If 
    End Sub 
    
    
    Last edited by Dave Hawley; February 14th, 2008 at 16:54.

  3. #3
    Join Date
    29th September 2005
    Location
    SLO
    Posts
    509

    Re: Combobox Validation

    Quote Originally Posted by Dave Hawley
    VB:
    Private Sub ComboBox1_Change() 
        If ComboBox1 <> vbNullString And ComboBox1.ListIndex > 0 Then 
             'CODE
        End If 
    End Sub 
    
    
    Dave, thank you for your reply - as always you are a big help...

    I tried that code but it seems to give an error ONLY when the user picks from the ListIndex (choices in the combobox drop down). User can freeform anything and no error.

    Guy

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Restrict Combobox Selection To List Only But Allow Deletion

    It restricts users to choose from all but the 1st combobox item (listindex 0).

  5. #5
    Join Date
    29th September 2005
    Location
    SLO
    Posts
    509

    Re: Restrict Combobox Selection To List Only But Allow Deletion

    Quote Originally Posted by Dave Hawley
    It restricts users to choose from all but the 1st combobox item (listindex 0).
    OK, I think I understand. How would I adjust this so the only valid entry is the choices in the combobox (I assume that is thelistiondex). Sorry if I sound confused. I am slow. It is late :-)

    Guy

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Restrict Combobox Selection To List Only But Allow Deletion

    VB:
    Private Sub Combobox5_Change() 
        If ComboBox5.ListIndex < 0 Then 
            MsgBox "Please Only Pick From The List.  Use Admin Page to Add More to the List", vbCritical, "Error" 
        End If 
    End Sub 
    
    
    Try using your original code and set the MatchEntry property of combobox5 to MatchEntryComplete
    Bill
    Tip: To avoid chasing code always use Option Explicit.

  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,364

    Re: Restrict Combobox Selection To List Only But Allow Deletion

    Hi,

    This is what is loaded as ComboBox choices. I only want them to pick what is on this list. Nothing else.
    Set the Style property to fmStyleDropDownList

    HTH

  8. #8
    Join Date
    29th September 2005
    Location
    SLO
    Posts
    509

    Re: Restrict Combobox Selection To List Only But Allow Deletion

    Quote Originally Posted by Krishnakumar
    Hi,



    Set the Style property to fmStyleDropDownList

    HTH
    Perfect, thank you. A simple solution that works great. Many thanks... Guy

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Restrict Combobox Selection To List Only But Allow Deletion

    I thought you wanted to allow deletion of any choice?

  10. #10
    Join Date
    29th September 2005
    Location
    SLO
    Posts
    509

    Re: Restrict Combobox Selection To List Only But Allow Deletion

    Quote Originally Posted by Dave Hawley
    I thought you wanted to allow deletion of any choice?
    Dave,
    This solution seems to work. A user can type in a valid option and then change that option without any error. The user is forced to only user valid combobox options. I thought I tested it multiple times but will do again to make sure the user can delete their entry and change it.

    Guy

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Combobox Visibility After Drop List Selection
    By hippy in forum EXCEL HELP
    Replies: 1
    Last Post: June 21st, 2008, 11:30
  2. Restrict Listbox/ComboBox Heading Selection
    By jassythedog in forum EXCEL HELP
    Replies: 3
    Last Post: May 22nd, 2008, 09:56
  3. Replies: 6
    Last Post: July 22nd, 2007, 14:52
  4. Selection Deletion
    By adncmm1980 in forum EXCEL HELP
    Replies: 1
    Last Post: November 2nd, 2004, 19:27
  5. Replies: 4
    Last Post: December 4th, 2003, 23:32

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
  •  
porno