PDA

View Full Version : VLookup inside a UserForm



sonicflash
11-16-2011, 08:27 PM
This problem has been frustrating me for a while. :banghead: I am very new at this and everything I have learned is through google searches and trial & error and up to this point I've been able to accomplish everything I've needed to.

I'm creating an attendance record for my mom to use at the school she teaches.

As it is I'm using VLookUp within the cells of the spreadsheet to call up a first and last name from a second sheet using an ID number that is input on the first sheet.

What I want to do is have the user put the ID number in a user form (which I already have) and have the functions of the form call the first and last name from the second sheet.

I'm trying to limit the amount of code in the spreadsheet that the user could potentially over write.

Any help would be usefull.

This is the code i'm trying to move to the user form:

=VLOOKUP($B10,Database!$A$1:$D$5000,2,FALSE)

mgm05267
11-16-2011, 08:45 PM
Hi Sonicflash,

I believe that the data (ID Number, First & Last Name) are in a spreadsheet & you want to know the First & Last Name based on the ID Number.

If that is the case, in VBA, if you are selecting the User ID as the input, & First & Last Name as the output, then....

In the userform, add a combobox by name ID, Textboxes LName1 & FName1.

Load the ID with the existing ID numbers..

Add this code in the userform...

Private Sub ID_Change()
Dim b As Integer
b = ID.ListIndex + 1

For b = 1 To ID.ListCount
FName1.Text = Sheet1.Cells(ID.ListIndex + 3, 3)
LName1.Text = Sheet1.Cells(ID.ListIndex + 4, 3)
Next b

End Sub

In the above code, if you select the ID, first name & last name will be displayed in the respective FName1 & LName1 textboxes.

Please let me know if you have any queries...

Regards,

MGM

sonicflash
11-16-2011, 09:26 PM
Sorry for the elementry-ness of this question, but how do I load the combobox?

mgm05267
11-16-2011, 10:48 PM
Hi Sonicflash...

In the Index sheet (where ID, LName & FName are saved), name the Range of ID as Table1 (using format as table).

if you want to change the names, change in both sheet & code.

In the user form, insert combobox & name it as ID..

in code of the form, add this....



Private Sub UserForm_Initialize()


Dim ws As Worksheet
Set ws = Worksheets("Index")


For Each cPart In ws.Range("Table1")
With
Me.ID
.AddItem cPart.Value

.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next
cPart


End Sub


The above code helps you update the combobox when there are addition or deletion of data in the ID column...

Hope this wil help..

Regards,

MGM

mikerickson
11-16-2011, 11:04 PM
If you load the combobox with all the data, no VlookUp is needed.

Private Sub UserForm_Initialize()
With ComboBox1
.ColumnCount = 4
.ColumnWidths = ";0;0;0"
.TextColumn = 1
.BoundColumn = 2
.MatchEntry = fmMatchEntryComplete

.List = Sheet1.Range("A1:D5000").Value
End With
End Sub
Private Sub ComboBox1_Change()
Rem put associated values into labels

With ComboBox1
If .ListIndex <> -1 Then
Label1.Caption = .Text: Rem value from column A
Label2.Caption = .Value: Rem value from column B
Label3.Caption = .List(.ListIndex, 2): Rem value from column C
End If
End With
End Sub

rem more code

sonicflash
11-16-2011, 11:28 PM
Can I get some clarification of what needs to be changed in that code to match the names of my labels and such in my spreadsheet?

Sorry for being difficult.

mgm05267
11-16-2011, 11:43 PM
Hi Sonicflash,

Example is here for you...

mikerickson
11-16-2011, 11:50 PM
If sonicflash could please attach a sample workbook, we would know where the data is located.

sonicflash
11-17-2011, 01:33 AM
The form I'm working with is under the 'Main Menu' Button then 'Check A Student In.'

I'm eventually going to have the First and Last names show into the respective cells in the CM Log sheet but for now I'm just trying to get them into the labels directly below the ID Number in the fCheckIn form.

The Names are in the table on the 'Database' sheet.

I've tried to implement the code in your worksheet. I guess I'm not understanding the code.

Thanks again for your help.

6912

mgm05267
11-17-2011, 02:21 AM
Hi....

Hope this will help...

you can proceed now...

Regards,

MGM

mikerickson
11-17-2011, 08:07 AM
Try this with the added control cNumber.

Private Sub UserForm_Initialize()
Dim dataCells As Range
With ThisWorkbook.Sheets("Database").Range("A:A")
Set dataCells = .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
Set dataCells = Range(.Cells(2, 3), dataCells)
End With

With Me.cNumber
.ColumnCount = 3
.ColumnWidths = ";0;0"
.TextColumn = 1
.BoundColumn = 2
.List = dataCells.Value
End With
End Sub

Private Sub cNumber_Change()
With Me.cNumber
If .ListIndex <> -1 Then
Me.lLastName = .Value
Me.lFirstName = .List(.ListIndex, 2)
End If
End With
End Sub

Private Sub cNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
With Me.cNumber
For i = 0 To .ListCount - 1
If .List(i, 0) = .Text Then
.ListIndex = i
Exit For
End If
Next i
End With
End Sub

sonicflash
11-17-2011, 03:44 PM
Thanks guys. That's exactly what I needed!