auto populate with cell change event?

[RatherBeRidi]

I am populating column D with =IF($C2="","",VLOOKUP($C2,Name_ID,2,FALSE)) based on selection in column C poulated using a drop down list from a range titled PracticeID. Other named ranges, all on sheet titled Validation, are PracticeName and Name_ID (a 2 column range made up of PracticeName and PracticeID). This works great the first time a selection is made in column C. But, of course, if the user needs to change the selection in column C, the value in column D does not change. Would the cell change event be the appropriate place to place this action? Can anyone help me with coding for this? Many thanks!

[Bob Phillips]

You say of course, but it should update even selecting from a DV.

Can you post the workbook?

[RatherBeRidi]

Thanks for the reply. I will have to modify the workbook and can post it. First, I may need to clarify a bit. The IF statement is in the cell of column D, which is replaced when a selection is made from column C. If I go back to the the same cell in column C and choose another name, the content of the cell in column D does not change because the formula is no longer there.
For Example:
Column C........Column D
NameA...........ID1
NameB...........ID2 (in same row as above)

Hope this make more sense.
Again, Thanks!

[Bob Phillips]

Why, how, is it replaced when a change is made in column C? Sounds as if you maybe have some event code messing it up.

[RatherBeRidi]

The file is attached. In case you need more information about it's purpose, you will see a data entry form for the user to enter new records. This will be submitted monthly. I will have sheet protection and locked cells in place when this is distributed. The problem occurs when a change to existing data is required. If they need to select a different Practice Name, the Practice ID must also change. Thanks for your help.

[Bob Phillips]

There is no named range for NAME_ID. IF you define this to validation!C:D, and then change this procedure on Userform1 as indicated

[VBA]Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim txt As TextBox
Dim cbo As ComboBox
Dim ws As Worksheet
Set ws = Worksheets("Report")

'revised code to avoid problems with
'Excel lists and tables in newer versions
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cboPracticeName.ListIndex

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtCHOID.Value
.Cells(lRow, 2).Value = Me.cboReportPeriod.Value
.Cells(lRow, 3).Value = Me.cboPracticeName.Value
.Cells(lRow, 4).Value = Me.cboPracticeName.List(lPart, 1) '<<<<<<<<<<< remove this line
.Cells(lRow, 5).Value = Me.txtCareManagerName.Value
.Cells(lRow, 6).Value = Me.txtFTE.Value
.Cells(lRow, 7).Value = Me.cboCareManagerRole.Value
.Cells(lRow, 8).Value = Me.cboPatientPopulation.Value
.Cells(lRow, 9).Value = Me.txtDateBegan.Value
.Cells(lRow, 10).Value = Me.txtDateTraining.Value
.Cells(lRow, 11).Value = Me.txtClinicName.Value
.Cells(lRow, 12).Value = Me.txtCareManagerPhone.Value
.Cells(lRow, 13).Value = Me.txtCareManagerEmail.Value
.Cells(lRow, 14).Value = Me.txtReportsTo.Value
.Cells(lRow, 15).Value = Me.txtSupervisorEmail.Value
.Cells(lRow, 16).Value = Me.txtSupervisorPhone.Value
End With

'clear the data
Me.txtCHOID.Value = ""
Me.cboReportPeriod.Value = ""
Me.cboPracticeName.Value = ""
Me.txtCareManagerName.Value = ""
Me.txtFTE.Value = ""
Me.cboCareManagerRole.Value = ""
Me.cboPatientPopulation.Value = ""
Me.txtDateBegan.Value = ""
Me.txtDateTraining.Value = ""
Me.txtClinicName.Value = ""
Me.txtCareManagerPhone.Value = ""
Me.txtCareManagerEmail.Value = ""
Me.txtReportsTo.Value = ""
Me.txtSupervisorEmail.Value = ""
Me.txtSupervisorPhone.Value = ""

Me.txtCHOID.SetFocus
End Sub
[/VBA]

[RatherBeRidi]

Thanks ever so much! I was making it much more complicated than needed. I thought I had to populate all fields from the user form. I have the Name_ID range in my copy, but maybe forgot to add it to the copy I posted.

Now that you see what I am attempting to do with the database, I hope I can ask for a little more advice.

We want the users to enter information with the data entry form and only access the Report worksheet if they need to make edits to the existing data. Is it possible to lock all rows that do not have data, thus preventing the user from starting a new record in the worksheet? And, is it possible to require completion of some fields in the data entry form before submitting? I am more familiar with Access where this can be set with a field property. I could not find a similar property for the text box or combo box.

Your help is appreciated!

[Bob Phillips]

No you cannot set any property, you would have an add/update button and check the fields from within the button event.