Tranfer data from worksheet to Userfrm

[Emperor]

Hi all,

I've got a little challenge, at least for me.

Example; Data starting in G3:
<TABLE style="WIDTH: 304pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=404 x:str><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" span=4 width=101><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 76pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=17 width=101 align=right x:num="0">00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=101 align=right x:num="2">02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=101 align=right x:num>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=101 align=right x:num>13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 align=right x:num="12098.836368303919">€ 12.099 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 align=right x:num="2053.6594660487945">€ 2.054 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 align=right x:num="2796.3092571388406">€ 2.796 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 align=right x:num="289.81285289091988">€ 290 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17 align=right x:num="96.790690946431354">€ 96,79 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num="16.429275728390355">€ 16,43 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num="22.370474057110727">€ 22,37 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num="2.3185028231273592">€ 2,32 </TD></TR></TBODY></TABLE>

Total number of columns is variabel (up to 40 colums), there allways 3 rows.

What I need is a way to display this information on a Userform. When possible in a way so they can be selected to copy (3rd item of each column, the €96.76 etc)

I have been think of a multirow listbox, but this isnt the solution for the copy thing. (or is it? maybe with a dblclick event? I see potential but don't know how!?)

What I was thinking about is a Userform with a row of textboxes with a label in front with the 00, 02 etc.

How could the following be achieved?

"00 (information from Vlookup)" €96,79
"02 (information from Vlookup)" €16,43
"11 (information from Vlookup)" €22,37

Vlookup = worksheets("data").range("A24:B56"), 2

Any help is welcome!

Mathijs.

 

[Trevor G]

Which version of Excel are you using, as 2003 and lower will give you a Data Form from the Data Menu.

 

[Emperor]

Hi Trevor,

I'm using Excel 2003.
But I think your going the wrong way? I have a Userform is which the initial listbox is situated. The data to complete the second listbox is in Different worksheets.

So:
- Userform1, Listbox4 has the selection with 00, 01, 02 etc, This Listbox manipulates a Pivottable at worksheet PivotTable. Above the pivot table I have some calculations for averages etc. (See the above example from cell G3, etc).
- Userform2, has a Listbox1, in this listbox needs to be the new data, partially from worksheet "pivottable" and partially from worksheet "data".

So how could the data be pasted together to get 1 clear Listbox? Or another way with the same result. (all controlled by VBA and userforms)

Mathijs

 

[Emperor]

Hi Trevor another idea / question.

If I just edit the data is G3 so it allready has the information needed, it only has to transposed and filled into a listbox.

So this:
<TABLE style="WIDTH: 304pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=404 x:str><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" span=4 width=101><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 76pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=17 width=101>0 Text1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=101>2 Text1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=101>11 Text2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=101>13 Text3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 align=right x:num="12098.836368303919">€ 12.099 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 align=right x:num="2053.6594660487945">€ 2.054 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 align=right x:num="2796.3092571388406">€ 2.796 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 align=right x:num="289.81285289091988">€ 290 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17 align=right x:num="96.790690946431354">€ 96,79 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num="16.429275728390355">€ 16,43 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num="22.370474057110727">€ 22,37 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num="2.3185028231273592">€ 2,32 </TD></TR></TBODY></TABLE>

Needs to become, per row in a listbox;
0 Text1 €96,79
2 Text1 €16,43
11 Text2 €22,37

How could I achieve this?

Mathijs.

 

[berenger]

I think what you are looking for is something that is similar to this code.

Private Sub Text1_Change()
If Len(Text1.Text) = 8 Then
On Error Resume Next
x = Application.WorksheetFunction. _
VLookup(CDbl(Text1.Text), Worksheets("Sheet1").Range("G3:X500"), 2, False)
If Err.Number = 0 Then
Description = x
Else
Description = "No such product found"
End If
On Error GoTo 0
End If
End Sub

You can change Description to match you text box that you want it to go into. At the range I started at G3 but I didn't know the entire range of your spreadsheet so you can change that.

This should take a value you enter into Text1 and then return a value in say Text3 if that is where the Description will go.