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 19

Thread: Combobox MatchEntry Property

  1. #1
    Join Date
    24th June 2003
    Posts
    166

    Combobox MatchEntry Property

    I have a ComboBox (cmbJobNo) in excel which lists a series of numbers. By using the code below other TextBoxes are automatically filled with related text once the ComboBox number is selected.
    On typing a number in the ComboBox the number is predicted, which is great except when a number is not sequential. Say the number is 15304 (the next number in list is 15315), when 1530 is entered, 15304 is predicted and VBA jumps to the next data entry Textbox, the data entry person then enters 4 (they type a lot faster than me, without looking), which is incorrect for that box.
    VBA does not allow the complete number to be entered. If I turn off MatchEntry the other related textboxes txtClient and txtProject) do not automatically update when number is entered.
    What can I do to allow complete number to be entered and related Textboxes updated after number entry?

    Thanks Sandy

    VB:
     
    Private Sub cmbJobNo_Change() 
         
        If cmbJobNo.ListIndex > -1 Then 
            txtClient = Format(Range("Jobs").Cells(cmbJobNo.ListIndex + 1, 2), "0") 
            txtProject = Format(Range("Jobs").Cells(cmbJobNo.ListIndex + 1, 3), "0") 
             
        End If 
        ActiveSheet.Range("A1").Value = Me.cmbJobNo.Value 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,897

    Re: Combobox Data Entry And Related Textboxes

    Are the numbers always 5 digits?

  3. #3
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,222

    Re: Combobox Data Entry And Related Textboxes

    try change to AfterUpdate evnet.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th June 2003
    Posts
    166

    Re: Combobox Data Entry And Related Textboxes

    Yes,

    Sandy

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Combobox Data Entry And Related Textboxes

    Sound more like data entry error. If they simply type without looking, set the MatchEntry Property to: FmMatchEntryNone. It has NO effect on the Change Event firing.

  6. #6
    Join Date
    24th June 2003
    Posts
    166

    Re: Combobox MatchEntry Property

    Hi,

    There is always 5 digits.

    I tried setting MatchEntry to none but the related textboxes did not update all.

    What do I do with "AfterUpdate event" or where do I set it?

    Off for a few hours (Kiwi hours), back tomorrow.

    Thanks,

    gsandy

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,897

    Re: Combobox MatchEntry Property

    I dont see how the prediction cause the change event to fire but anyway try:

    VB:
    Private Sub cmbJobNo_Change() 
         
        If cmbJobNo.ListIndex > -1 And Len(cmbjobno.value) = 5 Then 
            txtClient =  Format( Range("Jobs").Cells(cmbJobNo.ListIndex + 1, 2), "0") 
            txtProject = Format(Range("Jobs").Cells(cmbJobNo.ListIndex + 1, 3), "0") 
             
        End If 
        ActiveSheet.Range("A1").Value = Me.cmbJobNo.Value 
         
    End Sub 
    
    

  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Combobox MatchEntry Property

    You are mistaken, gsandy. The MatchEntry Property will not stop the Change event firing.

  9. #9
    Join Date
    24th June 2003
    Posts
    166

    Re: Combobox MatchEntry Property

    Hi Reafidy,

    Tried you code but I still could not get to the end of 15304 before it jumped to the next texbox, got a far as 1530.

    Hi Dave,

    I must be doing something wrong, will try again tomorrow.

    Thank,

    Sandy

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,222

    Re: Combobox Data Entry And Related Textboxes

    Quote Originally Posted by jindon
    try change to AfterUpdate evnet.
    Have you tried???

    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. Shapes Text Property VS Caption Property
    By econstantin in forum EXCEL HELP
    Replies: 1
    Last Post: January 17th, 2008, 10:46
  2. Set Rowsource Property On Combobox
    By ravenswood in forum EXCEL HELP
    Replies: 1
    Last Post: November 10th, 2006, 09:30
  3. Replies: 1
    Last Post: September 15th, 2006, 11:53
  4. Replies: 2
    Last Post: May 18th, 2005, 13:23

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