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

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???




wrote:

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

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

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


wrote:

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

--

Dave Peterson

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