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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,770
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
 

Kris75

Board Regular
Joined
Jul 29, 2009
Messages
143
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,770
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,524
Messages
5,511,816
Members
408,865
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top