0

The following assignment statement is in the code for a commandbutton on a form:

Range("AvailabilityDataModified").Value = "No"

AvailabilityDataModified is a single-celled named range on a sheet called "Controls". This statement executes properly with no error.

I also have three occurrences of the following statement (virtually identical to the one above) that reside in a sheet's code for multiple event handlers:

Range("AvailabilityDataModified").Value = "Yes"

My problem is the 3 occurrences of the 2nd instance of code generate the 1004 Method 'Range' of object '_Worksheet' failed error while the first does not. This might be a problem with scope; however, I don't believe you need any additional reference info when assigning a value to a named range. I'm at a loss at this point.

Improve this question
11
  • Are other workbooks activated? Is the scope of the name the workbook or a worksheet?
    – Siphor
    Commented Jun 8, 2014 at 22:07
  • According to the Name Manager, the scope of the named range is workbook. That's what makes this confusing.
    – BillD
    Commented Jun 8, 2014 at 22:21
  • If I understand your question correctly, the answer is no. The name of the range, "AvailabilityDataModified", together with the cell address, was created with the Name Mangager.
    – BillD
    Commented Jun 8, 2014 at 22:25
  • 1
    What happens if you prepend "worksheets("Controls")." before Range?
    – Siphor
    Commented Jun 8, 2014 at 22:28
  • 1
    You could also try "Thisworkbook.worksheets("Controls").Range("AvailabilityDataModified").Value" or Thisworkbook.worksheets("Controls").[AvailabilityDataModified].Value
    – Siphor
    Commented Jun 8, 2014 at 22:36

1 Answer 1

Reset to default
4

Use 

Worksheets("Controls").Range("AvailabilityDataModified").Value = "Yes"

instead.
Inside a worksheet object Range refers to SheetName.Range, not to the workbook-scoped Excel.Application.Range object. This causes the range "AvailabilityDataModified" to be restricted to the sheet of the worksheet object. Because no cells of "AvailabilityDataModified" are in the sheet the Range returns a error.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.