CODEWITHSUNDEEP
excelvba
ranganatha channarayapatna
ranganatha channarayapatna

Reputation: 13

Click event of Combobox control

I have hardcopies of 3000 invoices, in which the details of the product and the name of the dealer are available. The sales data from SAP has been exported to excel for analysis. But unfortunaltely the name of the dealer is not found in the exported data against each invoice. Hence I have decided to incorporate the dealer's name against each invoice. For this I have embedded a Combobox Control on to the worksheet containing the sales data, to which I have loaded the names of all dealers.

To the left of the Combo control in a cell say Cells(1,1) I enter the invoice number and select the corresponding dealer as mentioned in hardcopy of the invoice from the Combo control so that with the click on the name of the dealer the name gets printed against the invoice number in the next column. To do this I have written the following macro in the Combobox1_ Click event.

Sub Combobox1_Click()
For i = 5 to 3000
If cells(1,1).value = Cells(i,4).value then
Cells(i,5).value = Combobox1.Text
End if
Next

This works fine as long as I select different dealers for each click event. But when I select the same dealer consecutively twice the click event is not triggered and the name of the dealer is not printed in the second instance.

To make the point clear say for Invoice 1233 I have selected the dealer X and the name X gets printed against invoice 1233.The next invoice 1244 also belongs to dealer X and after entering 1244 in Cells(1,1) if I select the same delaer X, the name X doesnot get printed against invoice 1244.

Please advise me resolve the problem

Upvotes: 1

Views: 17903

Answers (1)

jdh
jdh

Reputation: 1655

The click event isn't being triggered because the control hasn't detected a change. You can reset the control by changing its display value either by adding this code at the end of the click event:

ComboBox1.Text = "Select Dealer"

Or change the combobox display text only when you move away from the control, by adding this event:

Private Sub ComboBox1_LostFocus()
  ComboBox1.Text = "Select Dealer"
End Sub

Upvotes: 2

Related Questions