Populating ActiveX Drop List from Cell Values

[LordDragon]

Greetings,


I'm trying to populate an ActiveX Drop List based on the values of other cells on another sheet.

I have a form that the customer fills out. This will then be entered into a copy of that form in my workbook (until I figure out how to inport it).

The problem I'm working on now though is there is a field called "Contractor" and another called "Operator". Since either one of these could be our "Customer" I have another field on a different page that specifies whom our Customer is. I have put an ActiveX Drop List in this field, which I would like to populate by using the Contractor and Operator fields from the other page.

Currently I am populating the other ActiveX Drop Lists with this code: (or something like it).



With Worksheets("System Selection").EDRBox
.AddItem "No"
.AddItem "Yes"
End With


So I tried duplicating that with this code:



'Sets the options for the drop box for Customer.
With Worksheets("System Selection").CustomerBox
.AddItem rsfContractor
.AddItem rsfOperator
End With


rsfContractor & rsfOperator were declared as Variables for the entire Module and I added a Function to find the values. I think that is where my problem is.



Function GetCustomer()


With ActiveWorkbook.Worksheets("Rig Survey Form")
rsfContractor = Range("D4:I4").Value
rsfOperator = Range("D6:I6").Value
End With

End Function



I'm pretty sure I'm just missing something simple. I would appreciate any help I can get.

[LordDragon]

I got rid of the ActiveX control and put a standard Data Validation List in. I hid the list in a column to the right, far away from what is being used. Then I put in VBA Code to populate the list:




Private Sub Worksheet_SelectionChange(ByVal Target As Range)


With ActiveWorkbook.Worksheets("Rig Survey Form")
.Range("AG7").Value = Range("D4").Value
.Range("AG8").Value = Range("C6").Value
End With


End Sub


Now whenever those cells are changed, the Customer Drop List is updated.

[snb]

You might consider to use this instead (it might be faster).


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6,D4")) Is Nothing Then Range("AG7").Offset(Abs(Target.Row = 6)).Value = Target
End Sub