adding a list validation to a cell

[Phil Sobolik]

This was covered in a thread back in 1999, but the response didn't
work for me. I'm trying to add a drop-down list to a cell with valid
entries for that cell.

With Application.ActiveWorkbook.Worksheets("Items")
.Unprotect
With .Cells(1, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="1,2,3"
.InCellDropdown = True
End With ' Validation
End With ' Worksheet

Doesn't work. I get an 'Application-defined or object-defined error -
1004' at the Add call. I realize that there are a number of ways that
the list can be specified - a Range (named or with an address) will be
the eventual way, but I've used a typed in list as a trivial example.
I can do it in the worksheet itself, but not with VBA. The worksheet
isn't protected, the cell isn't locked. I got the Delete call from
the previous thread. Modify without the Delete doesn't work either.

This is a crucial part of the project I'm working on. It's a do or
the project dies situation.

Excel 2000 9.0.6926 SP3 on Windows 2000

Any help would be greatly appreciated!

 

[Dave Peterson]

I copied and pasted your code into a new workbook. I renamed a sheet Items and
ran the code.

It worked ok for me (xl2002).

If you do the same thing, can you get it to work?

(If the worksheet were protected and I by passed the password prompt, I'd get
the 1004 error.)

 

[Phil Sobolik]

I figured it out! You can only add validation to the worksheet that
is active. I added a .Activate right before the .Unprotect and it
worked. Thanks for your help. The clue was when I did what you did
and it worked.

 

[Dave Peterson]

I tried it again with another worksheet active (not Items).

It still worked ok (xl2002, still).

But glad you got it working.