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]

lol that is what I am attempting to do with the above code... If I take out the Sheet2.Range("A:E").Clear the list isn't created correctly. Two lines are added every time. So recreating my list on subform initialize is the only way to go - that i can see.

 

[Norie]

What if you had an existing list?

Perhaps it's just me but it doesn't seem right to be running that code whenever you open the userform.

Why not just add any new contacts to the list as they are added in the log worksheet?

Then when you want to populate the userform you just use the existing list which has the new contacts.

Adding new contacts could be done automatically using code, perhaps when they're entered on the logsheet or even via the userform.

 

[rguy84]

How would I build that list? I don't like running the code everytime either. I Ccould thriw part in the workbook_open event, but not sure how to correctly add/sort per new person. I would imagine that would be like removing the .clear from the sub, but whatever it is doing, the names aren't staying correct.

Adding new contacts could be done automatically using code, perhaps when they're entered on the logsheet or even via the userform

Hints on how?

 

[Norie]

Is that code working at all?

I was going to suggest using it once to get your initial list and then stealing the sort part from it for your own purposes.

For adding the contacts I can think of 2 ways:

1 A worksheet change event triggered when the user types in a contact.

The code in it would check if that contact was in the list and if it isn't ask the user if they want to add it.

2 Via the userform, the user enters a name and if it's not on the list again they get prompted about adding it.

How either would work really depends on how things are setup and/or how you would want them to work.

If it was via the userform it would be kind of straightforward as it's easy to check if the user enters something not on the list.

Any of this making sense?

 

[rguy84]

>Is the code working at all?
Kinda. I noticed if the who (col 1 who on the team) is different, the name shows twice - even though the unique values should only be get from D & E. Sometimes the sort doesn't seem to work. If you run the macro, fix an entry or add a new one, it inputs the data, then two blank lines then the updates. run the macro again, fixes it. Run the macro on subform initialization grabs activeSheet.Range("A1:B2") for the listbox values, vs sheet2.range("A2:B[last row]").

I like #2, but prompt wouldn't be needed. The point of the list is to save a few minutes while entering in people's info. I knowI would use it if i know John Smith was already a contact.

 

[Norie]

You could try making the initial list yourself.

1 Create a new sheet.

2 Copy the contact columns(s) to this, if they don't have headers add some.

3 Select the columns then goto Data>Remove Duplicates.

4 That should be it.

By the way, I'm assuming you are using Excel 2010 or 2007.

If you aren't you can do the above using Data>Advanced Filter..., selecting the Unique Items Only option.

That should be a start.

I actually kind of started something for this but didn't get round to the adding new contacts bit.

I can post what I've got so far but it'd probably make more sense to post the lot, which hopefully I'll
get round to doing sometime.

 

[rguy84]

Hey Norie, Sorry for the delay, I tried making a macro doing what you suggested, then attempted to make it dynamic, and ended up imploding. After about 2 hours of playing "fix the data" - I called it a night.

Doing the steps you pointed out worked fine- but it only liked to be done once via macro. I am using 2010.

 

[Norie]

What exactly where you trying to do and how did it 'implode'?

Like I said I did start something but like you called it a night.

I can post it now but it's still at the stage it was last night and I'm not likely to get much chance of doing anything with it now.

 

[rguy84]

Oh even though I said use relative references it used activesheet.x/y/z (various methods). So when I saw like activesheet.next (or whatever) 2 times, I did sheet("Name").activate thinking that defining the actual sheet would be better. Something didn't take, so it took rows A1:E40ish on my log sheet, sorted, removed dupes from log sheet D1:H77. oooooooooooopsie

 

[Norie]

If you are just trying to get the initial list why not do it manually?

It's probably going to be a one off anyway.

PS You shouldn't ever need to use Activate/Select etc, and they can just cause problems.

Here's the file I was working on, like I said I didn't really do much on it but perhaps it'll give you an idea of what I've been rambling about.

http://www.box.net/shared/furub2fls8u5ufurhd0f

Just double click on column B on the Log worksheet.