Range.Style code gives Run-time error 450
 

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

Range.Style code gives Run-time error 450
 

"Paul Martin" wrote in message

I know error 450 is associated with the Style name not existing

On Error Resume Next
Err.Raise 450
Debug.Print Err.Description

"Wrong number of arguments or invalid property assignment"

Could be many reasons apart from a Style not existing, not that I have any
idea what might be the reason.

Difficult to hazard a guess just looking at your code. If you can, upload a
non sensitive file to show the error.

Peter T

Range.Style code gives Run-time error 450
 

Thanks, Peter. I get error 450 when the range is a merged cell, but it works fine when it's a single cell. The cell/s have range names that define a single cell. If I define the range name as the merged cells, the code works fine for .Style, but then has other problems (such as assigning .Value). I'm a little stumped...

Range.Style code gives Run-time error 450
 

I ascertained that error 450 was because the cells that were merged were defined as Named Ranges only for one cell, rather than the entire range. Defining the Name as the full range fixed it. I did have some other issues around Validation, which had to be deleted and redefined. Thanks...