Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 133
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 133
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 133
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Run time error specifying range for sort code vba excel 2010 dial13[_2_] Excel Programming 2 September 10th 12 05:37 PM
Code Run-time Error '13 Ron[_6_] Excel Programming 4 August 21st 09 11:03 PM
Applying style to row, getting Run-time error '450' Dustin Carter[_2_] Excel Programming 2 December 9th 05 03:26 AM
VB Code Naming a Range (range changes each time) krazylain Excel Programming 4 May 15th 04 12:41 PM
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) Tim[_36_] Excel Programming 4 April 23rd 04 02:53 AM


All times are GMT +1. The time now is 06:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"