combobox exact match

[charly1]

hi all I have a table with two columns of data, As below

Column1	Column2
1	ג' תשס
2	ג' תשסא
3	ג' תשסב

In a userform i have two comboboxes which on form initilisation each load with one of the two columns of data from table above. The click event in each of the two cmb activates a xlookup which populates the other cmb with the corresponding data from the table. I have posted the two bits of code below

Private Sub ComboNewArtistDODJ_Click()
ComboNewArtistDODG.Value = ""

Dim searchValue As String
Dim searchRange As Range
Dim tableData As Range
Dim resultRange As Range


searchValue = ComboNewArtistDODJ.Value
Set searchRange = Worksheets("Helper Data").ListObjects("Table4").ListColumns(2).DataBodyRange
Set tableData = Worksheets("Helper Data").ListObjects("Table4").ListColumns(1).DataBodyRange
Set resultRange = WorksheetFunction.XLookup(searchValue, searchRange, tableData)

ComboNewArtistDODG.Value = resultRange
End Sub

2

Private Sub ComboNewArtistDODG_Click()

ComboNewArtistDODJ.Value = ""

Dim searchValue As Long
Dim searchRange As Range
Dim tableData As Range
Dim resultRange As Range


searchValue = ComboNewArtistDODG.Value
Set searchRange = Worksheets("Helper Data").ListObjects("Table4").ListColumns(1).DataBodyRange
Set tableData = Worksheets("Helper Data").ListObjects("Table4").ListColumns(2).DataBodyRange
Set resultRange = WorksheetFunction.XLookup(searchValue, searchRange, tableData)

ComboNewArtistDODJ.Value = resultRange
End Sub

My problem is like this. Both cmb boxes are set to match required. The combo that contains letters is fine. The cmb with the numbers doesn't recognize any match, even when I simply use the drop arrow and select a number from the drop list.

I know this has something to do with the code click event because when i delete the code, it works fine. Why this is happening though, I haven't a clue - any idea?

 

[DanteAmor]

What happens is that in the controls: combobox, textbox, listbox, the data is stored as text, to use it as a numeric value you must convert that text to a number, one option is the following:

searchValue = Val(ComboNewArtistDODG.Value)

 

[charly1]

What happens is that in the controls: combobox, textbox, listbox, the data is stored as text, to use it as a numeric value you must convert that text to a number, one option is the following:

searchValue = Val(ComboNewArtistDODG.Value)

I've been trying this idea, and anything else i could think of and nothing seems to help.

thanks for your time

 

[DanteAmor]

and anything else i could think of and nothing seems to help

You can put here the code to use to load the two combos.

 

[DanteAmor]

I did test with your code and it works for column 1 where you have numbers.

Could you share your file for review.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[charly1]

NEW11 - Copy.xlsm

drive.google.com

above is the link to the workbook.

the relevant user form is called with the button on the first worksheet.

Thanks you so much for your help.

 

[DanteAmor]

You can put here the code to use to load the two combos.

That's important, you have to help us with all your code.
I took the load of your combobox as a base to do what you want.

Add the following variable as global:

Option Explicit

Dim updating As Boolean     '<<--- at the beginning of your code

Replace the 2 codes with the following:

Private Sub ComboNewArtistDODG_Click()
  If updating = True Then Exit Sub
  updating = True
  ComboNewArtistDODJ.Value = ComboNewArtistDODJ.List(ComboNewArtistDODG.ListIndex)
  updating = False
End Sub

Private Sub ComboNewArtistDODJ_Click()
  If updating = True Then Exit Sub
  updating = True
  ComboNewArtistDODG.Value = ComboNewArtistDODG.List(ComboNewArtistDODJ.ListIndex)
  updating = False
End Sub

I return your file with the changes.

NEW11 - Copy dam.xlsm

drive.google.com

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[charly1]

WOW. You aside for my issue you seem to have shortened my code immensely. THANKS

There is still two slight issues though.

1) Your coding works when selecting a number from the combo dropdown list. If i try to type in a number I get the same problem, even if it matches the combo box list].

2) When I make a selection in the other combo box [the one without the numbers], then using your coding it goes and updates the combo box with the numbers. If after that, I go and select the combo box that contains the numbers and then try and exit the combo box, it gives me the same issue.

BTW you wrote

That's important, you have to help us with all your code.
I took the load of your combobox as a base to do what you want.

Without trying to be obtuse, My coding for the user form was all in the sheet that I uploaded?

 

[DanteAmor]

If i try to type in a number I get the same problem

If after that, I go and select the combo box that contains the numbers and then try and exit the combo box, it gives me the same issue

It's not exactly the same problem.
The problem starts from how you loaded the data in the combo. That's why it was necessary that you put all the code as I requested from post #4.
Another problem occurs when you modify a combo the other combo must also be modified, so it goes into a loop. Hence the global variable.

I've done more testing and the correct event for what you want is the Change event instead of the Click event.

I changed the code and now it works with this:

Private Sub ComboNewArtistDOBG_Change()
  If updating = True Then Exit Sub
  updating = True
  If ComboNewArtistDOBG.ListIndex = -1 Then
    ComboNewArtistDOBJ.Value = ""
  Else
    ComboNewArtistDOBJ.Value = ComboNewArtistDOBJ.List(ComboNewArtistDOBG.ListIndex)
  End If
  updating = False
End Sub

Private Sub ComboNewArtistDOBJ_Change()
  If updating = True Then Exit Sub
  updating = True
  If ComboNewArtistDOBJ.ListIndex = -1 Then
    ComboNewArtistDOBG.Value = ""
  Else
    ComboNewArtistDOBG.Value = ComboNewArtistDOBG.List(ComboNewArtistDOBJ.ListIndex)
  End If
  updating = False
End Sub

Private Sub ComboNewArtistDODG_Change()
  If updating = True Then Exit Sub
  updating = True
  If ComboNewArtistDODG.ListIndex = -1 Then
    ComboNewArtistDODJ.Value = ""
  Else
    ComboNewArtistDODJ.Value = ComboNewArtistDODJ.List(ComboNewArtistDODG.ListIndex)
  End If
  updating = False
End Sub

Private Sub ComboNewArtistDODJ_Change()
  If updating = True Then Exit Sub
  updating = True
  If ComboNewArtistDODJ.ListIndex = -1 Then
    ComboNewArtistDODG.Value = ""
  Else
    ComboNewArtistDODG.Value = ComboNewArtistDODG.List(ComboNewArtistDODJ.ListIndex)
  End If
  updating = False
End Sub

However, I can't make the code so "repetitive", so I design a procedure that works for all 4 cases, simplifying the code like this:

Private Sub ComboNewArtistDOBG_Change()
  Call updateCombos(ComboNewArtistDOBG, ComboNewArtistDOBJ)
End Sub

Private Sub ComboNewArtistDOBJ_Change()
  Call updateCombos(ComboNewArtistDOBJ, ComboNewArtistDOBG)
End Sub

Private Sub ComboNewArtistDODG_Change()
  Call updateCombos(ComboNewArtistDODG, ComboNewArtistDODJ)
End Sub

Private Sub ComboNewArtistDODJ_Change()
  Call updateCombos(ComboNewArtistDODJ, ComboNewArtistDODG)
End Sub

Sub updateCombos(a As MSForms.ComboBox, b As MSForms.ComboBox)
  If updating = True Then Exit Sub
  updating = True
  If a.ListIndex = -1 Then b.Value = "" Else b.Value = b.List(a.ListIndex)
  updating = False
End Sub

Other details that you must correct:

1. This should be the load of the combos:

Private Sub UserForm_Initialize()
  Dim Range1 As Range, Range2 As Range, c As Range
  
  Set Range1 = Worksheets("Helper Data").ListObjects("Table4").ListColumns(1).DataBodyRange
  Set Range2 = Worksheets("Helper Data").ListObjects("Table4").ListColumns(2).DataBodyRange
  
  For Each c In Range1
    ComboNewArtistDOBG.AddItem c.Text
    ComboNewArtistDODG.AddItem c.Text
  Next
  For Each c In Range2
    ComboNewArtistDOBJ.AddItem c.Text
    ComboNewArtistDODJ.AddItem c.Text
  Next
  
  ToggleNewArtistNickName.Value = True
End Sub

Note:​

You should not use Range as a variable, it is a reserved word for VBA, if you use it as a variable it could cause conflicts, ideally you should use it with numbers or accompanied by a hyphen and another word, for example: range1 or range_a.​

2. If you are going to write data directly to the combo, then you should not set the MatchRequired property equal to True. Change the property on all 4 combos to False.

Here the complete code:

Option Explicit

Dim updating As Boolean     '<<--- at the beginning of your code

Private Sub ComboNewArtistDOBG_Change()
  Call updateCombos(ComboNewArtistDOBG, ComboNewArtistDOBJ)
End Sub

Private Sub ComboNewArtistDOBJ_Change()
  Call updateCombos(ComboNewArtistDOBJ, ComboNewArtistDOBG)
End Sub

Private Sub ComboNewArtistDODG_Change()
  Call updateCombos(ComboNewArtistDODG, ComboNewArtistDODJ)
End Sub

Private Sub ComboNewArtistDODJ_Change()
  Call updateCombos(ComboNewArtistDODJ, ComboNewArtistDODG)
End Sub

Sub updateCombos(a As MSForms.ComboBox, b As MSForms.ComboBox)
  If updating = True Then Exit Sub
  updating = True
  If a.ListIndex = -1 Then b.Value = "" Else b.Value = b.List(a.ListIndex)
  updating = False
End Sub

Private Sub ButtonNewArtistAddToTable_Click()
  Dim tableName As ListObject
  Set tableName = Worksheets("îçáøéí").ListObjects("Artist")
  Dim addedRow As ListRow
  Set addedRow = tableName.ListRows.Add()
  With addedRow
      .Range(1) = TextBoxNewArtistName.Value
      .Range(2) = TextBoxNewArtistNickName.Value
      .Range(4) = ComboNewArtistDOBG.Value
      .Range(6) = ComboNewArtistDODG.Value
       .Range(5) = ComboNewArtistDOBJ.Value
      .Range(7) = ComboNewArtistDODJ.Value
  End With
  
  If MsgBox("New Artist has been added to table" & vbNewLine & "Would you still like to add another artist", vbYesNo + vbExclamation, "New Artist") = vbYes Then
  Unload Me
  UserFormNewArtist.Show
  Else
  Unload Me
  End If
End Sub

Private Sub ButtonNewArtistAdd_BackToNewSefer_Click()
  Dim tableName As ListObject
  Set tableName = Worksheets("îçáøéí").ListObjects("Artist")
  Dim addedRow As ListRow
  Set addedRow = tableName.ListRows.Add()
  With addedRow
      .Range(1) = TextBoxNewArtistName.Value
      .Range(2) = TextBoxNewArtistNickName.Value
      .Range(4) = ComboNewArtistDOBG.Value
      .Range(6) = ComboNewArtistDODG.Value
       .Range(5) = ComboNewArtistDOBJ.Value
      .Range(7) = ComboNewArtistDODJ.Value
  End With
  
  Unload Me
  UserFormNewSefer.Show
End Sub

Private Sub ButtonNewArtistClearDOB_Click()
  ComboNewArtistDOBJ.Value = ""
  ComboNewArtistDOBG.Value = ""
End Sub

Private Sub ButtonNewArtistClearDOD_Click()
  ComboNewArtistDODJ.Value = ""
  ComboNewArtistDODG.Value = ""
End Sub

Private Sub ToggleNewArtistNickName_Click()
  If ToggleNewArtistNickName.Value = False Then
  TextBoxNewArtistNickName.Enabled = True
  TextBoxNewArtistNickName.Value = ""
  TextBoxNewArtistNickName.BackColor = vbWhite
  ElseIf ToggleNewArtistNickName.Value = True Then
  TextBoxNewArtistNickName.Enabled = False
  TextBoxNewArtistNickName.Value = "-"
  TextBoxNewArtistNickName.BackColor = &H8000000F
  End If
End Sub

Private Sub UserForm_Initialize()
  Dim Range1 As Range, Range2 As Range, c As Range
  
  Set Range1 = Worksheets("Helper Data").ListObjects("Table4").ListColumns(1).DataBodyRange
  Set Range2 = Worksheets("Helper Data").ListObjects("Table4").ListColumns(2).DataBodyRange
  
  For Each c In Range1
    ComboNewArtistDOBG.AddItem c.Text
    ComboNewArtistDODG.AddItem c.Text
  Next
  For Each c In Range2
    ComboNewArtistDOBJ.AddItem c.Text
    ComboNewArtistDODJ.AddItem c.Text
  Next
  
  ToggleNewArtistNickName.Value = True
End Sub

Attached version 2 of the file:

NEW11 - Copy dam2.xlsm

drive.google.com

--------------
Regards
Dante Amor
--------------​