Populating data on userform
 

Hello All,

I am sure that you, guys, would know the answer to my question.

As I attached a sample of my database underneath, I would like t
populate the information belonging to each product ID when I click o
commandbutton on the userform.

So far I tried to run several programme codes with searching function
but I cannot make it correct....

The programme should look like as below
Dim a(20) As Variant b(20) Variant
Dim x As Integer, Column As Integer
x=1: column=74

Do Until IsEmpty(Sheets("Sheet1").Cells(2,Column))
If Sheets("Sheet1").Cells(1, Column).Value=txtcode.value Then
a(x)=Sheets("Sheet1").Cells(3, Column)
b(x)=Sheets("Sheet1").Cells(4, Column)
.....
x=x+1
End If

column=column+1

Loop

Dim c As Object, d As Object
For x=1 To 20
Set c=Userform1.Controls("txtco0" & x)
Set d = Userform1.Controls("txtdefinition0" & x)
c.Value=a(x)
d.Value=b(x)
Next x

Once it reach the empty cell, it should look for the next component.

I am sure that I am very close to the solution, but I am already blin
and tired....

I need to breathe. Please HELP!!!

:confused: :(

Thanks for your time,

Looking forward to hear you soon,

Gk

Attachment filename: question.gif
Download attachment: http://www.excelforum.com/attachment.php?postid=62769
--
Message posted from http://www.ExcelForum.com

Populating data on userform
 

I assumed that "txtcode.value" was a reference to the
value of a named range "txtcode". Be advised that your
attachment was essentially illegible. From looking at it,
I was suspicious that you might be wanting to row offset
instead of column offset. I didn't really benefit from
seeing it.

There's a good chance I misunderstood something but
hopefully this will be helpful. Paste to the UF code
module.

Private Sub CommandButton1_Click()
Dim x As Integer, Col As Integer
Dim WS As Worksheet

x = 1: Col = 74
Set WS = Sheets("Sheet1")
Do Until Trim(WS.Cells(2, Col)) = ""
If WS.Cells(1, Col) = Range("txtcode") Then
Controls("txtco0" & x) = WS.Cells(3, Col)
Controls("txtdefinition0" & x) = WS.Cells(4, Col)
x = x + 1
End If
Col = Col + 1
Loop
End Sub

Regards,
Greg


-----Original Message-----
Hello All,

I am sure that you, guys, would know the answer to my
question.

As I attached a sample of my database underneath, I would
like to
populate the information belonging to each product ID
when I click on
commandbutton on the userform.

So far I tried to run several programme codes with
searching function,
but I cannot make it correct....

The programme should look like as below
Dim a(20) As Variant b(20) Variant
Dim x As Integer, Column As Integer
x=1: column=74

Do Until IsEmpty(Sheets("Sheet1").Cells(2,Column))
If Sheets("Sheet1").Cells(1, Column).Value=txtcode.value
Then
a(x)=Sheets("Sheet1").Cells(3, Column)
b(x)=Sheets("Sheet1").Cells(4, Column)
.....
x=x+1
End If

column=column+1

Loop

Dim c As Object, d As Object
For x=1 To 20
Set c=Userform1.Controls("txtco0" & x)
Set d = Userform1.Controls("txtdefinition0" & x)
c.Value=a(x)
d.Value=b(x)
Next x

Once it reach the empty cell, it should look for the next
component.

I am sure that I am very close to the solution, but I am
already blind
and tired....

I need to breathe. Please HELP!!!

:confused: :(

Thanks for your time,

Looking forward to hear you soon,

Gki

Attachment filename:
question.gif
Download attachment:
http://www.excelforum.com/attachment.php?postid=627693
---
Message posted from http://www.ExcelForum.com/

.

Populating data on userform
 

Sorry Greg,

Didnt work.. :( I still dont see the point why it doesnt make i
work...

Thanks for your reply anyway,

Gk

--
Message posted from http://www.ExcelForum.com

Populating data on userform
 

1) What is the txtcode.value? Is this the value of a
userform control named "txtcode" or a named range?
2) Do you in fact have controls on the userform
named "txtco01", "txtco02" etc. as well
as "txtdefinition01", "txtdefinition02" etc. ?
3) Is it your intention to loop horizontally? Your
attachment gave me the impression you might be wanting to
loop vertically. However, it wasn't legible so it was of
little benefit.
4) What doesn't work and/or what error message do you get?

Note: Both your code and mine worked for me in a simple
test based on my interpretation. Mine is only a
simplification of yours with the exception that I assumed
that "txtcode" was a named range. Therefore, I used the
syntax Range("txtcode").Value instead.

I suspect that the problem is that your control names are
incorrect or that the ranges don't refer to the proper
cells. If all else fails you can email me the workbook if
you like and I'll have a look.

Regards,
Greg



-----Original Message-----
Sorry Greg,

Didnt work.. :( I still dont see the point why it doesnt
make it
work...

Thanks for your reply anyway,

Gki


---
Message posted from http://www.ExcelForum.com/

.

Populating data on userform
 

Hello Greg,

That would be very helpful,

Where can I find your e-mail address?

Thanks and best regards,

gk

--
Message posted from http://www.ExcelForum.com

Populating data on userform
 

Regards,
Greg


-----Original Message-----
Hello Greg,

That would be very helpful,

Where can I find your e-mail address?

Thanks and best regards,

gki


---
Message posted from http://www.ExcelForum.com/

.

Populating data on userform
 

As far as I could tell from your email this is what you
were wanting. There was some guessing so it might be wrong.

Instead of having the information separated amongst the
different textboxes you might be better off just using one
large textbox and constructing a complex string (using
vbLf to create line change). You would thus read the info
like a report in this large textbox. Alternatively, use
labels perhaps. Just throwing out some ideas.

Note that, for my tests, I added more textboxes to the UF
so that there was a total of 18 "txtdes" type
(i.e. "txtdes1" to "txtdex18") corresponding with the 18
Attribute cells. You should do this to test the code. Hope
my interpretation was correct.

Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim C As Range, CC As Range
Dim i As Integer, ii As Integer
Dim x As Integer

On Error Resume Next
Set WS = Sheets("Blad1")
Set C = WS.Range("A:A").Find(txtcode.Value)
i = 0: ii = 0: x = 0
For i = 2 To 18 Step 8
For ii = 1 To 2
Set CC = WS.Cells(C.Row, i + ii)
Controls("TextBox" & x * 2 + ii) = CC
Next ii
For ii = 3 To 8
Set CC = WS.Cells(C.Row, i + ii)
If Trim(CC) < "" Then
x = x + 1
Controls("txtdes" & x) = CC
End If
Next ii
Next i
On Error GoTo 0
End Sub

Regards,
Greg


-----Original Message-----
Hello Greg,

That would be very helpful,

Where can I find your e-mail address?

Thanks and best regards,

gki


---
Message posted from http://www.ExcelForum.com/

.