Hi charlie,
Got it.
Ok, the error is just indicating that the vlookup failed to find the Part#.
It failed for at least two reasons: (1) The VLookup is in the Change event,
which fires after every change, such as after each keypress - -so the whole
part# was probably not entered yet, and (2) The value of textboxes is text
(a string in vba terminology), but the Part#s in your database are numbers.
You can resolve issue #1 by handling/ignoring errors, but leaving the code
in the Change event, if you want Vlookups to execute repeatedly while
someone types, i.e.:
Private Sub TextBox8_Change()
On Error Resume Next
TextBox5.Value = WorksheetFunction.VLookup(TextBox8.Value, Range("DBase"),
2, False)
End Sub
You can resolve issue #2 by use of the Val() function to convert the text to
a number:
Private Sub TextBox8_Change()
On Error Resume Next
TextBox5.Value = WorksheetFunction.VLookup(Val(TextBox8.Value),
Range("DBase"), 2, False)
End Sub
You could, if desired, make the description field go blank rather than
retaining it's previous value if there was an error in the vlookup:
Private Sub TextBox8_Change()
On Error Resume Next
TextBox5.Value = WorksheetFunction.VLookup(Val(TextBox8.Value),
Range("DBase"), 2, False)
If Err.Number <> 0 Then TextBox5.Value = ""
End Sub
Back to issue #1: If you want to only do your Vlookups when the user has
completely finished entering the part#, you should use a different event
instead. There are several events that might be appropriate, but probably
BeforeUpdate would be a good call barring a specific reason to use another
event:
Private Sub TextBox8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
TextBox5.Value = WorksheetFunction.VLookup(Val(TextBox8.Value),
Range("DBase"), 2, False)
If Err.Number <> 0 Then TextBox5.Value = "" ' Optional
End Sub
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Cab Boose
Sent: Sunday, February 05, 2012 10:48 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ vlookup in Text Box
Hi Asa
Found the files see attached.
Charlie in New Zealand
On Mon, Feb 6, 2012 at 7:14 PM, Asa Rossoff <a...@lovetour.info> wrote:
Charlie,
Hmm. still not getting it. Maybe an Excel 2010 incompatibility with your
file?
Can you right-click the userform and select Export File... then send the
resulting .frm and .frx files?
Thanks,
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Cab Boose
Sent: Sunday, February 05, 2012 9:10 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ vlookup in Text Box
Hi Asa
Workbook attached. The code is in Userform 1. Textbox 8
Thanks
Charlie
On Mon, Feb 6, 2012 at 2:43 PM, Asa Rossoff <a...@lovetour.info> wrote:
Hi Charlie,
Can you post your workbook? The original workbook you posted did not seem
to contain your VBA project--just the data.
If I can see your form code I can better tell what might be going on.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Cab Boose
Sent: Sunday, February 05, 2012 1:35 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ vlookup in Text Box
Hi Asa
Have changed the two things, but now I get a 1004 error. Unable to get the
vlookup of the worksheet function class.
Note that I have Option Explicit at top of page, do I need to declare
anything ?
Thanks
Charlie
On Sun, Feb 5, 2012 at 8:20 PM, Asa Rossoff <a...@lovetour.info> wrote:
Hi Charlie,
Glad my last post helped :)
>From a quick look I see two issues/potential issues:
1. The syntax error:
TextBox5.Value = WorksheetFunction.VLookup(TextBox8.Value,
Range.("DBase"),2,False)
Remove the "." between "Range" and ("Dbase"):
TextBox5.Value = WorksheetFunction.VLookup(TextBox8.Value, Range
("DBase"),2,False)
2. The Defined Names (ranges) in your workbook. They include the
header row, which can interfere with lookup functions. Not a high
likelihood in the exact usage that you have here, but still might be a good
idea to change.
P.S. In my last post, I used ActiveWorkbook.Worksheets(.).Range(.) in the
Userform_Initialize event to set up the linked cell. You can consider if
ThisWorkbook is more appropriate than ActiveWorkbook in a given instance. I
actually recommend always using ThisWorkbook if the workbook containing the
VBA code/userform is the workbook you mean and only using ActiveWorkbook
when you might mean another workbook---that which is on-screen at any given
moment. Avoid pitfalls that way. Whoops :)
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Cab Boose
Sent: Saturday, February 04, 2012 10:48 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ vlookup in Text Box
Hi
See attached worksheet. Excel2000
I have userform1 and one of the codes is to lookup a part # (in Textbox
8)( say Part # 30000 is to be entered here) in a named range called DBase
Once located to place the description in textbox 5(named Description)
This is the code as per attached workbook, have played around with the code
but still not working. Currently get a message for Syntax error.
Private Sub TextBox8_Change()
TextBox5.Value = WorksheetFunction.VLookup(TextBox8.Value,
Range.("DBase"),2,False)
End Sub
So if 3000 entered in Part # text box8 I would expect to get in Textbox 5
the description: Print.Download-1/64...................
Will have a 1000 + rows eventually. Will make the named range dynamic as
well.
Not much code to check. Your advice much appreciated.
Thanks
Charlie
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
