Populating data on userform

[gki]

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!!!



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

 

[Greg Wilson]

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

 

[gki]

Sorry Greg,

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

Thanks for your reply anyway,

Gk

 

[Greg Wilson]

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

 

[gki]

Hello Greg,

That would be very helpful,

Where can I find your e-mail address?

Thanks and best regards,

gk

 

[Greg Wilson]

(e-mail address removed)

Regards,
Greg

 

[Greg Wilson]

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