Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box: Vlookup Multiple Cells by referencing to Text Box
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("C5:D1878").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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box: Vlookup Multiple Cells by referencing to Text Box
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 BigMac wrote: 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("C5:D1878").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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing Cells in Multiple Worksheets | Excel Worksheet Functions | |||
Referencing cells in multiple worksheets | Excel Discussion (Misc queries) | |||
Directly referencing multiple cells | New Users to Excel | |||
Using vlookup when referencing text | Excel Discussion (Misc queries) | |||
Vlookup? Referencing cells? | Excel Worksheet Functions |