Get details button from a data.

[sethu29]

Can any one help on this.
Hi
i want to create a user form as designed in this attached sheet.
In this attached sheet, two sheets are there.
One is summary
and another one is data.

The data given in data sheet. So what i've to do is , In summary page If I type any login ID and clicking get details ,it has fetch the details from the data sheet and it has to show the details in this mentioned cell.

Can any one help on this... It would be very helpful, and it will less my work...

[sethu29]

Please help me

[Kenneth Hobs]

It is better to not reply to your own thread if no one has responded. Give it a day or two. Most that see 0 responses will look to see if they can help. If no responses, then a reply will bump it to the top.

Looking at your data, not all fields match. While it is easy enough to hard code each field, it is better to be smart and name the Columns the same. Even better would be if your returned data is in the same order but the data is just transposed. This means that the row found is then returned as the column. You can then label the rows different than your data's column names.

[Rob342]

Why not just display the details from the user id selected in the combo box on the form. Why display it on the summary sheet.
Is the data sheet hidden or ?
As Kenneth pointed out you need the data to match.

Rob

[sethu29]

Thanks. RA ID, RA Name, Email, Address, Locality, City State, Pincode, Country ,Login ID ,Active Enrolled date and end Date, serial No, Role type, User type. This are all set of data placed in a row.Now while creating a user form when type any login ID, the all the set of details in that same row has to be displayed in summary sheet as mentioned.
If not possible, Instruct me for another way....

[sethu29]

Assigned macro for user form
If i type afwf12 in that combo box, it has to fetch the same related data in that row and have to display the same in summary sheet at details.

if i typing afwf12 in combobox and then clicking get details,it have to display like below in summary sheet.8074


Details
RA ID RA 002RA Name RaguEmail test12@gmail.comAddress 73Locality BangaloreCity BangaloreState KarnatakaPincode 560016Country indiaLogin ID afwf12Active YesEnrolled date and end Date 02.01.11 to 05.05.12serial No hhjgvRole type hgUser type uu

[Kenneth Hobs]

In your userform code:
Option Explicit

Private Sub UserForm_Initialize()
FillComboBox1
ComboBox1.ListIndex = 0
End Sub

Private Sub CommandButton1_Click()
Dim a() As Variant
Dim r As Range
Set r = Worksheets("Data").Range("J2:J" & Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row)
Set r = r.Find(ComboBox1.Value)
If r Is Nothing Then Exit Sub

Worksheets("Data").Range("A" & r.Row & ":O" & r.Row).Copy
Worksheets("Summary").Range("H4").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub

Private Sub Cancelbutton_Click()
Unload Me
End Sub

Private Sub FillComboBox1()
Dim a() As Variant
Dim r As Range

Set r = Worksheets("Data").Range("J2:J" & Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row)
a = UniqueValues(r)
a = SortArray(a)
ComboBox1.Clear
ComboBox1.List = a
End Sub

In Module1:
Option Explicit

'http://msdn.microsoft.com/en-us/library/aa730921.aspx
'http://www.mrexcel.com/forum/showthread.php?t=329212
Function UniqueValues(theRange As Range) As Variant
Dim colUniques As New VBA.Collection
Dim vArr As Variant
Dim vCell As Variant
Dim vLcell As Variant
Dim oRng As Excel.Range
Dim i As Long
Dim vUnique As Variant
Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
vArr = oRng
On Error Resume Next
For Each vCell In vArr
If vCell <> vLcell Then
If Len(CStr(vCell)) > 0 Then
colUniques.Add vCell, CStr(vCell)
End If
End If
vLcell = vCell
Next vCell
On Error GoTo 0

ReDim vUnique(1 To colUniques.Count)
For i = LBound(vUnique) To UBound(vUnique)
vUnique(i) = colUniques(i)
Next i

UniqueValues = vUnique
End Function


Function SortArray(ByRef MyArray As Variant, Optional Order As Long = xlAscending) As Variant
Dim w As Worksheet
Dim r As Range

Set w = ThisWorkbook.Worksheets.Add()

On Error GoTo D1
Range("A1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = WorksheetFunction.Transpose(MyArray)
Continue:
Set r = w.UsedRange
If Order = xlAscending Then
r.Sort Key1:=r.Cells(1, 1), Order1:=xlAscending
Else
r.Sort Key1:=r.Cells(1, 1), Order1:=xlDescending
End If

SortArray = r

Set r = Nothing
Application.DisplayAlerts = False
w.Delete
Application.DisplayAlerts = True
Set w = Nothing

Exit Function
D1:
Range("A1").Resize(UBound(MyArray, 1), 1) = WorksheetFunction.Transpose(MyArray)
On Error GoTo 0
GoTo Continue
End Function

[sethu29]

HI
Still it is encooutered with Run time error 9
subscript out of range.

What might be the error. How it is coming.

[sethu29]

Kenneth Hobs
Its working fine. Thank you. Please ignore previous post. I changed the 2nd sheet name as partsdata. so that it wasnt working. Now i cleared the encountered error.

Thanks a lot:friends: