Worksheet data to userform Textboxes

Kris75

Board Regular
Joined
Jul 29, 2009
Messages
143
Hi I've tried several ways to explain my current problem, but so far i haven't been able to resolve it.

i have a worksheet containing rows of data. i would like a userform to be able to retrieve this data row by row.

<TABLE style="WIDTH: 477pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=636 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" span=3 width=108><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 68.25pt; mso-height-source: userset" height=91><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 98pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 68.25pt; BACKGROUND-COLOR: transparent" width=131 height=91>a</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=108>b</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=108>c</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=108>d</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=74>e</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=107>f</TD></TR><TR style="HEIGHT: 33pt; mso-height-source: userset" height=44><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 33pt; BACKGROUND-COLOR: #ffff99" width=131 height=44 x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 81pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=108>Date</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 81pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=108>Time</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 81pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=108>Account Number</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=74>Agent</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 80pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=107>Customer Name</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Wed, 21 Jul 2010</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0.4513888888888889">10:50</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>123</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">ky6141</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">customer</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>3</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Wed, 21 Jul 2011</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0.49305555555555602">11:50</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>456</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">ky6142</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">customer</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Wed, 21 Jul 2012</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0.53472222222222199">12:50</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>789</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">ky6143</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">customer</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Thu, 22 Jul 2010</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0.4777777777777778">11:28</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>1231</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">ky6141</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">bob</TD></TR></TBODY></TABLE>

i am using a combobox to retrieve the data in column A

Private Sub UserForm_Initialize()
UserForm1.Hide

Dim Esc As Range
Dim WS As Worksheet
Set WS = Worksheets("Stage1")

For Each Esc In WS.Range("Logged_Escalations")
With Me.ComboBox1
.AddItem Esc.Value
.List(.ListCount - 1, 1) = Esc.Offset(0, 1).Value
End With
Next Esc

End Sub

i want the other textboxes on my userform to retrieve the corresponding data in column B:F
so if Combobox 1 = 5
textbox1= Thu, 22 Jul 2010
textbox2=11:28
etc...


PLEASE HELP ME
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
I'm not sure if column A is in the list box, but this does not put it there.

Code:
Private Sub ListBox1_Click()
    With ComboBox1
        If .ListIndex <> -1 Then
            TextBox1.Text = .List(.ListIndex, 0)
            TextBox2.Text = .List(.ListIndex, 1)
            TextBox3.Text = .List(.ListIndex, 2)
            TextBox4.Text = .List(.ListIndex, 3)
            TextBox5.Text = .List(.ListIndex, 4)
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    With ComboBox1
        .ColumnCount = 5
        
        For Each oneCell In Range("B2:B5")
            .AddItem CStr(oneCell.Value)
            .List(.ListCount - 1, 1) = Format(Val(CStr(oneCell.Offset(0, 1))), "hh:mm")
            .List(.ListCount - 1, 2) = CStr(oneCell.Offset(0, 2))
            .List(.ListCount - 1, 3) = CStr(oneCell.Offset(0, 3))
            .List(.ListCount - 1, 4) = CStr(oneCell.Offset(0, 4))
        Next oneCell
    End With
End Sub
 
Upvote 0
THANK YOU SO MUCH, with a few ammendments that works really well

it doesn't seem to like double figures though
textbox9.Text = .List(.ListIndex, 9) ok
textbox10.Text = .List(.ListIndex, 10) !
textbox11.Text = .List(.ListIndex, 11) !

what can i do about this
 
Upvote 0
Did you fill those columns of the ComboBox? Did you specify the .ColumnCount property of the box?

One oddity of ListBoxes and ComboBoxes is that, even if their .ColumnCount property is less than 10, the underlying .List is a 2D array with 10 columns (indices 0-9).
I can't test right now, but I seem to remember that in a ListBox with .ColumnCount = 2 that .List(i, 8) will not error but rather return "" (even though 8 > 1), while .List(i, 10) will error.

If you are using more than 10 columns, you have to be careful to specify the column count and fill each item of the .List specifically.

Also, I forgot to mention that which columns are visible in the drop down can be controlled by setting some of the .ColumnWidths to 0.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top