Vlookup in a userform

  • Thread starter Thread starter R D S
  • Start date Start date
R

R D S

Hi,
If I enter a number into a userform in a box called 'reference' can I then
use a vlookup to fill in the next box 'name' from an external file?
I can use the vlookup on a sheet but am not sure of the syntax? within VBa.

TIA,
Rick
 
With such a generic question a specific answer isn't possible. But in
general if you can write a formula in a cell that returns the result you
want, yes, you can do it from VB.

--
Jim Rech
Excel MVP
| Hi,
| If I enter a number into a userform in a box called 'reference' can I then
| use a vlookup to fill in the next box 'name' from an external file?
| I can use the vlookup on a sheet but am not sure of the syntax? within
VBa.
|
| TIA,
| Rick
|
|
 
=IF(ISNA(VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)),"",VLOOKU
P($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE))

OK....
the above formula works on the sheet.
i am trying to implement the above into a userform and instead of looking at
cell B1 the lookup value is data entered into a txtbox named 'reference' and
the value needs to be returned to the next textbox in the userform which is
named 'name'.

Thanks,
Rick


Jim Rech wrote:
|| With such a generic question a specific answer isn't possible. But
|| in general if you can write a formula in a cell that returns the
|| result you want, yes, you can do it from VB.
||
|| --
|| Jim Rech
|| Excel MVP
|| ||| Hi,
||| If I enter a number into a userform in a box called 'reference' can
||| I then use a vlookup to fill in the next box 'name' from an
||| external file?
||| I can use the vlookup on a sheet but am not sure of the syntax?
||| within VBa.
|||
||| TIA,
||| Rick
 
I think a pretty easy way to accomplish this is to put that formula in an out of
the way spot (maybe A1 of a hidden worksheet).

Then have your code update B1 of that worksheet when you leave textbox1.

Option Explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With ThisWorkbook.Worksheets("Hidden")
.Range("b1").Value = Me.TextBox1.Value
'application.calculate
Me.TextBox2.Value = .Range("a1").Value
End With

End Sub

Maybe even toss in an application.calculate if you think it's necessary.
=IF(ISNA(VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)),"",VLOOKU
P($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE))

OK....
the above formula works on the sheet.
i am trying to implement the above into a userform and instead of looking at
cell B1 the lookup value is data entered into a txtbox named 'reference' and
the value needs to be returned to the next textbox in the userform which is
named 'name'.

Thanks,
Rick

Jim Rech wrote:
|| With such a generic question a specific answer isn't possible. But
|| in general if you can write a formula in a cell that returns the
|| result you want, yes, you can do it from VB.
||
|| --
|| Jim Rech
|| Excel MVP
|| ||| Hi,
||| If I enter a number into a userform in a box called 'reference' can
||| I then use a vlookup to fill in the next box 'name' from an
||| external file?
||| I can use the vlookup on a sheet but am not sure of the syntax?
||| within VBa.
|||
||| TIA,
||| Rick
Click to expand...
 
If the workbook you're looking up in is closed, as your formula seems to
indicate, then I think Dave's approach is the way to go. With an open
workbook you can also do the lookup without using a formula on a worksheet.
This illustrates two ways to do it although you'll have to adapt them to
your specific situation:

Sub a()
Dim WB As String
Dim WS As String
Dim Rg As String
Dim Result As Variant
Dim Val2Lookup As Variant
WB = "book1.xls"
WS = "Sheet1"
Rg = "A1:B3"
Val2Lookup = "b"
Result = Application.VLookup(Val2Lookup, _
Workbooks(WB).Worksheets(WS).Range(Rg), _
2, False)
End Sub

Sub aa()
Dim Result As Variant
Dim Val2Lookup As Variant
Val2Lookup = "b"
Result = Application.Evaluate("VLOOKUP(""" & Val2Lookup _
& """,[Book1.xls]Sheet1!$A$1:$B$3,2,FALSE)")
End Sub

Btw, the formula you posted is really inefficient:

VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)

You reference a range with 35 columns when all you need is 3 since your
offset is 3:

VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$C,3,FALSE)),"",


--
Jim Rech
Excel MVP
|
=IF(ISNA(VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)),"",VLOOKU
| P($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE))
|
| OK....
| the above formula works on the sheet.
| i am trying to implement the above into a userform and instead of looking
at
| cell B1 the lookup value is data entered into a txtbox named 'reference'
and
| the value needs to be returned to the next textbox in the userform which
is
| named 'name'.
|
| Thanks,
| Rick
|
|
| Jim Rech wrote:
||| With such a generic question a specific answer isn't possible. But
||| in general if you can write a formula in a cell that returns the
||| result you want, yes, you can do it from VB.
|||
||| --
||| Jim Rech
||| Excel MVP
||| |||| Hi,
|||| If I enter a number into a userform in a box called 'reference' can
|||| I then use a vlookup to fill in the next box 'name' from an
|||| external file?
|||| I can use the vlookup on a sheet but am not sure of the syntax?
|||| within VBa.
||||
|||| TIA,
|||| Rick
|
|
|
|
 
Thanks, ill have a look at those.
As to the inefficient formula I have other Vlookups on the sheet which use
the whole range, in this sort of instance would there be any difference? If
so I will trim them as necessary.

Regards,
Rick

Jim Rech wrote:
|| If the workbook you're looking up in is closed, as your formula
|| seems to indicate, then I think Dave's approach is the way to go.
|| With an open workbook you can also do the lookup without using a
|| formula on a worksheet. This illustrates two ways to do it although
|| you'll have to adapt them to your specific situation:
||
|| Sub a()
|| Dim WB As String
|| Dim WS As String
|| Dim Rg As String
|| Dim Result As Variant
|| Dim Val2Lookup As Variant
|| WB = "book1.xls"
|| WS = "Sheet1"
|| Rg = "A1:B3"
|| Val2Lookup = "b"
|| Result = Application.VLookup(Val2Lookup, _
|| Workbooks(WB).Worksheets(WS).Range(Rg), _
|| 2, False)
|| End Sub
||
|| Sub aa()
|| Dim Result As Variant
|| Dim Val2Lookup As Variant
|| Val2Lookup = "b"
|| Result = Application.Evaluate("VLOOKUP(""" & Val2Lookup _
|| & """,[Book1.xls]Sheet1!$A$1:$B$3,2,FALSE)")
|| End Sub
||
|| Btw, the formula you posted is really inefficient:
||
|| VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)
||
|| You reference a range with 35 columns when all you need is 3 since
|| your offset is 3:
||
|| VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$C,3,FALSE)),"",
||
||
|| --
|| Jim Rech
|| Excel MVP
|| |||
||
=IF(ISNA(VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)),"",VLOOKU
||| P($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE))
|||
||| OK....
||| the above formula works on the sheet.
||| i am trying to implement the above into a userform and instead of
||| looking at cell B1 the lookup value is data entered into a txtbox
||| named 'reference' and the value needs to be returned to the next
||| textbox in the userform which is named 'name'.
|||
||| Thanks,
||| Rick
|||
|||
||| Jim Rech wrote:
||||| With such a generic question a specific answer isn't possible.
||||| But in general if you can write a formula in a cell that returns
||||| the result you want, yes, you can do it from VB.
|||||
||||| --
||||| Jim Rech
||||| Excel MVP
||||| |||||| Hi,
|||||| If I enter a number into a userform in a box called 'reference'
|||||| can I then use a vlookup to fill in the next box 'name' from an
|||||| external file?
|||||| I can use the vlookup on a sheet but am not sure of the syntax?
|||||| within VBa.
||||||
|||||| TIA,
|||||| Rick
 
Cheers Dave, that is pretty much the way I ended up going about it. I seem
to remember reading somewhere that it was scorned upon to do calculations on
the sheet while running vb.
If it works its good for me though.

Rick

Dave Peterson wrote:
|| I think a pretty easy way to accomplish this is to put that formula
|| in an out of the way spot (maybe A1 of a hidden worksheet).
||
|| Then have your code update B1 of that worksheet when you leave
|| textbox1.
||
|| Option Explicit
|| Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
||
|| With ThisWorkbook.Worksheets("Hidden")
|| .Range("b1").Value = Me.TextBox1.Value
|| 'application.calculate
|| Me.TextBox2.Value = .Range("a1").Value
|| End With
||
|| End Sub
||
|| Maybe even toss in an application.calculate if you think it's
|| necessary.
||
|| R D S wrote:
|||
|||
=IF(ISNA(VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)),"",VLOOKU
||| P($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE))
|||
||| OK....
||| the above formula works on the sheet.
||| i am trying to implement the above into a userform and instead of
||| looking at cell B1 the lookup value is data entered into a txtbox
||| named 'reference' and the value needs to be returned to the next
||| textbox in the userform which is named 'name'.
|||
||| Thanks,
||| Rick
|||
||| Jim Rech wrote:
||||| With such a generic question a specific answer isn't possible.
||||| But in general if you can write a formula in a cell that returns
||||| the result you want, yes, you can do it from VB.
|||||
||||| --
||||| Jim Rech
||||| Excel MVP
||||| |||||| Hi,
|||||| If I enter a number into a userform in a box called 'reference'
|||||| can I then use a vlookup to fill in the next box 'name' from an
|||||| external file?
|||||| I can use the vlookup on a sheet but am not sure of the syntax?
|||||| within VBa.
||||||
|||||| TIA,
|||||| Rick
||
|| --
||
|| Dave Peterson
 
Back
Top