Object error - method failed

Hi, I am trying to make a page where
1) if a1 has a value, combobox 1,2,3,4 will be visible
if a2 has a value, combobox 5,6,7,8 will be visible, etc,
2) when combobox1's value = completed, c1's value will be 1
when combobox1's calue = inactive, c1's value will be 2, etc.

I used the following code

Private Sub ComboBox1_Change()
If ComboBox1.Value = "Completed" Then
Range("i16").Value = 1
Else
If ComboBox1.Value = "On Track Low Risk" Then
Range("i16").Value = 2
Else
If ComboBox1.Value = "On Track Medium Risk" Then
Range("i16").Value = 3
Else
If ComboBox1.Value = "High Risk" Then
Range("i16").Value = 4
Else
If ComboBox1.Value = "Inactive" Then
Range("i16").Value = 5
Else
If ComboBox1.Value = "" Then
Range("i16").Value = 0

End If
End If
End If
End If
End If
End If
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim WS As Excel.Worksheet
Set WS = ActiveSheet
Dim comboboxname As String
Dim obj As Object
Dim obj2 As Object
Dim obj3 As Object
Dim obj4 As Object
Dim row1
For row1 = 16 To 40 Step 1
Dim A, i, j, k, l
A = row1 - 21
i = row1 + 3 * A
 j = i + 1
 k = i + 2
 l = i + 3
 
 comboboxname = "ComboBox" & i
 comboboxname2 = "ComboBox" & j
  comboboxname3 = "ComboBox" & k
   comboboxname4 = "ComboBox" & l
 Set obj = WS.OLEObjects(comboboxname)
  Set obj2 = WS.OLEObjects(comboboxname2)
   Set obj3 = WS.OLEObjects(comboboxname3)
    Set obj4 = WS.OLEObjects(comboboxname4)
If Not Cells(row1, 2).Value = "" Then
obj.Visible = True
obj2.Visible = True
obj3.Visible = True
obj4.Visible = True
Else
obj.Visible = False
obj2.Visible = False
obj3.Visible = False
obj4.Visible = False
End If
 Next row1
End Sub

but i keep getting a "Run-time error '1004', Method OLEObjects' of object '_Worksheet' failed." and

 Set obj = WS.OLEObjects(comboboxname)

will be highlighted.
Help please! Thanks in advance!

Hi

At what point in the loop is it failing? First run through? When it fails, do a debug and see what is in the strings (comboboxname, comboboxname2...). Do these comboboxes exist?

If it is failing on the first loop, then build an example file that matches your structure, with the comboboxes etc. Remove any sensitive data, but make the dummy data representative of your data type.

I just took your code for the worksheet change event, put 4 comboboxes from the controls toolbar on the sheet and stepped through it for the first iteration and there were no problems.


rylo