Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error specifying range for sort code vba excel 2010 | Excel Programming | |||
Code Run-time Error '13 | Excel Programming | |||
Applying style to row, getting Run-time error '450' | Excel Programming | |||
VB Code Naming a Range (range changes each time) | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |