ComboBox Permission Error

[nubranger]

I am getting "Permission Denied" error when I change the value of one of the ComboBoxes. Error happens starting on the 3rd change. I am getting error on these lines

Worksheets("Settings").ComboBox1.List = Split(Left2Use, ",")
Worksheets("Settings").ComboBox2.List = Split(Left2Use, ",")

Full Code

Private Sub Workbook_Open()

  Worksheets("Settings").ComboBox1.Value = Null
  Worksheets("Settings").ComboBox2.Value = Null
   
Worksheets("Settings").ComboBox1.List = Split(" ,1,2,3,4,5", ",")
Worksheets("Settings").ComboBox2.List = Split(" ,1,2,3,4,5", ",")

End Sub

Sub ComboBox1_Change()
   ManageDropDowns (1)
End Sub

Sub ComboBox2_Change()
   ManageDropDowns (2)
End Sub


Sub ManageDropDowns(IDNum As Integer)

   Dim UsedList As String, FullList As String, Left2Use As String
   Dim FullArray As Variant, UsedArray(1 To 2) As String

   UL1 = ""
   UL1 = UL1 & IIf(Trim(ComboBox2.Value) = "", "", "" & Trim(ComboBox2.Value) & ",")
   While Right(UL1, 1) = ","
      UL1 = Left(UL1, Len(UL1) - 1)
   Wend

   UL2 = ""
   UL2 = UL2 & IIf(Trim(ComboBox1.Value) = "", "", "" & Trim(ComboBox1.Value) & ",")

   FullList = "1,2,3,4,5"
   FullArray = Split(FullList, ",")
   Left2Use = " ,"

   If IDNum <> 1 Then
      UsedList = UL1
      Left2Use = " "  ' Space
      For i = 0 To UBound(FullArray)
         iTxt = Trim("" & i + 1)
         If Not (InStr(1, UsedList, iTxt) > 0) Then
            ' Not Already Used - Add it to Left2Use
            Left2Use = Left2Use & "," & iTxt
         End If
      Next i
      Worksheets("Settings").ComboBox1.List = Split(Left2Use, ",")
   
   End If
   If IDNum <> 2 Then
      UsedList = UL2
      Left2Use = " "  ' Space
      For i = 0 To UBound(FullArray)
         iTxt = Trim("" & i + 1)
         If Not (InStr(1, UsedList, iTxt) > 0) Then
            ' Not Already Used - Add it to Left2Use
            Left2Use = Left2Use & "," & iTxt
         End If
      Next i
      Worksheets("Settings").ComboBox2.List = Split(Left2Use, ",")
   End If
End Sub

 

[davesexcel]

First thought, you are trying to programically change the control, yet you have entered the list/source using the controls properties window.

 

[nubranger]

First thought, you are trying to programically change the control, yet you have entered the list/source using the controls properties window.

I'm not really sure what to do to resolve this. also tried getting the list from cells.

 

[Kyle123]

As @davesexcel says, just remove the rowsource that you’ve set in the ComboBox property window. You can’t use rowsource and list

 

[nubranger]

C

As @davesexcel says, just remove the rowsource that you’ve set in the ComboBox property window. You can’t use rowsource and list

Could you please tell me which code i need to remove?

The property window you are talking about, is it the "properties" when I right click the ActiveX Control ComboBox?

Thanks

 

[Kyle123]

Yes, it is.

 

[nubranger]

Yes, it is.

I haven't set a rowsource in the properties window or change any settings in the properties window.

Thanks for the reply

 

[davesexcel]

That is usually the case with that error, give us a link to a sample workbook

 

[nubranger]

That is usually the case with that error, give us a link to a sample workbook

The file is HERE.

 

[Kyle123]

your link doesn’t work unfortunately