Sub-form lookup

R

rguy84

Board Regular
Joined
May 18, 2011
Messages
112
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
So I have this working - kind of. When I run the sub from the VBA Editor, it works. When I add a button to my main form, to launch the subform it doesn't work at all. It grabs A1 and B1 of the sheet i was on last. This code was mainly taken from: http://www.ozgrid.com/VBA/UserForms.htm

Code: 
Sub SortAndRemoveDupes()
    
    Dim lastCell As Long, lastContact As Long
    Dim rListSort As Range, rOldList As Range
    Dim strRowSource As Range
    
    lastCell = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    lastContact = Sheet1.Range("H" & Rows.Count).End(xlUp).Row
    
    Sheet2.Range("A:E").Clear
    Set rOldList = Sheet1.Range("D1:H" & lastContact)
    
    'copy unique list to sheet2
    rOldList.AdvancedFilter Action:=xlFilterCopy, _
               CopyToRange:=Sheet2.Cells(1, 1), unique:=True
    
    With Sheet2.Sort
        .SetRange Range("A1:E" & lastCell)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   'Check & remove dupes
    Sheet2.Range("A1:E" & lastCell).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _
        Header:=xlYes
    
    'Parse the address of the sorted unique items
     
    With frmUserLookup.lbox
        .RowSource = Sheet2.Range("A2:B" & lastCell).Address
    End With
End Sub
 
Upvote 0
Little confused here, that code appears to be for removing duplicates.

There isn't a userform mention anywhere in it, and your first post wasn't about duplicates.:)

If the problem is the data is getting grabbed from the wrong worksheet it could be because the code for that doesn't have any worksheet references.

Without references the data will come from the active sheet, which could be the last you were on.
 
Upvote 0
Yeah Norie, I needed to filter the data prior to inserting it into a listboox. The last few lines parse the data. I cut the frmUserlookup.show from the sub because the form is launched already via button click off my main form.

f the problem is the data is getting grabbed from the wrong worksheet it could be because the code for that doesn't have any worksheet references.
Click to expand...
aren't the various Sheet1.Range(...) references?
 
Upvote 0
In that code they are, but I though it was grabbing them for the userform where the problem was.

What are you trying to grab?

What data do you actually have?

Can you give more details about the userform, or is that not the problem?
 
Upvote 0
Here we go:
To be frank this project project should really be an Access DB, but due to our network, i have to use Excel. The workbook is a contact log for the team (3 people). the columns go Team member, date, last name, first, division, email, phone, summary, method, starttime,end time. I could talk to somebody 5 times a day, so instead of typing last/first, division, e-mail 5 times, i want to have another form pop up with past contacts. The past contacts form will just have the first and last name, after selection and button click the main form is populated with their info. Their info is gathered from the contact sheet (sheet1).
 
Upvote 0
Why not set up the contacts part like a sort of database using a separate sheet as a 'table'?

In fact the code you've posted is sort of doing that but it seems to recreate the worksheet every time it's run.


You would still have the main log sheet but you'd grab the contact detail from the other worksheet.

You could even add/edit/delete contacts either manually or via the userform.
 
Upvote 0
I cannot pre-define the contacts because the team can talk to (answer questions from) anybody in the company basically. We have abouut 20,000 employees.
 
Upvote 0
Who said anything about predifining the contacts?

I meant you create a list of contacts from the current data, store that on a worksheet, then use that in for your userform.

There's nothing stopping a contact not on the list being entered.

You could even have new contacts added to the list of contacts so they are available the next time the form is opened.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Creating a 'Reset' button for a text form, plus additional buttons
Replies
2
Views
291
WEB78
W
D
  • Question Question
VBA Basic Userform - add -update -delete
Replies
3
Views
681
Drexi
D
I
  • Question Question
Use the majority of an existing working code in new project advice
Replies
1
Views
215
ipbr21054
I
S
  • Question Question
Macro to add sum total to track percentages
Replies
0
Views
337
spenserallen7
S
M
  • Question Question
Seeking alternate/better solution for excel XLAM addin, currently using form/multipages
Replies
1
Views
628
Exceljeanie
E

Forum statistics

Threads
1,224,600
Messages
6,179,834
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top