List index help

Kris75

Board Regular
Joined
Jul 29, 2009
Messages
143
I have two userforms one for admin assistants to input information onto a worksheet. The second userform (used by supervisors) is populated by information on the same worksheet using this code, they then complete additional text boxes which then save the information on a second worksheet.

.. Is there anyway that when the supervisor reaches the last populated row (which would populate Combox1) that userform loops to the first entry or stops before it errors out and crashes?

Run Time error 381
could not get the list property, Invalid Property array index.

Private Sub ComboBox1_Change()
With ComboBox1
If .ListIndex <> 1 Then
textbox1.Text = .List(.ListIndex, 2)
txtTLTime.Text = .List(.ListIndex, 3)
txtTLagent.Text = .List(.ListIndex, 4)
CbxTLAccount.Text = .List(.ListIndex, 5)
CbxTlCustname.Text = .List(.ListIndex, 6)
txtTLtel.Text = .List(.ListIndex, 7)
txtTLservice.Text = .List(.ListIndex, 8)
txtTLsummary.Text = .List(.ListIndex, 9)


End If
End With
End Sub
Private Sub CbxTlCustname_Change()

With CbxTlCustname
If .ListIndex <> 1 Then
txtTLowner.Text = .List(.ListIndex, 2)
txtTLcallback.Text = .List(.ListIndex, 3)

End If
End With
End Sub

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
Dim oneCell As Range
With ComboBox1
.ColumnCount = 11

For Each oneCell In Range("A1:A10000")
.AddItem CStr(oneCell.Value)
.List(.ListCount - 1, 1) = CStr(oneCell.Offset(0, 0))
.List(.ListCount - 1, 2) = CStr(oneCell.Offset(0, 1))
.List(.ListCount - 1, 3) = Format(Val(CStr(oneCell.Offset(0, 2))), "hh:mm") '= CStr(oneCell.Offset(0, 2))
.List(.ListCount - 1, 4) = CStr(oneCell.Offset(0, 3))
.List(.ListCount - 1, 5) = CStr(oneCell.Offset(0, 4))
.List(.ListCount - 1, 6) = CStr(oneCell.Offset(0, 5))
.List(.ListCount - 1, 7) = CStr(oneCell.Offset(0, 6))
.List(.ListCount - 1, 8) = CStr(oneCell.Offset(0, 7))
.List(.ListCount - 1, 9) = CStr(oneCell.Offset(0, 8))
Next oneCell
End With
'====================================================================================
Dim twoCell As Range
With CbxTlCustname
.ColumnCount = 2
For Each twoCell In Range("F1:F10000")
.AddItem CStr(twoCell.Value)

.List(.ListCount - 1, 2) = CStr(twoCell.Offset(0, 4))
.List(.ListCount - 1, 3) = CStr(twoCell.Offset(0, 5))
Next twoCell
End With

End Sub
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

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
Top