Using a ComboBox for searching in VBA
Solved
Usgpa
Posted messages
14
Status
Membre
-
Usgpa -
Usgpa -
Hello,
I am a beginner in VBA Excel and I am submitting a case that will probably seem very simple to you.
Actually, I have a list of documents in Sheet2 with 4 columns of characteristics (A, B, C, and D). I have created a Userform where I have a combobox that allows selecting an item from the list by its characteristic A. I would like to know how to make the corresponding data B, C, and D appear in the three Textboxes of my Userform.
Thank you in advance for the time you will give me.
I am a beginner in VBA Excel and I am submitting a case that will probably seem very simple to you.
Actually, I have a list of documents in Sheet2 with 4 columns of characteristics (A, B, C, and D). I have created a Userform where I have a combobox that allows selecting an item from the list by its characteristic A. I would like to know how to make the corresponding data B, C, and D appear in the three Textboxes of my Userform.
Thank you in advance for the time you will give me.
18 réponses
Hello,
In Microsoft Visual Basic, double-click on the combo box, the following code appears:
Between these two lines write:
Private Sub ComboBox1_Change()
End Sub
Of course, adapt it well
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
In Microsoft Visual Basic, double-click on the combo box, the following code appears:
Private Sub ComboBox1_Change() End Sub
Between these two lines write:
Private Sub ComboBox1_Change()
Dim a As Integer Dim cherche As String cherche = ComboBox1.Value a = Sheets("Feuil1").Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row TextBox1 = Range("A" & a).Offset(0, 1).Value TextBox2 = Range("A" & a).Offset(0, 2).Value TextBox3 = Range("A" & a).Offset(0, 3).Value End Sub
Of course, adapt it well
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
try without Sheets(""Sheet2"") like this:
a = Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row
Otherwise, how do you complete your combobox?
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
a = Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row
Otherwise, how do you complete your combobox?
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
Hello,
Of course. The code:
or
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, then where is the problem? --
Of course. The code:
a = Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Rowreturns a number: the row number. Then, you can use it to delete one or more cells:
Range("B" & a).Delete or
Range(Cells(1,a),Cells(6,a)).Deleteto delete from column A to column F
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, then where is the problem? --
Thank you Pijaku. Apparently my VBA is blocking on:
a = Sheets("Sheet2").Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row
It highlights it in yellow and reports a Type "9" error.
a = Sheets("Sheet2").Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row
It highlights it in yellow and reports a Type "9" error.
Is there a type "9" error? What is the complete error message?
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
Pijaku Thank you!!! Your second solution was the right one!!! Thank you so much and have a good day!
Hello,
I would like to know if it is possible, using a "delete" button in a userform, to delete the found cells without having to delete the entire row.
Thank you in advance.
I would like to know if it is possible, using a "delete" button in a userform, to delete the found cells without having to delete the entire row.
Thank you in advance.
Thank you again Pijaku. Once again your code works but unfortunately, once the cells are deleted, my first userform bugs because the code (a = Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row) becomes yellow since the value that served as a reference no longer exists. The bug also occurs if I unload it when opening the second userform...
Do you have any idea?
Do you have any idea?
We return to my question from post 5: So how do you complete your combobox?
I also don't understand why you don't want to delete the entire row? In database management, you either replace or delete an entire row, but leaving empty cells is absolutely not good for a db...
--
Best regards, and..... Happy New Year, my best wishes for 2010!!!
-- Every problem has a solution. If there is no solution, where is the problem? --
I also don't understand why you don't want to delete the entire row? In database management, you either replace or delete an entire row, but leaving empty cells is absolutely not good for a db...
--
Best regards, and..... Happy New Year, my best wishes for 2010!!!
-- Every problem has a solution. If there is no solution, where is the problem? --
My combobox takes the first column of my sheet, which is one of the characteristics of the listed objects.
Actually, I wanted to avoid deleting the entire row so as not to remove the buttons and statistics that I placed to the right of the table... If that poses a problem, I will place them in another sheet; I’m not yet familiar with VBA... and therefore don’t yet have the useful reflexes :)
Thank you.
Actually, I wanted to avoid deleting the entire row so as not to remove the buttons and statistics that I placed to the right of the table... If that poses a problem, I will place them in another sheet; I’m not yet familiar with VBA... and therefore don’t yet have the useful reflexes :)
Thank you.
In fact, not knowing how to program a combo to only contain the non-empty cells of a column, I programmed it to take all the cells from column A. As a result, the disappearing cells are not visible.
So I will create a homepage with buttons, etc. This way I can delete the entire row. Can you give me the formula to delete an entire row?
So I will create a homepage with buttons, etc. This way I can delete the entire row. Can you give me the formula to delete an entire row?
Simply put, adapted to your case:
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
--
Every problem has its solution. If there is no solution, where is the problem? --
a = Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row Rows(a).Delete
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
--
Every problem has its solution. If there is no solution, where is the problem? --
Thank you very much. However, once the line is deleted, there is still an error message highlighting in yellow:
a = Feuil2.Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row
of the first userform...
Error 1004
a = Feuil2.Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row
of the first userform...
Error 1004
yes since you're looking again (I don't know why by the way) for the value "search" that you just removed...
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
In fact, I programmed the search to start every time there is a change in ComboBox1 (Private Sub ComboBox1_Change()). The strange thing is that the search starts even when my userform containing the combobox is closed... I thought that would solve the problem but apparently it hasn't...
Hi,
how did you close it? If you only did:
Try:
It would be best if you posted all your code here so we can see why it's not working. I will have a bit of time tomorrow if you want...
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --
how did you close it? If you only did:
UserForm1.Hidethat’s not enough.
Try:
Unload UserForm1
It would be best if you posted all your code here so we can see why it's not working. I will have a bit of time tomorrow if you want...
--
Best regards, and ..... Happy New Year, my best wishes for 2010!!!
-- Every problem has its solution. If there is no solution, where is the problem? --