Need Help Creating a Loop

[jcrump64]

I am trying to avoid having to enter the following code 72 times (once
for each fo the 72 listboxes I have) in the worksheet:

Sheet1.ListBox1.AddItem "Select From List"
Sheet1.ListBox1.AddItem "1 (High Risk)"
Sheet1.ListBox1.AddItem "2"
Sheet1.ListBox1.AddItem "3"
Sheet1.ListBox1.AddItem "4"
Sheet1.ListBox1.AddItem "5 (Low Risk)"
If Sheet1.Range("B5") = "Select From List" Then
Sheet1.ListBox1.ListIndex = 0
End If
If Sheet1.Range("B5") = "1 (High Risk)" Then
Sheet1.ListBox1.ListIndex = 1
End If
If Sheet1.Range("B5") = "2" Then
Sheet1.ListBox1.ListIndex = 2
End If
If Sheet1.Range("B5") = "3" Then
Sheet1.ListBox1.ListIndex = 3
End If
If Sheet1.Range("B5") = "4" Then
Sheet1.ListBox1.ListIndex = 4
End If
If Sheet1.Range("B5") = "5 (Low Risk)" Then
Sheet1.ListBox1.ListIndex = 5
End If

Any help?

Thanks,

Jeff

 

[Dave Peterson]

If your list boxes are numbered nicely: Listbox1, ..., listbox72, you could do
something like:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim iCtr As Long

For iCtr = 1 To 72
Set OLEObj = Sheet1.OLEObjects("Listbox" & iCtr)
With OLEObj.Object
.Clear
.AddItem "Select From List"
.AddItem "1 (High Risk)"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5 (Low Risk)"

Select Case LCase(Sheet1.Range("B5").Value)
Case Is = LCase("Select from List")
.ListIndex = 0
Case Is = LCase("1 (High Risk)")
.ListIndex = 1
Case Is = "2", "3", "4"
.ListIndex = Sheet1.Range("B5").Value
Case Is = LCase("5 (Low Risk)")
.ListIndex = 5
End Select
End With
Next iCtr
End Sub

And you really wanted one cell (B5) to set the listindex for all the boxes???

 

[jcrump64]

Dave:

Thanks. Good question: No, I need the listindex to increment as well;
starting with B5. I guess that will change the code ...

Regards,

Jeff

 

[jcrump64]

Hi Dave:

I know you're busy but I was wondering if you could advise on what the
changes would be to accomodate the changing listindex as well?

Thanks,

Jeff

 

[Dave Peterson]

Do you mean that the cell to check needs to change for each listbox?

B5, then B6, then B7, ..., B76???

If yes:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim iCtr As Long
Dim CellToCheck As Range

Set CellToCheck = Sheet1.Range("b5")

For iCtr = 1 To 72
Set OLEObj = Sheet1.OLEObjects("Listbox" & iCtr)
With OLEObj.Object
.Clear
.AddItem "Select From List"
.AddItem "1 (High Risk)"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5 (Low Risk)"

Select Case LCase(CellToCheck.Value)
Case Is = LCase("Select from List")
.ListIndex = 0
Case Is = LCase("1 (High Risk)")
.ListIndex = 1
Case Is = "2", "3", "4"
.ListIndex = CellToCheck.Value
Case Is = LCase("5 (Low Risk)")
.ListIndex = 5
End Select
End With
'dropdown one row for the next listbox
Set CellToCheck = CellToCheck.Offset(1, 0)
Next iCtr
End Sub