Err: 50290 on check box (Form control)

[stanleytp]

Code was working until I tried to call Sub cbMaint_Click for me another Sub cbMaint_btn. Now I am geting a 50290 error on red line. Any thoughts?

How can I streamline this code?



Sub cbMaint_btn()
Dim MaintItems As Range
Set MaintItems = Range("B9:B19")
Call cbMaint_Click(MaintItems)
End Sub


Sub cbMaint_Click(Maintlist As Range)
Dim cell As Range

If Range("A1").Value = "D" Or ActiveSheet.Shapes("cbMaint").ControlFormat.Value = xlOn Then

Range("F9").Interior.ColorIndex = 6

For Each cell In Worksheets("ROM").Range(Maintlist)
'enable Complexity column
cell.Offset(0, 1).Interior.ColorIndex = 6 'yellow
cell.Offset(0, 1).Font.ColorIndex = xlColorIndexAutomatic
cell.Font.ColorIndex = xlColorIndexAutomatic 'black
cell.Offset(0, 1).Value = "None"
cell.Offset(0, 1).Validation.InCellDropdown = True
cell.Offset(0, 1).Validation.ShowError = True

'enable Hours column
cell.Offset(0, 2).Interior.ColorIndex = xlColorIndexNone 'no color
cell.Offset(0, 2).Font.ColorIndex = xlColorIndexAutomatic
"
Next cell
Else

For Each cell In Worksheets("ROM").Range(Maintlist)
cell.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone 'no color
cell.Offset(0, 1).Font.ColorIndex = 15
cell.Font.ColorIndex = 15 'light gray
cell.Offset(0, 1).Value = "None"
cell.Offset(0, 1).Validation.InCellDropdown = False
cell.Offset(0, 1).Validation.ShowError = False

'disable Hours column
cell.Offset(0, 2).Font.ColorIndex = 15 'light gray
Next cell

End If

End Sub

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Sub cbMaint_btn()
    Dim MaintItems As Range
    Set MaintItems = ThisWorkbook.Worksheets("ROM").Range("B9:B19")
    Call cbMaint_Click(MaintItems)
End Sub


Sub cbMaint_Click(ByVal Maintlist As Range)
    Dim State       As Boolean
    Dim ws          As Worksheet
    
    Set ws = Maintlist.Parent
    
    State = ws.Range("A1").Value = "D" Or ws.CheckBoxes("cbMaint").Value = xlOn
    
    ws.Range("F9").Interior.ColorIndex = 6
    
    With Maintlist
        With .Offset(, 1)
            .Interior.ColorIndex = IIf(State, 6, xlColorIndexNone)              'yellow / None
            .Font.ColorIndex = IIf(State, xlColorIndexAutomatic, 15)            'Black / Light Gray
            .Value = "None"
            .Validation.InCellDropdown = State
            .Validation.ShowError = State
        End With
        .Font.ColorIndex = IIf(State, xlColorIndexAutomatic, 15)                'Black / Light Gray
        'Hours column
        .Offset(0, 2).Interior.ColorIndex = xlColorIndexNone                    'no color
        .Offset(0, 2).Font.ColorIndex = IIf(State, xlColorIndexAutomatic, 15)   'Black / Light Gray
    End With
    
End Sub

Dave