Could someone please check my IF statement?

[Adam88]

Hi Guys,

Hope you all had a great New Years!

First day back in work and I'm already at a stumbling block... Can anyone please offer advice on why this IF statement is trying to carry out the code after Else even though the initial part of the IF statement is TRUE?

Private Sub cbbEmployee_Change()

Dim EmpName As Variant

If IsNull(Me.cbbEmployee.Value) Then
    txtHourly.Value = "0"
Else
    EmpName = cbbEmployee.Value
    txtHourly.Value = WorksheetFunction.VLookup(EmpName, Worksheets("Formulas").Range("T1:U53"), 2, False)
End If

End Sub

It's a combo box, I select an employee and the Vlookup pulls in their hourly rate from another worksheet. Once the user clicks Save, it runs;

me.cbbEmployee.Clear

Which then prompts this section of code. Its supposed to check for Null values and then just populate the txtHourly box with a 0 if nothing is detected as selected in the combobox. I receive an error 1004 "Unable to get the Vlookup property etc etc" which means its trying to carry out the latter part of the code even though the combobox has nothing in it!

Hope you can help

 

[Special-K99]

Display the length of the combobox value before the If.

result should be 0, if it's not then you have a value in the combobox which is therefore not a null hence the "Else" part of the code being executed.

 

[Adam88]

Thank you for your advice.

I've F8'd my way through the code and added a line to check the combobox value prior to the IF statement, it's coming back as ""

Is there a way I can set this so that it satisfies the IsNull criteria?

Thanks

 

[Special-K99]

That's weird.
If I place a value in A1 and run this code

If Isnull(Range("A1")) then
    MsgBox "is null"
else
    Msgbox "is not null")

I get "is not null" which is correct.
If I then blank out the value in A1...

I still get "is not null"

Can't you change your code to

If (Me.cbbEmployee.Value="" Then

 

[Special-K99]

I know this is Access but post #4 on this page may suggest Excel operates the same way.

https://www.access-programmers.co.uk/forums/showthread.php?t=97256

 

[Adam88]

After reading that link you sent and also your original reply to check length, I've adapted the code to now do this;

If Len(Me.cbbEmployee.Value) = 0 Then
    txtHourly.Value = "0"
Else
    EmpName = cbbEmployee.Value
    txtHourly.Value = WorksheetFunction.VLookup(EmpName, Worksheets("Formulas").Range("T1:U53"), 2, False)
End If

Which works perfectly! Thank you for the idea!