Can I add a description column or alternate column in a validation

[Spongebob]

I am running a validation on a cell for a particular code to be used for a
timesheet. For the finished result I need just the code to show up in the
cell, however there are quite a few codes and they are difficult to remamber.
Is there a way to have the drop-down list display the description (S -
Schematic Design) but then have the result of the drop-down be just the code
(S)?

Thanks in advance,
Jeffrey K. Ries

[Tom Ogilvy]

Not using data validation alone. You might be able to work something up in
the worksheet change event that reacts to the entry and changes it to the
code alone

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count > 1 then exit sub
if target.column = 2 then
On Error goto ErrHandler
Application.EnableEvents = False
target.value = "(" & Mid(Target.Value,2,1) & ")"
end if
ErrHandler:
Application.EnableEvents = True
End Sub.

--
Regards,
Tom Ogilvy

"Spongebob" <[email protected]> wrote in message
news:[email protected]...
> I am running a validation on a cell for a particular code to be used for a
> timesheet. For the finished result I need just the code to show up in the
> cell, however there are quite a few codes and they are difficult to
remamber.
> Is there a way to have the drop-down list display the description (S -
> Schematic Design) but then have the result of the drop-down be just the
code
> (S)?
>
> Thanks in advance,
> Jeffrey K. Ries

[Spongebob]

I attempted to implement your code, but was unable. My VBA knowledge is
very low if any at all. Is this the only way this can be accomplished?

"Tom Ogilvy" wrote:

> Not using data validation alone. You might be able to work something up in
> the worksheet change event that reacts to the entry and changes it to the
> code alone
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> if target.count > 1 then exit sub
> if target.column = 2 then
> On Error goto ErrHandler
> Application.EnableEvents = False
> target.value = "(" & Mid(Target.Value,2,1) & ")"
> end if
> ErrHandler:
> Application.EnableEvents = True
> End Sub.
>
> --
> Regards,
> Tom Ogilvy
>
> "Spongebob" <[email protected]> wrote in message
> news:[email protected]...
> > I am running a validation on a cell for a particular code to be used for a
> > timesheet. For the finished result I need just the code to show up in the
> > cell, however there are quite a few codes and they are difficult to
> remamber.
> > Is there a way to have the drop-down list display the description (S -
> > Schematic Design) but then have the result of the drop-down be just the
> code
> > (S)?
> >
> > Thanks in advance,
> > Jeffrey K. Ries
>
>
>

[Dave Peterson]

Rightclick on the worksheet tab that should have this behavior. Select view
code. Paste Tom's code into that code window.

If your data|validation is not in column B, then change this line:
if target.column = 2 then
to match the column that you need
(a=1, b=2, c=3, ..., iv=256)

Then back to excel and test it out.


Spongebob wrote:
>
> I attempted to implement your code, but was unable. My VBA knowledge is
> very low if any at all. Is this the only way this can be accomplished?
>
> "Tom Ogilvy" wrote:
>
> > Not using data validation alone. You might be able to work something up in
> > the worksheet change event that reacts to the entry and changes it to the
> > code alone
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > if target.count > 1 then exit sub
> > if target.column = 2 then
> > On Error goto ErrHandler
> > Application.EnableEvents = False
> > target.value = "(" & Mid(Target.Value,2,1) & ")"
> > end if
> > ErrHandler:
> > Application.EnableEvents = True
> > End Sub.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Spongebob" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am running a validation on a cell for a particular code to be used for a
> > > timesheet. For the finished result I need just the code to show up in the
> > > cell, however there are quite a few codes and they are difficult to
> > remamber.
> > > Is there a way to have the drop-down list display the description (S -
> > > Schematic Design) but then have the result of the drop-down be just the
> > code
> > > (S)?
> > >
> > > Thanks in advance,
> > > Jeffrey K. Ries
> >
> >
> >

--

Dave Peterson

[Spongebob]

OK, I took a break from that issue but I have now returned.

I have done what you have told me and it now works. Thank you for all of
your help. I think the main problem I was encountering was that my security
level for excel was set too high. This prevented the code from working.

Now that it does work I have encountered a new problem. The data validation
works flawlessly - the drop-downs are great. However, now that the VBA code
changes the result to a single letter it has prevented the end user from
entering the single letter code manually. When this is attempted the
validation error states that it must be equal to that of the original list -
not the resulting single letter.

Does anyone know of a work around?

Jeff

"Dave Peterson" wrote:

> Rightclick on the worksheet tab that should have this behavior. Select view
> code. Paste Tom's code into that code window.
>
> If your data|validation is not in column B, then change this line:
> if target.column = 2 then
> to match the column that you need
> (a=1, b=2, c=3, ..., iv=256)
>
> Then back to excel and test it out.
>
>
> Spongebob wrote:
> >
> > I attempted to implement your code, but was unable. My VBA knowledge is
> > very low if any at all. Is this the only way this can be accomplished?
> >
> > "Tom Ogilvy" wrote:
> >
> > > Not using data validation alone. You might be able to work something up in
> > > the worksheet change event that reacts to the entry and changes it to the
> > > code alone
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > if target.count > 1 then exit sub
> > > if target.column = 2 then
> > > On Error goto ErrHandler
> > > Application.EnableEvents = False
> > > target.value = "(" & Mid(Target.Value,2,1) & ")"
> > > end if
> > > ErrHandler:
> > > Application.EnableEvents = True
> > > End Sub.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Spongebob" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I am running a validation on a cell for a particular code to be used for a
> > > > timesheet. For the finished result I need just the code to show up in the
> > > > cell, however there are quite a few codes and they are difficult to
> > > remamber.
> > > > Is there a way to have the drop-down list display the description (S -
> > > > Schematic Design) but then have the result of the drop-down be just the
> > > code
> > > > (S)?
> > > >
> > > > Thanks in advance,
> > > > Jeffrey K. Ries
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>