UserForm: Change TextBox.Value based on ComboBox selection - HOW? 1

[MeGustaXL]

Hi Guys,

I'm thick, I know, but I can't get this to work!

A sheet called "Data" with this table on it:

Serial Tail
No No
101 XZ300
102 XZ301
103 XZ302
104 XZ303
105 XZ304
106 XZ305
107 XZ306
... ..... etc

The whole table has the name "Fits", and is dynamic (Oh NO! not Dynamic Ranges again!!)
The Serial_No column is also dynamic, and it's called...
Yup, you guessed it: Serial_No

UserForm1 has a ComboBox called cboSerialNo and a TextBox called txtTailNo.
cboSerialNo has its RowSource property set to Data!Serial_No.

Button on the sheet to show the UserForm.
Click the button, up comes the form, select one of the numbers from the drop-down list (which appears perfectly),
then the ComboBox Change event does this:

Private Sub cboSerialNo_Change()
TextBox1.Value = Application.WorksheetFunction.VLookup(cboSerialNo.Value, Range("Fits&quot, 2, 0)
End Sub

Only I get "Unable to get the VLookup property of the WorksheetFunction class" .... cue Fist through monitor, embarrassing sniggers from others in office, etc.

I know it's a piece of cake, I KNOW it's my syntax that's wrong, but WHY/HOW/WHERE??

Sorry to waste your valuable time: is there a FAQ on this subject?

Chris

 

[Bowers74]

Try [Fits] instead of Range("Fits&quot.

And see my FAQ: faq707-4090

There is also a bit on Ranges in there. ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

Jeepers, Mike - you are quick!
But alas, it doesn't work
I've changed it to:

Private Sub cboSerialNo_Change()
TextBox1.Value = Application.WorksheetFunction.VLookup(cboSerialNo.Value,[Fits], 2, 0)
End Sub

and tried doing "answer=Application.Work...." then MsgBox'ing that - same error

Any other ideas?

Chris

BTW, I've printed out that FAQ so I can refer to it even more than the Wife's photo!! HAR-HAR!

 

[Bowers74]

I don't understand.

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

works for me, and (as you know [DataTable] is a dynamic Range.

What Range is being loaded as the RowSource for cboSerialNo? [Fits] or [Serial_No]?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

[Serial_No]

 

[MeGustaXL]

This works:

Private Sub cboSerialNo_Change()
[G6].Value = cboSerialNo.Value
TextBox1.Value = [G7].Value
End Sub

Where [G7] contains the formula: =VLOOKUP(G6,Fits,2,0)

But

TextBox1.Value = Application.WorksheetFunction.VLookup([G6], [Fits], 2, 0).Value gives an "Object Required" error.

Am I supposed to DIM all these ranges before I use 'em?

Chris

 

[Bowers74]

I just create what you have described and it worked for me:

Two dynamic Ranges [Serial_No] & [Fits] and here is the code for my UserForm, which contains a ComboBox (cboSerialNo) and a TestBox (TextBox1):

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

Private Sub UserForm_Initialize()
cboSerialNo.RowSource = [Serial_No].Address
End Sub

I can't figure out what isn't working? Have I missed something?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

No Mike, you've got everything as required, but when I copy and paste your code I still get the "Unable to get the VLookup property of the WorksheetFunction class" error.

I'm using XL2K under WinNT, and I can't figure out why it works if I use the worksheet as a half-way house but not directly!

I'll mull it over tonight with a bottle of red and see if anything occurs to me.

Cheers M8!

Chris

 

[MeGustaXL]

Hey Mike, I posed the same question on Ozgrid and Andy Pope solved it by modifying the Change() event to read:

Private Sub cboSerialNo_Change()
TextBox1.Value = WorksheetFunction.VLookup(Clng(cboSerialNo.Value), [Fits], 2, 0)
End Sub

This converts the text value from the cbo to a Long number! Neat, huh?

However, I'll be using a mix of data types in the real table, ie A101, 102, BX103-Z, and so on. Doing that makes it crash, so I changed the cell formatting of the Serial_No column to "Text", removed the Clng operator, and it works!

Thanks again for all your help - wouldn't be the "Office Excel Ace" without you guys!

Varium et mutabile semper femina

Chris

 

[Bowers74]

Hey Chris,

Instead of always having to worry about changing the column's format to "Text", you could also use the Find method.

Private Sub cboSerialNo_Change()
TextBox1.Value =

[Fits].Find(cboSerialNo, , xlValues).Offset(0, 1)

End Sub
[code]



Peace! [peace]

Mike

[COLOR=red][b]Never say Never!!!
Nothing is impossible!!![/b][/color]

 

[MeGustaXL]

That's Cooool! I can think of several ways I could put that method to use in my projects right now! Have a great big glittery, shiny star for finding yet another way to excoriate this feline!

Happiness!

Chris

 

[Bowers74]

Thanks for the star!

I don't know why I didn't think about the Find method sooner, since I ALWAYS use it instead of VLOOKUP in my procedures.

It is recommended to try to use the worksheetfunctions in your procedures as often as possible (I guess it's faster), but in the case of VLOOKUP, we saw that there can be problems with different data types.

Glad I could help!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!