[Solved] VBA: UserForm TextBox.Value change based on ComboBo

Hi Folks,

I know I'm thick, but I just can't get the attached to work.

Its got a 2-column table, column A is named "Serial_No", and the whole table is named "Fix".

Click the button and a UserForm pops up with a ComboBox linked to Serial_No, and a TextBox in which I want to display the Tail Number associated with the Serial number picked from the ComboBox - "Piece of cake!" you all shout. You just set the TextBox.Value in the ComboBox Change() event, like this:

Private Sub cboSerialNo_Change()
txtTailNo.Value = WorksheetFunction.VLookup(cboSerialNo.Value, [Fix], 2, 0)
End Sub

But that gives an error every time, either "unable to set VLookup property of WorksheetFunction class" if I specify the range name complete with sheet! name, or "Argument not optional" if I don't! :mad:

How the Sam Hill can I fix this?

Chris

Cheers Andy, that's fixed it!

I modified it to Clng(cboSerialNo.Value), but left [Fix] with the square brackets ('cos that means it's a range) - and BINGO!

Supplementary Question:

I understand that Clng converts the variable to type Long, and that if I just have "=cboSerialNo.Value" then that should be text, but what if the data in Serial Number is a mixture of numbers and text, ie A101, 102, BX-103-Z, etc? Where (or how) is the best way to handle mixed data types?

Edit: I've just change the formatting of the Serial_No column to Text and removed the "CLng" operator and it works fine for both data types! Woo-Hoo!

Still Big Thankus Muchlius to Andy for pointing me in the right direction tho'

Crash