Brilliant ..

I had the same problem and searched the forums .. found this solution
.... and it workes.

Apparently the focus control is getting transferred elsewhere

thanks Dave ..


Dave Peterson wrote:
This was a problem with xl97 and went away with xl2k.

If you're not running xl97, then this wasn't the solution.

(by the way, your code also worked for me in xl2002.)

Dave Peterson wrote:

Are you invoking your macro from a commandbutton from the controls
toolbox?

If yes, try setting the TakeFocusOnClick property to false.

If it's from another activex control, then put a line at the top of
your sub
like:

Activesheet.Activate
ActiveCell.Activate

It brings the focus to the worksheet (instead of the control).

=====
The .add not be .Add

I bet you once had a variable named add that was lower case.

Anywhere in your program, type this line:
Dim Add
(hit enter, then delete the line.)

Eddie Brophy wrote:

I am using the following code in an Excel 97 VBA program
to set data validation:

Range("A6").Select
With Selection.Validation
.Delete
.add Type:=xlValidateTextLength, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="15"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = "Your Unique Scheme Reference"
.ErrorTitle = "Your Unique Scheme Reference"
.InputMessage = _
"Enter as a text reference between 1 and 15
characters. Please ensure you use the same references for
the same schemes as in past year and that each scheme has
a different reference."
.ErrorMessage = _
"Enter the unique reference for this scheme, between 1
and 15 characters long."
.ShowInput = True
.ShowError = True
End With

Unfortunately I get:

"Run-time error '1004' Application-defined or object-
defined error"

on the .add statement when I run it and cannot work out
why. Have trawled extensively through newsgroups and
found a similar problem reported on this Group on 17 Dec
1999 by Tommy Hansen but without a solution. Any help
much appreciated.

(Please note the lower case "a" in .add above - I changed
thie to upper case but it reverts to lower case.)

--

Dave Peterson


--

Dave Peterson