scriptman
03-05-2012, 05:53 AM
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.
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
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.
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