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

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

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

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" wrote in message
...
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

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

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" wrote in message
...
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

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

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" wrote in message
...
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

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

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" wrote in message
...
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