Help with coding

[scriptman]

I'd like to point out that I am a novice when it comes down to VBA but I managed to create a workable userform by adapting bits of info from various websites. It works perfect and looks good. I am certain, however, that anyone with a bit of VBA nous would find my coding erratic and tad messy.
Anyway, here is my question. I have several combo-boxes on my form. When I enter the data, the data is sent to a worksheet called FamilyHistory, which is where I want it to go. I also have a worksheet called Data where the combo-boxes take the list choices from. The combo-box lists are not exhaustive so sometimes when the 'name' does not appear on the combo-box drop down list, I have to manually type in the name. What I want to know, is there a code I can add on the userform so the list (on the Data worksheet) will automatically update when I manually type a name in the combo-box because it does not appear on the list?
Here is my code. I apologise if it seems confusing. Any advice on tidying the code up will also be greatly apreciated, just that I dare no delete anything in case the form does not work again. Thanks very much in advance.

[VBA]Private Sub ComboBox1_Change()
End Sub
Private Sub abode_Change()
End Sub
Private Sub abode1_Change()
End Sub
Private Sub clear_Click()
Unload Me
Userform1.Show
End Sub
Private Sub commandbutton_enter_Click()
Dim emptyRow As Long
'Make Sheet1 Active
Sheets(1).Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Export Data to ws worksheet

Cells(emptyRow, 1).Value = cbosurname.Value
Cells(emptyRow, 2).Value = cboforename.Value
Cells(emptyRow, 3).Value = age.Value
Cells(emptyRow, 4).Value = eve.Value
Cells(emptyRow, 5).Value = cboparish.Value
Cells(emptyRow, 6) = Me.year.Value
Cells(emptyRow, 7).Value = cboday.Value
Cells(emptyRow, 8).Value = cbomon.Value
Cells(emptyRow, 9).Value = cboabode.Value
Cells(emptyRow, 10).Value = cbosurname1.Value
Cells(emptyRow, 11).Value = cboforename1.Value
Cells(emptyRow, 12).Value = cboabode1.Value
Cells(emptyRow, 13).Value = cboage_1.Value
Cells(emptyRow, 14) = Me.surname3.Value
Cells(emptyRow, 15).Value = cboforename3.Value
Cells(emptyRow, 16).Value = cbocollection.Value
Cells(emptyRow, 17).Value = cboref.Value
Unload Me
Userform1.Show
End Sub
Private Sub cancelbutton_Click()
Unload Me
End Sub

Private Sub forename1_Change()
End Sub
Private Sub forename3_Change()
End Sub
Private Sub Frame1_Click()
End Sub
Private Sub ScrollBar1_Change()
End Sub
Private Sub Frame3_Click()
End Sub
Private Sub Frame4_Click()
End Sub
Private Sub Frame5_Click()
End Sub
Private Sub Frame6_Click()
End Sub
Private Sub Label11_Click()
End Sub
Private Sub Label17_Click()
End Sub
Private Sub sur_Change()
End Sub
Private Sub ref_no_Change()
End Sub
Private Sub surname_Change()
End Sub
Private Sub surname3_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub year_Change()
End Sub[/VBA]

[Bob Phillips]

It depends upon how you load the combobox.

[scriptman]

By defining a range on the worksheet (named Data) and applying the range name to the combo-box property 'RowSource'.

I hope this makes sense.

[Bob Phillips]

Then you need to add the new value to that named range, and ensure the name includes the new value.

[scriptman]

I understand that, xld, and I thank you for your time. Essentially what I am wanting is for the combobox range to automatically update/populate each time I type a NEW name into the combobox, one which isn't already included in the range. Doing it the way you suggest is very time consuming for me as I have 100s of names and each time I want to add a new unique name in the range I have to close the userform and mess about adding the new name into the range. It would be great if all I had to do was to type the name in the combobox and it automatically stores in the range if or when I needed it again.

[Bob Phillips]

No, I mean you check the value entered in the combobox, in code, and if it is not in the source range, add it to the source name, in code.

[scriptman]

I really am a novice, xld. I am not sure how to do that. It took me two week to get the userform to work how it does.

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Export Data to ws worksheet

Cells(emptyRow, 1).Value = cbosurname.Value

'Would I need to put some kind of code here'?

Cells(emptyRow, 2).Value = cboforename.Value

[Bob Phillips]

Assuming that that is how your data is structured, it looks about right. Try it.

If it fails, post back with details of what happens, maybe your workbook as well.

[scriptman]

This is my workbook attached. The worksheet called 'Data' is where the ranges are held. The worksheet called 'FamilyHistory' is where the entries are produced.

If I'm honest, I wouldn't know how to formulate the code.

[Bob Phillips]

Here is an example of how to do it for the Surname

[vba]


Private Sub cbosurname_AfterUpdate()
Dim rng As Range

Set rng = Worksheets("Data").Range("surname")

With Me.cbosurname

If IsError(Application.Match(.Value, rng, 0)) Then

rng.Cells(1, 1).End(xlDown).Offset(1, 0).Value = .Value

Set rng = Worksheets("Data").Range("surname")
rng.Sort key1:=rng.Cells(1, 1), _
Order1:=xlAscending, _
Header:=xlNo

.RowSource = "surname"
End If
End With
End Sub
[/vba]

I suggest that you change the range names to be dynamic range names, such as changing surname to

=OFFSET(Data!$A$1,1,0,COUNTA(Data!$A:$A)-1)

it will get rid of all of those empty lines and make sure it never runs out.

[scriptman]

Thank you so much for your time.xld. You truly are a wizard with the codes.

Best regards.

Brian

[scriptman]

xld, you are an absolute star. I had every confidence it would work.... I have just tried it and it works a treat. If there's anything I can do for you (as long as it's not coding), please let me know.

Thank you very much again

[Bob Phillips]

Did you change the range names to dynamic names as well?