Connecting to Database on Worksheet_Change Event

[Nerisha]

Hi there.

I have an Excel Worksheet that I have included a Data Validation List to. The list is populated with items for sale. What I would like, is to have the user select any item from the list, and the cost of the item should be updated accordingly. No, I read somewhere that I can use the Worksheet_change event to do this, but When I put in my code below, I get the following error: Compile error: User-defined type not defined on the following line:
Dim cn as ADODB.Connection

Any ideas on how to do this?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim Item As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set cn = ozConnection()
Set rs = New ADODB.Recordset
Range("B22") = Item

sql = "Select Amount from Items WHERE ItemDescr = " & Item
rs.Open sql, cn

If Not rs.EOF Then
Range("CostItem") = rs!amount
End If

End If

 

[Richard Schollar]

I am using Excel 2007. The list is populated with a range of data located on the same sheet.

Are you up to date with the latest service pack and/or patches?

 

[Nerisha]

i have the latest service pack and patches installed already. I managed to get the connection to the database working. Now for the problem with the listbox. If I put the code behind the worksheet_change event, it fires every time the user changes anything on the sheet. It does not fire when the user selects a different item on the listbox. This is a data validation listbox that I am using. It gets populated from data listed on the same worksheet.

 

[Norie]

Nerisha

Do you have to use the data validation for the dropdown and the worksheet change event for whatever?

Could you not use an ActiveX or Forms listbox/combobox?

They both have their own events associated with them, eg Click, Change etc.

Oh, almost forgot - if you are going to cross-post add a link.

 

[Nerisha]

I can use an ActiveX listbox. I just don't know how to. I tried adding one on, but this will have to done via code because the sheet gets created via code. I only tried the data validation listbox, because I thought it would be easier.

 

[Norie]

Is the entire sheet being created by code, including the code for the change event?

 

[Nerisha]

Yes, the entire sheet will be created by code. The change event will also have to be created via code.

 

[Norie]

Can I ask why you need/want to do that?

Have you actually managed to create a worksheet with the code for the worksheet event?

 

[Andrew Poulsom]

My problem is not the query. That I can fix. The problem is this. I have the code behind the worksheet_change event, but it only fires when I click on a different cell than the one currently selected. If I change the value in the listbox, nothing happens. How can I make it such that I have a listbox populated with items for sale, and when the user changes the item on the list, the cost of the item is updated.

It sounds like your code is in the Worksheet_SelectionChange procedure rather than in the Worksheet_Change procedure.