Announcement

Collapse
No announcement yet.

Combobox MatchEntry Property

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

    Code:
    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

  • #2
    Re: Combobox Data Entry And Related Textboxes

    Are the numbers always 5 digits?
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: Combobox Data Entry And Related Textboxes

      try change to AfterUpdate evnet.

      Comment


      • #4
        Re: Combobox Data Entry And Related Textboxes

        Yes,

        Sandy

        Comment


        • #5
          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.

          Comment


          • #6
            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

            Comment


            • #7
              Re: Combobox MatchEntry Property

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

              Code:
              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
              Reafidy

              Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

              Comment


              • #8
                Re: Combobox MatchEntry Property

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

                Comment


                • #9
                  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

                  Comment


                  • #10
                    Re: Combobox Data Entry And Related Textboxes

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

                    Comment


                    • #11
                      Re: Combobox MatchEntry Property

                      If the first of the five didgits is never 0, this might work.

                      Code:
                      If (cmbJobNo.ListIndex > -1) And (Val(cmbJobNo.Value) > 9999) Then

                      Comment


                      • #12
                        Re: Combobox MatchEntry Property

                        Hi jindon,

                        Tried "AfterUpdate" but it still had the same problem.

                        Hi mikerickson,

                        Unfortunately your code did not solve the problem.

                        If I set MatchEntry to fmMatchEntryComplete the related textboxes change as soon as the combobox number is entered.
                        If I set MatchEntry to fmMatchEntryNone the related textboxes do not change even when the complete combobox number is entered.

                        I am using the following code to link the related textboxes to a cell, I don't know if this is causing the problem?

                        Code:
                        Private Sub txtClient_Change()
                        
                        txtClient = Format(Range("Jobs").Cells(cmbJobNo.ListIndex + 1, 2), "0")
                        ActiveSheet.Range("B1").Value = Me.txtClient.Value
                        
                        End Sub
                        So the problem is still the jump to the next data entry textbox before the combobox value is completed. The data entry person types in the 5 digit number very quickly and if a jump occurs before the complete number is typed (say 1530 of 15304) the last number get typed into the next data entry textbox (4), which of course is the wrong entry.


                        Sandy

                        Comment


                        • #13
                          Re: Combobox MatchEntry Property

                          Originally posted by gsandy
                          If I set MatchEntry to fmMatchEntryNone the related textboxes do not change even when the complete combobox number is entered.
                          This statement should not be true. The textbox should still fire the change event when the matchentry is set to none. (as dave said)

                          I set match entry to none and add a stop to the change event to check if it is firing you may being getting an unwanted cycle of events.
                          Reafidy

                          Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                          Comment


                          • #14
                            Re: Combobox MatchEntry Property

                            Hi Reafidy,

                            I know you and Dave must be right, but in my case it's not working??
                            I must have some conflicting code which I cant figure out.

                            If it's not too much of a pain, I could send you the Excel file.

                            Thanks for you help so far.

                            Sandy

                            Comment


                            • #15


                              Re: Combobox MatchEntry Property

                              We like to keep as much correspondance on the forum as possible.
                              If you can zip part of the file containing the problem to OZ that would be better.
                              If this cant be done - you can email it to me and I will post the results.
                              Reafidy

                              Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                              Comment

                              Working...
                              X