Combo Box: Vlookup Multiple Cells by referencing to Text Box

[BigMac]

Hi there. I'm currently doing a volunteer project on setting up a
borrowing system for a small museum library using EXCEL and VBA,
however my knowledge of VBA is truly rudimentary, so I need a lot of
help from the experts here.

The main problem I face now is this: The library makes use of the Dewey
Decimal Number (DDN) system to keep track of its books. However this
DDN is not unique to each book i.e. one DDN can have many books.

As I did up my user form, everything was successful up till the point
whereby I set up a combobox which references to a book database. By
typing the DDN into a textbox, all the books having the same DDN should
appear in the combobox. My simple programming is as below:


Private Sub cmdFindBook_Click()

Dim MyRange As Variant
Dim MyRange2 As Variant
Dim colNumber As Long

MyRange = Sheets("Book Database").Range("B5:G1878").Value
MyRange2 = Sheets("Book Database").Range("C51878").Value

cbxBookTitle.Text = ""
lblAuthor.Caption = ""

On Error GoTo Errorhandler

If txtDDN.Text = "" Then

MsgBox ("Please enter Dewey Decimal Number.")

Else

cbxBookTitle.Text = Application.WorksheetFunction.VLookup(txtDDN.Text,
MyRange, 2, False)
lblAuthor.Caption =
Application.WorksheetFunction.VLookup(cbxBookTitle.Text, MyRange2, 2,
False)

End If
Exit Sub

Errorhandler:
MsgBox ("No book found.")


End Sub

Foolish me is unable to get a list of the books with the same DDN to
appear in the combo box drop down list. Can anyone give me any guidance
at all? I understand it might be something pretty complex, so thank you
guys for your help

Cheers!
Mac

 

[dolivastro]

Well, you might take this as nothing but a teaser, but I think you are
using the wrong application. The books of a library, even a small one,
belong in a database. My advice is: Move over to Access, or (what I
think is better) to a stand alone VB executable that uses ADO to
connect to the Access backend.

What you are doing is called "querying a database", and you want to
populate the combo box with the ResultSet.

I know that doesn't help, in fact it makes things more difficult for
you, but I think you are going to write a large Excel / VBA program
that crashes once you get too many books in your database. So ...
change now!

Just my $.02

Dom