PDA

View Full Version : Help with coding



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

Bob Phillips
03-05-2012, 11:08 AM
It depends upon how you load the combobox.

scriptman
03-05-2012, 11:30 AM
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
03-05-2012, 12:21 PM
Then you need to add the new value to that named range, and ensure the name includes the new value.

scriptman
03-05-2012, 12:58 PM
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
03-05-2012, 01:11 PM
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
03-05-2012, 01:42 PM
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
03-05-2012, 02:37 PM
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
03-05-2012, 02:55 PM
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
03-05-2012, 04:45 PM
Here is an example of how to do it for the Surname




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


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
03-06-2012, 12:13 AM
Thank you so much for your time.xld. You truly are a wizard with the codes.

Best regards.

Brian

scriptman
03-06-2012, 01:02 AM
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
03-06-2012, 03:04 AM
Did you change the range names to dynamic names as well?