Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B"
Const WS_STORES As String = "M1:M10"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then
If IsError(Application.Match(Target.Value, Range(WS_STORES), 0))
Then
MsgBox "Invalid selection, redo"
Target.Value = ""
End If
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"certain_death"
wrote in message
news:certain_death.29cd68_1150203653.7643@excelfor um-nospam.com...

Hi all,

Can anybody help me please?

I am trying to build a spreadsheet that has a list of customers on one
sheet (that I control) and some other data on another that a user has
to fill out.

The reason I want this is that all my users are updating data with
different spellings of customers (eg Sainsbury / Sainsbury's) and
obviously then does not give accurate reporting.

What I want the user to do is to update new lines with data, but only
be allowed to select the customers from my sheet (like a drop down list
or combo box). I obviously don't want a combo box on each cell (it has
3000 lines of data), but does anyone have any ideas on how I could
force them to only pick my controlled list???

This could be through protection / macro / vb???

Anything anyone could suggest would be a great help....

Many thanks
Mark
:) :)


--
certain_death
------------------------------------------------------------------------
certain_death's Profile:
http://www.excelforum.com/member.php...o&userid=24561
View this thread: http://www.excelforum.com/showthread...hreadid=551388