- Jul 11, 2011
My objective is to use the Worksheet_Change event to test if any cells requiring a value have been cleared, and then to retrieve the default values and put them in the cell. All the cells requiring a value have a named range, and all the default named ranges are the same, except with the letters "Df" appended on them. Example, a named range "Item1" has its default value in a range named "Item1Df". All of my named ranges have scope = "Workbook". I am getting a range object failure, 1004, in the line marked "Error occurs here".
If Not Intersect(Target, Range("cellsWithDefaultValues")) Is Nothing Then If Target.Value = "" Then Dim targetName As Variant targetName = Target.Name.Name MsgBox (targetName & "Df") 'Everything is okay up to here MsgBox Range(targetName & "Df").Value 'Error 1004 occurs here Target.Value = Range(targetName & "Df").Value 'This is what I eventually want to do End If End If