Minor amendment needed for code to run

[Ironman]

Hi

The following code in the worksheet_change event is triggered when any text value other than 'OTHER' and 'REST' is input in column B in the last filled row. The input text "Double click for lifetime mileage total up to this date" is then added to the validation in Column H.

If target.Column = 2 And target.Value <> "OTHER" And target.Value <> "REST" Then
    Application.EnableEvents = False
    With Range("A23358").End(xlUp).Offset(1, 7).Validation
         .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
         .InputMessage = "Double click for lifetime mileage total up to this date"
         .ShowInput = True
       End With
   Application.EnableEvents = True
End If

I'm pretty close, but I can't quite get the first 3 lines right for the code to run.

Help would be appreciated.

Many thanks!

 

[NoSparks]

When putting a breakpoint on that first line does the code actually break there when you make a change in column B ?
If so, does F8 step through each of the following lines ?

 

[Ironman]

Hi Nolan

It errors 424 "Object Required"

 

[NoSparks]

on what line?

 

[Ironman]

If target.Column = 2 And target.Value <> "OTHER" And target.Value <> "REST" Then

 

[NoSparks]

if you comment out everything between the IF line and the End IF line do you still get that?

 

[Ironman]

Yes

 

[NoSparks]

I don't see anything wrong with that IF line.

I just pasted the code from post 1 to the end of the Worksheet_Change Event before the call to ImportantMessages in the last file you provided and it doesn't give any error on that line, although it does error (1004 - Application-defined or object-defined error) on the .Add Type line.

I know nothing about data validation like that so will probably be searching the web for a while.

Sorry I can't help with this.

 

[Ironman]

No worries, thanks for trying. I know the data validation line works OK, I've used that before.

 

[Joe4]

Something that looks a little odd to me, "target" is in lower case, when it typically is in upper case, i.e. "Target".
It leads me to believe that perhaps you declared your own "target" variable, which could conflict with the imbedded parameter in the "Worksheet_Change" even procedure.

Could you please post the entire block of code you have for this procedure?