Importing Data to a Form

[bopo]

Hi people :hi:

Right basically I have a textbox on a form which allows users to enter a code, when they click the generate button, I want a macro or something to browse the worksheet (with all codes & desc) , look for the code and import the description (which is within the next colum, left of the code) into a text box on my form

Possible?

Thanks

[Bob Phillips]

TextBox2.Text = Application.Vlookup(TextBox1.Text, Worksheets("Sheet1").Range("A:B"),2,False)

[bopo]

Hi

Thanks for that, however i get the following message:

run time error '9':

subscript out of range

help appriciated

[mdmackillop]

Private Sub TextBox1_AfterUpdate()
TextBox2.Text = Worksheets("Sheet1").Range("B:B").Find(What:=TextBox1.Text, _
LookIn:=xlValues, LookAt:=xlWhole).Offset(, -1)
End Sub

[Bob Phillips]

Hi

Thanks for that, however i get the following message:

run time error '9':

subscript out of range

help appriciated

Perhaps you do not have a sheet called Sheet1, adjust it to your situation.

[bopo]

Thanks for your help so far everyone, Im still getting the same error, so its probably something simple that I cant see, ill explain the best I can.

Senario:

I want the user to enter a code into a textbox, such as B1, or B2 etc, and when the user clicks the 'Generate' button, I want a piece of code to look at spreadsheet 2, find B2 (all codes are the the A colum) look to right colum (B colum) copy the description (look the the C colum) copy the price and then paste it in the description & cost textboxes on the form. I HAVE looked at a vba book, buts its too advance.

<Code behind the 'Generate' button>


Private Sub btngenrate_Click()
btncode1.Text = Application.VLookup(txtcode1.Text, Worksheets("Sheet2").Range("A:B"), 2, False)
end sub

<code behind the description textbox (dont know if I need this>


Private Sub txtcode1_Change()
txtdescription1.Text = Worksheets("Sheet2").Range("B:B").Find(What:=txtcode1.Text, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole).Offset(, -1)
End Sub

[Bob Phillips]

This is all that you need



Private Sub btngenrate_Click(()
With txtCode1
txtDescription1.Text = Application.VLookup(.Text, Worksheets("Sheet1").Range("A:C"), 2, False)
txtCost1.Text = Application.VLookup(.Text, Worksheets("Sheet1").Range("A:C"), 3, False)
End With
End Sub

[bopo]

Thanks again, but I am getting the following error:

run time error '9':

subscript out of range

As im quite new to VBA, I have uploaded it, heres the download link http://www.megaupload.com/?d=NE8951OW

I bet someone can spot what I am doing wrong in 5 seconds, this is my first VBA project, hence my inexperience

Thanks everyone for your help so far

Rob

[tpoynton]

That link doesnt seem to work; you can put the xls file with your post by 'going advanced' and scrolling down to 'manage attachments'. That'll help get your question answered!

[bopo]

taken down (http://www.vbaexpress.com/forum/attachment.php?attachmentid=4694&stc=1&d=1168194071)

[lucas]

It's also .rar compressed....I don't have a rar program any more do you guys? bopo just upload it as xls or zip compressed please.

[bopo]

It's also .rar compressed....I don't have a rar program any more do you guys? bopo just upload it as xls or zip compressed please.

Hi its in xls format in the above post :)

[mdmackillop]

Hi Bopo
Here's a slight change to your form which uses the sheet name in the code. I've also demerged the merged cells as this is a commom source of problems. To make things "more efficient" I've attached the code to the textboxes, rather than the Generate button.

[bopo]

:D, working perfectly, however I would like to know how the code works, just a sentence explaining it so I can understand it better, thanks.



With txtcode1
txtdescription1.Text = Application.VLookup(.Text, Worksheets("Act Data").Range("A:C"), 2, False)
txtcost1.Text = Application.VLookup(.Text, Worksheets("Act Data").Range("A:C"), 3, False)
End With

&



txtdescription1.Text = Application.VLookup(.Text, Worksheets("Act Data").Range("A:C"), 2, False)
txtcost1.Text = Application.VLookup(.Text, Worksheets("Act Data").Range("A:C"), 3, False)

[mdmackillop]

Vlookup checks the first column of a range for a value and returns the value from the column given by the stated value (2 or 3) (Note that this is Relative)

You use False when the first column is not sorted ascending.

BTW Here's some code yoinked from VBHelper to check that a credit card number has been entered correctly.

Private Sub txtcardno_AfterUpdate()
Application.EnableEvents = False
If ValidCard(txtcardno, txtcardtype) = False Then
txtcardno = ""
MsgBox "Card No. Error"
Else
MsgBox "Card No. OK"
End If
Application.EnableEvents = True

End Sub


' Return True if the credit card number
' makes sense.
Public Function ValidCard(ByVal card_number As String, _
ByVal card_type As String) As Boolean
Dim num_digits As Integer
Dim ch As String
Dim i As Integer
Dim odd_digit As Boolean
Dim doubled_digit As String
Dim checksum As Integer

' Assume the validation will fail.
ValidCard = False

' Make sure we have a card type.
If Len(card_type) = 0 Then Exit Function

' Remove any spaces.
card_number = Replace(card_number, " ", "")

' Make sure we have 16 digits.
If Len(card_number) <> 16 Then Exit Function

' Examine the digits.
odd_digit = False
For i = Len(card_number) To 1 Step -1
ch = Mid$(card_number, i, 1)
If ch < "0" Or ch > "9" Then
' Not a digit. Validation fails.
Exit Function
Else
' Process this digit.
odd_digit = Not odd_digit
If odd_digit Then
' Odd digit. Add it to the checksum
checksum = checksum + CInt(ch)
Else
' Even digit. Double it and add the
' digits in the result to the checksum
doubled_digit = Format$(2 * CInt(ch))
checksum = checksum + _
CInt(Left$(doubled_digit, 1))
If Len(doubled_digit) = 2 Then checksum = _
checksum + CInt(Mid$(doubled_digit, 2, _
1))
End If
End If
Next i

' Check the checksum.
If (checksum Mod 10) = 0 Then ValidCard = True

End Function

[Norie]

I know this appears to be solved but I was wondering if you've
considered using comboboxes instead of textboxes.

That would restrict the user to existing codes and you could use it's ListIndex to return the information you want.

See the attached file.