I am trying to create a dropbox where the user can only select from the
list. I know I can do this through data validation, but the problem is that
it can be overridden if a user paste a value on to the cell. How can I have
a dropbox where the user cannot override the validation through pasting or
through any other means?
Thanks
Leo
Leo,
I believe the only solution is to write VBA code, to check the data that's
sneaked by the data validation.
Are you comfortable doing that?
Naomi
"Leo" <LL@nospam.com> wrote in message
news:OLNQI2ZYGHA.1200@TK2MSFTNGP03.phx.gbl...
> I am trying to create a dropbox where the user can only select from the
> list. I know I can do this through data validation, but the problem is
that
> it can be overridden if a user paste a value on to the cell. How can I
have
> a dropbox where the user cannot override the validation through pasting or
> through any other means?
>
> Thanks
> Leo
>
>
Hi Naomi,
I know a little bit about writing code, but I really don't know where to
start on this one? Any ideas?
Thanks
Leo
"Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
news:yHW0g.595$wd2.307@trndny02...
> Leo,
>
> I believe the only solution is to write VBA code, to check the data that's
> sneaked by the data validation.
>
> Are you comfortable doing that?
>
> Naomi
>
>
> "Leo" <LL@nospam.com> wrote in message
> news:OLNQI2ZYGHA.1200@TK2MSFTNGP03.phx.gbl...
>> I am trying to create a dropbox where the user can only select from the
>> list. I know I can do this through data validation, but the problem is
> that
>> it can be overridden if a user paste a value on to the cell. How can I
> have
>> a dropbox where the user cannot override the validation through pasting
>> or
>> through any other means?
>>
>> Thanks
>> Leo
>>
>>
>
>
Leo,
Well, first figure out what your requirements are. Here are some questions
for you:
Do you have many cells with data validation, or just one or two?
Do you want the error message to fire:
a) as soon as the user tabs out of the cell
b) when he tries to save his spreadsheet
c) when he explicitly runs a Validate macro
Naomi
"Leo" <LL@nospam.com> wrote in message
news:OOWRagoYGHA.1200@TK2MSFTNGP03.phx.gbl...
> Hi Naomi,
> I know a little bit about writing code, but I really don't know where to
> start on this one? Any ideas?
>
> Thanks
> Leo
>
> "Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
> news:yHW0g.595$wd2.307@trndny02...
> > Leo,
> >
> > I believe the only solution is to write VBA code, to check the data
that's
> > sneaked by the data validation.
> >
> > Are you comfortable doing that?
> >
> > Naomi
> >
> >
> > "Leo" <LL@nospam.com> wrote in message
> > news:OLNQI2ZYGHA.1200@TK2MSFTNGP03.phx.gbl...
> >> I am trying to create a dropbox where the user can only select from the
> >> list. I know I can do this through data validation, but the problem is
> > that
> >> it can be overridden if a user paste a value on to the cell. How can I
> > have
> >> a dropbox where the user cannot override the validation through pasting
> >> or
> >> through any other means?
> >>
> >> Thanks
> >> Leo
> >>
> >>
> >
> >
>
>
Hi Naomi,
I have about four or five cells with data validation. I would like the
error message to fire as soon as the user tabs out of the cell.
Thanks
Leo
"Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
news:kyX0g.603$wd2.28@trndny02...
> Leo,
>
> Well, first figure out what your requirements are. Here are some questions
> for you:
>
> Do you have many cells with data validation, or just one or two?
>
> Do you want the error message to fire:
> a) as soon as the user tabs out of the cell
> b) when he tries to save his spreadsheet
> c) when he explicitly runs a Validate macro
>
> Naomi
>
>
> "Leo" <LL@nospam.com> wrote in message
> news:OOWRagoYGHA.1200@TK2MSFTNGP03.phx.gbl...
>> Hi Naomi,
>> I know a little bit about writing code, but I really don't know where to
>> start on this one? Any ideas?
>>
>> Thanks
>> Leo
>>
>> "Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
>> news:yHW0g.595$wd2.307@trndny02...
>> > Leo,
>> >
>> > I believe the only solution is to write VBA code, to check the data
> that's
>> > sneaked by the data validation.
>> >
>> > Are you comfortable doing that?
>> >
>> > Naomi
>> >
>> >
>> > "Leo" <LL@nospam.com> wrote in message
>> > news:OLNQI2ZYGHA.1200@TK2MSFTNGP03.phx.gbl...
>> >> I am trying to create a dropbox where the user can only select from
>> >> the
>> >> list. I know I can do this through data validation, but the problem
>> >> is
>> > that
>> >> it can be overridden if a user paste a value on to the cell. How can
>> >> I
>> > have
>> >> a dropbox where the user cannot override the validation through
>> >> pasting
>> >> or
>> >> through any other means?
>> >>
>> >> Thanks
>> >> Leo
>> >>
>> >>
>> >
>> >
>>
>>
>
>
Leo,
Does this code work for you? You'll need to repeat the logic for each cell
with validation, substituting in the appropriate column & row combinations,
and the valid values.
I'm sure there's a more elegant way, but this works...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 Then
Select Case Target.Value
Case "aaa", "bbb", "ccc"
' this is valid
Case Else
MsgBox "Value entered into cell A1 was not valid."
End Select
End If
End Sub
Naomi
"Leo" <LL@nospam.com> wrote in message
news:Oyvz0JpYGHA.3424@TK2MSFTNGP02.phx.gbl...
> Hi Naomi,
> I have about four or five cells with data validation. I would like the
> error message to fire as soon as the user tabs out of the cell.
>
> Thanks
> Leo
>
>
>
> "Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
> news:kyX0g.603$wd2.28@trndny02...
> > Leo,
> >
> > Well, first figure out what your requirements are. Here are some
questions
> > for you:
> >
> > Do you have many cells with data validation, or just one or two?
> >
> > Do you want the error message to fire:
> > a) as soon as the user tabs out of the cell
> > b) when he tries to save his spreadsheet
> > c) when he explicitly runs a Validate macro
> >
> > Naomi
> >
> >
> > "Leo" <LL@nospam.com> wrote in message
> > news:OOWRagoYGHA.1200@TK2MSFTNGP03.phx.gbl...
> >> Hi Naomi,
> >> I know a little bit about writing code, but I really don't know where
to
> >> start on this one? Any ideas?
> >>
> >> Thanks
> >> Leo
> >>
> >> "Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
> >> news:yHW0g.595$wd2.307@trndny02...
> >> > Leo,
> >> >
> >> > I believe the only solution is to write VBA code, to check the data
> > that's
> >> > sneaked by the data validation.
> >> >
> >> > Are you comfortable doing that?
> >> >
> >> > Naomi
> >> >
> >> >
> >> > "Leo" <LL@nospam.com> wrote in message
> >> > news:OLNQI2ZYGHA.1200@TK2MSFTNGP03.phx.gbl...
> >> >> I am trying to create a dropbox where the user can only select from
> >> >> the
> >> >> list. I know I can do this through data validation, but the problem
> >> >> is
> >> > that
> >> >> it can be overridden if a user paste a value on to the cell. How
can
> >> >> I
> >> > have
> >> >> a dropbox where the user cannot override the validation through
> >> >> pasting
> >> >> or
> >> >> through any other means?
> >> >>
> >> >> Thanks
> >> >> Leo
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>
Naomi,
That worked. Thanks for helping a newbie
Leo
"Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
news:LkZ0g.1735$UK5.773@trndny01...
> Leo,
>
> Does this code work for you? You'll need to repeat the logic for each cell
> with validation, substituting in the appropriate column & row
> combinations,
> and the valid values.
>
> I'm sure there's a more elegant way, but this works...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 1 And Target.Row = 1 Then
> Select Case Target.Value
> Case "aaa", "bbb", "ccc"
> ' this is valid
> Case Else
> MsgBox "Value entered into cell A1 was not valid."
> End Select
> End If
> End Sub
>
> Naomi
>
>
> "Leo" <LL@nospam.com> wrote in message
> news:Oyvz0JpYGHA.3424@TK2MSFTNGP02.phx.gbl...
>> Hi Naomi,
>> I have about four or five cells with data validation. I would like the
>> error message to fire as soon as the user tabs out of the cell.
>>
>> Thanks
>> Leo
>>
>>
>>
>> "Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
>> news:kyX0g.603$wd2.28@trndny02...
>> > Leo,
>> >
>> > Well, first figure out what your requirements are. Here are some
> questions
>> > for you:
>> >
>> > Do you have many cells with data validation, or just one or two?
>> >
>> > Do you want the error message to fire:
>> > a) as soon as the user tabs out of the cell
>> > b) when he tries to save his spreadsheet
>> > c) when he explicitly runs a Validate macro
>> >
>> > Naomi
>> >
>> >
>> > "Leo" <LL@nospam.com> wrote in message
>> > news:OOWRagoYGHA.1200@TK2MSFTNGP03.phx.gbl...
>> >> Hi Naomi,
>> >> I know a little bit about writing code, but I really don't know where
> to
>> >> start on this one? Any ideas?
>> >>
>> >> Thanks
>> >> Leo
>> >>
>> >> "Naomi Hildebrand" <naomi@hildebrand.com> wrote in message
>> >> news:yHW0g.595$wd2.307@trndny02...
>> >> > Leo,
>> >> >
>> >> > I believe the only solution is to write VBA code, to check the data
>> > that's
>> >> > sneaked by the data validation.
>> >> >
>> >> > Are you comfortable doing that?
>> >> >
>> >> > Naomi
>> >> >
>> >> >
>> >> > "Leo" <LL@nospam.com> wrote in message
>> >> > news:OLNQI2ZYGHA.1200@TK2MSFTNGP03.phx.gbl...
>> >> >> I am trying to create a dropbox where the user can only select from
>> >> >> the
>> >> >> list. I know I can do this through data validation, but the
>> >> >> problem
>> >> >> is
>> >> > that
>> >> >> it can be overridden if a user paste a value on to the cell. How
> can
>> >> >> I
>> >> > have
>> >> >> a dropbox where the user cannot override the validation through
>> >> >> pasting
>> >> >> or
>> >> >> through any other means?
>> >> >>
>> >> >> Thanks
>> >> >> Leo
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks