Change cells in row based on row of Combobox selection

[SHUTTEHFACE]

Good morning,

Can anyone see what is wrong with my code? I am using the name identified in the combobox (cmbPS) to identify the row and then change the resulting cells. I keep getting an application defined or object defined error highlighting the row marked

Cells(cmbPS.ListIndex, 66) = "OFF"

The entire code:

If chbMonday.Value = True Then
    Cells(cmbPS.ListIndex, 66) = "WORK"
    Cells(cmbPS.ListIndex, 67) = Me.cmbStartTime.Value
    Cells(cmbPS.ListIndex, 68) = Me.cmb1stBreakTime.Value
    Cells(cmbPS.ListIndex, 69) = Me.cmbLunchTime.Value
    Cells(cmbPS.ListIndex, 70) = Me.cmb2ndBreakTime.Value
    Cells(cmbPS.ListIndex, 71) = Me.cmbEndTime.Value
    Else
    Cells(cmbPS.ListIndex, 66) = "OFF"
    Range("BO7:BS7") = ""
End If

Thanks

M

[SHUTTEHFACE]

Any ideas?

[SHUTTEHFACE]

PArtially fixed using the following code:

If chbMonday.Value = True Then
    Cells(cmbPS.ListIndex + 7, 66) = "WORK"
    Cells(cmbPS.ListIndex + 7, 67) = Me.cmbStartTime.Value
    Cells(cmbPS.ListIndex + 7, 68) = Me.cmb1stBreakTime.Value
    Cells(cmbPS.ListIndex + 7, 69) = Me.cmbLunchTime.Value
    Cells(cmbPS.ListIndex + 7, 70) = Me.cmb2ndBreakTime.Value
    Cells(cmbPS.ListIndex + 7, 71) = Me.cmbEndTime.Value
    Else
    Cells(cmbPS.ListIndex + 7, 66) = "OFF"
    Range("BO:BS").Rows(cmbPSListIndex + 7) = ""
End If

Problem now is that it's updating the right columns but the wrong rows. Is my cmbPSListIndex + 7 incorrect in defining the row of the selected value from the combobox (the list starts 7 rows down on the spreadsheet)?

[SHUTTEHFACE]

Any ideas?

[bulina2k]

Hi ! Can't you upload a sample file? (after striping off the sensible data)

[SHUTTEHFACE]

Sorry I can't post the file, I think it has something to do with this line being incorrect:

Range("BO:BS").Rows(cmbPSListIndex) = ""

[bulina2k]

there is a dot missing there...
if that is the error it should be

Range("BO:BS").Rows(cmbPS.ListIndex) = ""

if not, try to find the cmbPS.ListIndex value
put a msgbox cmbPS.ListIndex before that line. (debug.print cmbPS.ListIndex works also if you have the Immediate Window enabled (Ctrl+G)

[SHUTTEHFACE]

Ok I pinpointed the issue, it's because cmBPS is creating unique values from PSList which has duplicate entries:

Dim MyUniqueList As Variant, i As Long
    With Me.cmbPS
        .Clear ' clear the combobox content
        MyUniqueList = UniqueItemList(Range("PSList"), True)
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i)
        Next i
        .ListIndex = 0 ' select the first item
    End With

As a result, cmbPS.ListIndex is not equal to PSList.ListIndex

How should I go about fixing this?

[SHUTTEHFACE]

OK I fixed that but for some reason it's still changing the incorrect rows.

[bulina2k]

i have no ideea untill i see the entire code BUT you can try :

Dim MyUniqueList As Variant, i As Long
    With Me.cmbPS
        .Clear ' clear the combobox content
        MyUniqueList = Range("PSList")
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i, 1)
        Next i
        .ListIndex = 0 ' select the first item
    End With