Sub-form lookup

[rguy84]

I have been making a call log. Due to the nature of it we have multiple entries per person usually. So typing a person's contact details 5 times gets annoying.

I was thinking of having a button that brings up another form that has every name (first last) of everybody who is currently added. I could click their name, and another button. The button would populate the main form and close the small form.

Ths is somewhat easy to do in access,. but not sure how to start in excel.

 

[rguy84]

My apologies Norie, I thought I replied to this, but ate it I guess.

Well all you need to do is add a button to open the contact 'sub' form and then direct the input to the main form instead of the worksheet.

Yep, the issue was that I couldn't get the sub's source correct.

Though I'm still unsure what you want to happen when an unknown contact is entered.

What I understood from your previous comments was everytime a person who is not on the list is entered into the form, a msgbox pops up asking to be added. I think that might be intrusive, so instead of another box popping up, I would make a check box on the main form, that asks if they want to add the person to the known contacts. If they check the box, they are added to the sheet holding the names. If not nothing happens.

Do the fields in the main form just stay blank?

When? On form submission, the form values are added to the log, then from close/hide.

Or do you still want the user to enter the details for the new contact on the sub for, and transfer them to the main form.

The sub form will ideally be a list box with a button. Clicking that will nab the info from the contacts sheet, add it to the main form then go from there.

 

[rguy84]

Norie- I got the subform working. The only thing that is a bit confusing is why I have to use

 frmAddContact.txtLastName.Value = ws.Range("A" & lbox.ListIndex + 2)

vs +1

 

[Norie]

Ryan

The ListIndex is zero-based, ie item 1 on the list has ListIndex 0, item 2 on the list has ListIndex 1 and so on.

Since the range that populates the listbox begins on row 2 we add 2 to get row 2 for the first item, row 3 for the second item and so on.

 

[rguy84]

I know that it is zero-based, that is why I am trying to get my head around +2. My RowSource starts at A2, so listindex[0]= a1, headers; listindex[1]=a2.

 

[Norie]

How exactly did you populate the listbox?

Have you included the headers as items on the list or as 'real' headers?

You can check that by trying to select the headers.

If you can they are items on the list.

By the way, why not just add labels above the columns in the listbox as headers?

 

[rguy84]

I did

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastR As Long
    
    Set ws = Sheets("PastContacts")
    lastR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    With Me.lbox
        .ColumnCount = 2
        .ColumnHeads = True
        .RowSource = "'" & ws.Name & "'!A2:B" & lastR
    End With
End Sub

 

[Norie]

Ryan

What row does the first item on the list come from?

Isn't it row 2?

What's the index of the first item on the list?

0 perhaps?

 

[rguy84]

I assume it is 0, how do i look

 

[Norie]

The index of the first item on the list in the listbox/combobox is always 0?

The header has no index, because it is no an item on the list.

 

[rguy84]

Thought there was a way to set it differently, no I didnt set one