Combobox/Textbox question

[Chewyhairball]

I have the following bit of code that works fine with my user form combobox

If ComboBox1.Value <> Application.WorksheetFunction.VLookup(Textbox3.Value, InfoTable, 6, False) Then
MsgBox "The selected value does not match " & textbox3.Value & " value previously input." & vbNewLine & vbNewLine & "Please recheck before inputing.", 64, "Value does not match"
Exit Sub
End If

I also have the following except it is for textbox2 and it doesnt work.

If textbox2.Value <> Application.WorksheetFunction.VLookup(textbox3.Value, InfoTable, 2, False) Then
MsgBox "The selected value does not match " & textbox3.Value & " value previously input." & vbNewLine & vbNewLine & "Please recheck before inputing.", 64, "Value does not match"
Exit Sub
End If

It relays the msgbox regardless if the values match or dont match and i cant figure out why.

The table range for 'InfoTable' is definately correct as it the col_index_num

it is driving me mad

Any ideas as to why the combobox would work but the textbox wouldnt?

Rory

 

[Jimmypop]

Hi there

Based on the information you have provided, it seems like the issue is not with the code syntax or the range used in VLookup, as the same range and syntax are used in both cases. Therefore, it is likely that the issue lies elsewhere.

One possibility is that the value in the textbox is not being passed correctly to the VLookup function. You can try adding a debug print statement to check the value of textbox2 just before the VLookup function is called, like this:

Debug.Print "textbox2.Value: " & textbox2.Value
If textbox2.Value <> Application.WorksheetFunction.VLookup(textbox3.Value, InfoTable, 2, False) Then

This will print the value of textbox2 in the immediate window, so you can verify that the correct value is being passed to the VLookup function.
Another possibility is that the value being looked up in the VLookup function (i.e., the value in textbox3) does not exist in the second column of the InfoTable range. You can check this by manually verifying that the value in textbox3 exists in the second column of InfoTable.

Let us know how it goes...

 

[mikerickson]

Are the values being entered numerical? TextBoxes and ComboBoxes only contain strings. VLOOKUP might be returning a number, which isn't equal to a string

"1234" <> 1234

 

[Chewyhairball]

Hi there

Based on the information you have provided, it seems like the issue is not with the code syntax or the range used in VLookup, as the same range and syntax are used in both cases. Therefore, it is likely that the issue lies elsewhere.

One possibility is that the value in the textbox is not being passed correctly to the VLookup function. You can try adding a debug print statement to check the value of textbox2 just before the VLookup function is called, like this:

Debug.Print "textbox2.Value: " & textbox2.Value
If textbox2.Value <> Application.WorksheetFunction.VLookup(textbox3.Value, InfoTable, 2, False) Then

This will print the value of textbox2 in the immediate window, so you can verify that the correct value is being passed to the VLookup function.
Another possibility is that the value being looked up in the VLookup function (i.e., the value in textbox3) does not exist in the second column of the InfoTable range. You can check this by manually verifying that the value in textbox3 exists in the second column of InfoTable.

Let us know how it goes...

Hi

The debug print didn,t do anything. I tried it for the combobox and it didnt do anything with that either.
The value definately exists in the table yes. Its very confusing.

 

[Chewyhairball]

Are the values being entered numerical? TextBoxes and ComboBoxes only contain strings. VLOOKUP might be returning a number, which isn't equal to a string

"1234" <> 1234

The combobox is input via a dropdown menu on the user form and the textboxes are manually input.

 

[Chewyhairball]

So i tried a few experiments.

if i just use a value then it works

If textbox2.Value <> 50 Then

but if i put a value in cell A1(50) and reference that cell it doesnt work

If textbox2.Value <> Range("A1").Value Then

It does however work for the combobox

If combobox1.Value <> Range("A1").Value Then

It looks like the combobox is treating the data differently.???

It is still confusing as i have other bits of code in the same sub that does work when comparing textboxes to cell references

If (textbox4.Value) * (textbox7.Value) + Range("potest").Value > Range("G3") Then
  Me.Row4.SetFocus
 MsgBox "The Maximum will be exceeded" & vbNewLine & vbNewLine & "Maximum Set Limit - " & Range("G3") & vbNewLine & "Potential  -" & TextBox2.Value &, 64, "Max Limit"
  Exit Sub
End If

 

[Chewyhairball]

So....I managed to get it to work. I dont know what I am doing but used logic in that somehow the combobox was activating the number and the text box wasnt.
I thought i would try and force the textbox to calculate by multiplying. I used 1 so it didn't change the figure.
It works. I dont know if it the correct way to do it but it does what i need it to do

If (textbox2.Value) * 1 <> Application.WorksheetFunction.VLookup(textbox3.Value, InfoTable, 2, False) Then

MsgBox "The selected value does not match " & textbox3.Value & " value previously input." & vbNewLine & vbNewLine & "Please recheck before inputing.", 64, "Value does not match"

Exit Sub

End If