Lookup Text and Display in Form (Excel 2003)

Topic

New Reply

Hi

I have create a Form, I want to look up the correspondence values in column B, C, D in sheet 1 and display these values in the matching textboxes of the form which are; Account Name & Standing Instruction details, after I input the account no and the currency into the form. I have attached the form and data.

How to modify this code to do what I want :

Sub FindText()
Dim wks As Worksheet
Dim rngDataRange As Range
Dim rng As Range
Dim strFindText As String

Set wks = ThisWorkbook.Worksheets(“Sheet1”)
Set rngDataRange = wks.Range(“A:Z”)
strFindText = ” ”

For Each rng In rngDataRange
If rng.Text = strFindText Then
…..”what need to be change here to display the values in the ComboBox”
End If
Next rng
End Sub

Thanks in advance.

Reply | Quote

Replies

I think you are trying to emulate a database in Excel. It might be better to use a database program such as Access for this kind of problem.

Reply | Quote

Hi Hans

Unfortunately, my office does not use Access. Morover, This sheet contain about 1000 entries, hence the idea to create the Form.

Reply | Quote

Perhaps it’s time that your office starts using Access (or another database system).

Reply | Quote

Hi Hans,

Generally every business will have a normal Office appl. but due to some policies, my organisation doesn’t allow that.

Thanks for the suggestion.

Reply | Quote

It would be much easier if you changed your data structure to show all the details on all the rows rather than having blank data – is that possible?

Reply | Quote

Hi Rory,

The blank row actually refers to the account no above, and yes its possible, I just need to copy down.

Reply | Quote

Hi

I have change the codes However, there are some which encounter some problem. The Form don’t show what I want when I click the Exit button.
Private Sub ExitButton_Click()
‘exits the system’
Dim response As String
response = MsgBox(“Are you sure you wish to exit?”, VbMsgBoxStyle.vbYesNo, “check selection”)
If response = vbYes Then
Me.Unload

End Sub

The Vlookup for Currency produce error 13 : type mismatch after I type in a currency code, such as USD
code :
Private Sub txtCurr_AfterUpdate()
With txtCurr
TxtSI.Text = Application.VLookup(.Text, Worksheets(“SI”).Range(“A:D”), 4, False)

End With

End Sub

I am loss as to where did I go wrong. Would appreciate your help.

Reply | Quote

1) response should be declared as an Integer, not as a String

2) The instruction to unload the form is

Unload Me

instead of

Me.Unload

3) You’re missing an End If below the line that unloads the form.

4) Your code refers to several non-existent controls: btnClear, txtCode1, txtCode2, txtName, txtAddress, txtPostCode, txtTown.

5) VLookup always searches for the search value in the first column of the lookup range.

I wouldn’t use a form at all, I would use either AutoFilter or Advanced Filter.

Reply | Quote

Hi Hans,

Thanks. I have managed to complete the codes for the form. However, I noticed that if a user input a wrong ac,
it won’t show an error message, such as ” Invaild Account”. Can you help with the codes.

Thanks

Reply | Quote

Change the text boxes for account and currency to combo boxes, and fill them in the Initialize event with the available values.

If you use AutoFilter instead of a userform, you get dropdown lists automatically, without any programming.

Reply | Quote

Hi Hans,

I know about the Auto and advance filter functions, but I need something where it is easy for the users and I can’t assume that
all will know how to use this. Moreover, this workbook / sheet will be protected and a read only file.

Coming back to this, I am at a loss how to use the ComboBox and the Initialize event. I have read from MS site, VB Help and JW’s book, but still confuse how to use it. . Appreciate that you shed some light on it as I am still learning VB and its beyond my level currently. Thanks

Attached the latest file.

Reply | Quote

You can turn on AutoFilter, then protect the worksheet and specify that AutoFilter is allowed.
AutoFilter is extremely easy to use.

Reply | Quote

Hi Hans

Thanks, I will try on the autofilter however, I still would like to know how to do what you have mentioned above changing the txtboxes to comboboxes and Initialize event with the available values.

thanks in advance

Reply | Quote

See attached version. The code uses a hidden worksheet Sheet2 to store the lists of unique entries.

Reply | Quote

Hi Hans,

Thanks for the guide. However, the txtbox for TxtSI don’t function as expected. I may not be able to use this as there are at least more than 500 different ac to be create in sheet 2.

After about 7 hours of reading, testing and surfing the net. I have solved out the error message part, but my Vlookup doesn’t work for Currency. Its list detail in the SI textbox even the account don’t have that Currency. How can I use the Vlookup with reference to the data in Col A and Col C to produce Col D in the Form. Can you assist in this code.

Thanks

Reply | Quote

Some of your currency entries contain "USD " instead of "USD". Such entries will not be found. Your data must be consistent.

Since you have chosen to ignore my previous reply, I cannot offer further assistance.

Reply | Quote

Hi Hans,

Thanks for trying to help. Your suggestion of using ComboBox is a good idea but I can’t use it in this because I will need to re-create more than 500 entries in Sheet 2 to hold the unique items. I am doing this in order to save time and manual work but its seem like the opposite here.

In the past, you have assist me and guide me to learn while I do my simple stuff and I thought it was a great forum.
Of course, It is at your discretion. Anyway, thanks for all your help.

Reply | Quote

You don’t need to create the list of unique entries, the code in the workbook I posted does that automatically.

Reply | Quote

Hans,

You should have told me earlier so that I don’t waste approximately 7 hrs last week to look up on how to produce error msg.
I will try on this and post back next week. Meanwhile, can you point out which line/s of your codes create the unique list.

Thanks, Appreciate your assistance and guide.

Reply | Quote

Take a look at the UserForm_Initialize procedure. This procedure is executed automatically when the userform is loaded into memory.

The instruction

Worksheets(“SI”).Range(“A1”).CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Worksheets(“Sheet2”).Range(“A1”), _
Unique:=True

copies the unique values of the acid column to Sheet2 (since A1 on Sheet2 contains the word acid.

You may want to make Sheet2 temporarily visible to see how it works (using Format | Sheet | Unhide).

Reply | Quote