I have a number of fields on a worksheet, each with a validation list. Based on a user's prior selections, these fields need to be either enabled or disabled. My plan is to grey out disabled (using one style) or not (with another style), and to allocate either the default list, or a disabled list.

For some inexplicable reason, the code fails on allocating a style. I know error 450 is associated with the Style name not existing, but this isn't the case here. It definitely exists, and the code works sometimes, and not others. Any suggestions are appreciated. Here is my code:


Private Sub EnableDisableSubGrpOptions()
Dim rngSubGrpOptions As Range
Dim i As Integer
Dim strBaseName As String
Dim strOption As String
Dim blnDisable As Boolean
Dim rngOption As Range
Dim strDisableList As String

Set rngSubGrpOptions = Range("DisableOptions")
strDisableList = "=List_Disabled"

For i = 5 To Range("Tbl_DisableOptions").Columns.Count
strBaseName = Range("Tbl_DisableOptions[#Headers]").Cells(i).Value
strOption = "Selected" & strBaseName
Set rngOption = ActiveSheet.Range(strOption)

' Ascertain whether to enable or disable from filtered table
blnDisable = Range("Tbl_DisableOptions").SpecialCells(xlCellTyp eVisible).Cells(i).Value

If blnDisable = True Then
' Disable list if not disabled
With rngOption.Validation
If .Formula1 < strDisableList Then
.Delete
.Add xlValidateList, , , strDisableList

With rngOption
.Value = "Not selected"
.Style = "Not available"
End With
End If
End With
Else
' Enable list if disabled
With rngOption.Validation
If .Formula1 = "=List_Disabled" Then
.Delete
.Add xlValidateList, , , "=List_" & strBaseName

With rngOption
.Value = "Not selected"
.Style = "List cell"
End With
End If
End With
End If
Next i
End Sub