validations....macros.....HELP!!!

[tasgirl]

How can i do the following with out creating another column

I know how to make a list using validation but I would like it to return a different value

I actually want it to retun the abrevation

so when they veiw the list to pick from they would see

REGT MF2
REGT MF1
OVER MF2
OVER MF1

what i would like it to return would be

01-1613
01-1614
03-1616
03-1617

lets say I would like this validation in sheet 1 Q10
the name range for the list is called TASEmployeeActingCodeDescription the range i would like it to return is named TASEmployeeActingCodes
TASEmployeeActingCodeDescription is in(sheet 2 K2:K5000) TASEmployeeActingCodes is in(sheet 2 J2:J5000)


Can some one help me out with this?
I hope this makes sense


I know it can be done i just cant remeber how
<!-- / message --><!-- edit note -->

 

[lenze]

A very similar question was asked here
http://www.mrexcel.com/forum/showthread.php?t=326812&highlight=Select+Case

abrevation[sic] was even spelled the same!

lenze

 

[tasgirl]

Yeah, that was my friend asking for me about something else yesterday...I wan't able to come on here....

So, it is giving me an error message as follows:

Compile error:

Ambiguous name detected: Worksheet_Change



What is this, and how can I make it work....

I already am using the same macro for something else in a different part of the same worksheet...so, I thought that I could just copy it and change some of the info...HELP please.

Thanks!!

 

[teri_carlson]

TasGirl ....You can use the same macro but you need to change all the info

You will need to change the following

Target.Column
Pick= Array
Out=Array

target collum is a number so for collum A=1 B=2 C=3 ect ect

 

[teri_carlson]

ok what she needs to know is she has this


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pick, Out, i As Integer
Pick = Array("Regular Time", "Regular Time + Shift Diff.", "Overtime", "Sleeptime", "Training Taken", "Bereavement Leave", "Lack of Shift", "Jury Duty", "Approved Requested Time Off", "Away without Approval", "TAS Personal Day", "TAS Sick", "TAS Lieu Day")
Out = Array("REGT", "RTSD", "OVER", "SLTM", "TRTK", "BELV", "0100", "JURY", "ARTO", "AWOA", "TSPR", "TSSK", "TSLU")
If Target.Column <> 7 Then Exit Sub
Application.EnableEvents = False
For i = LBound(Pick) To UBound(Pick)
If Target.Value = Pick(i) Then
Target.Value = Out(i)
Exit For
End If

But she also wants this to work on the same sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pick, Out, i As Integer
Pick = Array("First one", "Second one", "Third one")
Out = Array("001", "002", "003")
If Target.Column <> 17 Then Exit Sub
Application.EnableEvents = False
For i = LBound(Pick) To UBound(Pick)
If Target.Value = Pick(i) Then
Target.Value = Out(i)
Exit For
End If
Next i
Application.EnableEvents = True
End Sub

Note:
(i just put first, second third to show where she can put the data she really wants ) so what she is asking is how do you put the 2 together so both variable work on the same worksheet on the seperate collums

 

[lenze]

Untested, but something like this??

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo myEnd:
Application.EnableEvents = False
Select Case Target.Column
Case 7
Dim Pick, Out, i As Integer
Pick = Array("Regular Time", "Regular Time + Shift Diff.", "Overtime", "Sleeptime", "Training Taken", "Bereavement Leave", "Lack of Shift", "Jury Duty", "Approved Requested Time Off", "Away without Approval", "TAS Personal Day", "TAS Sick", "TAS Lieu Day")
Out = Array("REGT", "RTSD", "OVER", "SLTM", "TRTK", "BELV", "0100", "JURY", "ARTO", "AWOA", "TSPR", "TSSK", "TSLU")
For i = LBound(Pick) To UBound(Pick)
If Target.Value = Pick(i) Then
Target.Value = Out(i)
Exit For
End If

Case 17
Dim Pick, Out, i As Integer
Pick = Array("First one", "Second one", "Third one")
Out = Array("001", "002", "003")
For i = LBound(Pick) To UBound(Pick)
If Target.Value = Pick(i) Then
Target.Value = Out(i)
Exit For
End If
Next i
Case Else:
End Select
myEnd: Application.EnableEvents = True
End Sub

lenze

 

[tasgirl]

I tried the above...but, it is not working....any other ideas????

 

[tasgirl]

I know how to make a list using validation but I would like it to return a different value

I actually want it to retun the abrevation

so when they view the list to pick from they would see

REGT MF2
REGT MF1
OVER MF2
OVER MF1

what i would like it to return would be

01-1613
01-1614
03-1616
03-1617

lets say I would like this validation in sheet 1 Q10
the name range for the list is called TASEmployeeActingCodeDescription the range i would like it to return is named TASEmployeeActingCodes
TASEmployeeActingCodeDescription is in(sheet 2 K2:K5000) TASEmployeeActingCodes is in(sheet 2 J2:J5000)

I already have the following working in another part of the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pick, Out, i As Integer
Pick = Array("Regular Time", "Regular Time + Shift Diff.", "Overtime", "Sleeptime", "Training Taken", "Bereavement Leave", "Lack of Shift", "Jury Duty", "Approved Requested Time Off", "Away without Approval", "TAS Personal Day", "TAS Sick", "TAS Lieu Day")
Out = Array("REGT", "RTSD", "OVER", "SLTM", "TRTK", "BELV", "0100", "JURY", "ARTO", "AWOA", "TSPR", "TSSK", "TSLU")
If Target.Column <> 7 Then Exit Sub
Application.EnableEvents = False
For i = LBound(Pick) To UBound(Pick)
If Target.Value = Pick(i) Then
Target.Value = Out(i)
Exit For
End If
Next i
Application.EnableEvents = True
End Sub


I NEED them both to work on the same sheet, just in different portions of it.

Can some one help me out with this?
I hope this makes sense

 

[lenze]

Just use Select Case. Let's say you have 2 validation lists. One in "$F$5", the other in "$Q$10". You can have more than 2 if need be

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target. Address
     Case "$F$5" 
        Select Case Target
            Case "REGT MF2": Target = "01-1613"
            Case "REGT MF1": Target = "01-1614"
            'etc
            Case Else:
        End Select
     Case "$Q$10"
         Select Case Target
             Case "Regular Time": Target = "REGT"
             Case "Regular Time + Shift Diff.": Target = "RTSD"  
             'etc
             Case Else:
         End Select
      'etc
      Case Else:
End Select
Application.EnableEvents = True
End Sub

lenze