Hello,
Does anyone know how I can make a dropdown list in Excel return a value that
is not necessarily in the list?
Example - the list would include city names and possibly the corresponding
code, but I would like it to return only the corresponding three digit "code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation feature in
Excel, and I am more than willing to try it via VB, but I'm not familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a value
that
is not necessarily in the list?
Example - the list would include city names and possibly the corresponding
code, but I would like it to return only the corresponding three digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation feature
in
Excel, and I am more than willing to try it via VB, but I'm not familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

Okay,

Is it possible to do the change worksheet event without creating a named
range on a separate sheet? At the moment, I just have the list entered under
the Data/Validation "Source" box in the Excel menu. I have to submit this
workbook when the data is entered and would prefer not to have any extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that somewhere I
would have to tell VB that if "Langdon City" is chosen from the dropdown box,
excel should enter "292" in its place in that cell.

Thanks for your help!


"Tom Ogilvy" wrote:

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a value
that
is not necessarily in the list?
Example - the list would include city names and possibly the corresponding
code, but I would like it to return only the corresponding three digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation feature
in
Excel, and I am more than willing to try it via VB, but I'm not familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

Also, I need to have this type of dropbox entered in more than one cell in
the column- I need to have it available to all cells in column G.

Thanks again!
Tanya

"twirth" wrote:

Okay,

Is it possible to do the change worksheet event without creating a named
range on a separate sheet? At the moment, I just have the list entered under
the Data/Validation "Source" box in the Excel menu. I have to submit this
workbook when the data is entered and would prefer not to have any extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that somewhere I
would have to tell VB that if "Langdon City" is chosen from the dropdown box,
excel should enter "292" in its place in that cell.

Thanks for your help!


"Tom Ogilvy" wrote:

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a value
that
is not necessarily in the list?
Example - the list would include city names and possibly the corresponding
code, but I would like it to return only the corresponding three digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation feature
in
Excel, and I am more than willing to try it via VB, but I'm not familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

the cities were listed in column A of sheet2 and the corresponding code in
column B of sheet2

You can try this adjustement which requires that you enter the data in the
procedure. Sample data has been pre-entered. follow that pattern.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, v1, v2, idex as long, i as Long

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")
v2 = Array(292,396,442)
On Error GoTo ErrHandler
if Target.count 1 then exit sub
idex = -1
If Target.column = 7 Then
for i = lbound(v1) to Ubound(v1)
if lcase(v1(i)) = lcase(Target.value) then
idex = i
exit for
end if
Next
if idex < -1 then
Application.EnableEvents = True
Target.Value = v2(idex)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Also, I need to have this type of dropbox entered in more than one cell in
the column- I need to have it available to all cells in column G.

Thanks again!
Tanya

"twirth" wrote:

Okay,

Is it possible to do the change worksheet event without creating a named
range on a separate sheet? At the moment, I just have the list entered
under
the Data/Validation "Source" box in the Excel menu. I have to submit
this
workbook when the data is entered and would prefer not to have any extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that somewhere
I
would have to tell VB that if "Langdon City" is chosen from the dropdown
box,
excel should enter "292" in its place in that cell.

Thanks for your help!


"Tom Ogilvy" wrote:

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a
value
that
is not necessarily in the list?
Example - the list would include city names and possibly the
corresponding
code, but I would like it to return only the corresponding three
digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation
feature
in
Excel, and I am more than willing to try it via VB, but I'm not
familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

That works! You rock - thank you so much for the help!!!
Tanya

"Tom Ogilvy" wrote:

the cities were listed in column A of sheet2 and the corresponding code in
column B of sheet2

You can try this adjustement which requires that you enter the data in the
procedure. Sample data has been pre-entered. follow that pattern.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, v1, v2, idex as long, i as Long

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")
v2 = Array(292,396,442)
On Error GoTo ErrHandler
if Target.count 1 then exit sub
idex = -1
If Target.column = 7 Then
for i = lbound(v1) to Ubound(v1)
if lcase(v1(i)) = lcase(Target.value) then
idex = i
exit for
end if
Next
if idex < -1 then
Application.EnableEvents = True
Target.Value = v2(idex)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Also, I need to have this type of dropbox entered in more than one cell in
the column- I need to have it available to all cells in column G.

Thanks again!
Tanya

"twirth" wrote:

Okay,

Is it possible to do the change worksheet event without creating a named
range on a separate sheet? At the moment, I just have the list entered
under
the Data/Validation "Source" box in the Excel menu. I have to submit
this
workbook when the data is entered and would prefer not to have any extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that somewhere
I
would have to tell VB that if "Langdon City" is chosen from the dropdown
box,
excel should enter "292" in its place in that cell.

Thanks for your help!


"Tom Ogilvy" wrote:

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a
value
that
is not necessarily in the list?
Example - the list would include city names and possibly the
corresponding
code, but I would like it to return only the corresponding three
digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation
feature
in
Excel, and I am more than willing to try it via VB, but I'm not
familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

Tom,

Is it possible to run the worksheet_change event more than once? Because I
need different list info in another column with dropdowns, I attempted to
copy the code directly below itself and change the column and list
information, but I get the error "ambiguous name detected", I'm assuming
because these events are titled the same?


"Tom Ogilvy" wrote:

the cities were listed in column A of sheet2 and the corresponding code in
column B of sheet2

You can try this adjustement which requires that you enter the data in the
procedure. Sample data has been pre-entered. follow that pattern.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, v1, v2, idex as long, i as Long

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")
v2 = Array(292,396,442)
On Error GoTo ErrHandler
if Target.count 1 then exit sub
idex = -1
If Target.column = 7 Then
for i = lbound(v1) to Ubound(v1)
if lcase(v1(i)) = lcase(Target.value) then
idex = i
exit for
end if
Next
if idex < -1 then
Application.EnableEvents = True
Target.Value = v2(idex)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Also, I need to have this type of dropbox entered in more than one cell in
the column- I need to have it available to all cells in column G.

Thanks again!
Tanya

"twirth" wrote:

Okay,

Is it possible to do the change worksheet event without creating a named
range on a separate sheet? At the moment, I just have the list entered
under
the Data/Validation "Source" box in the Excel menu. I have to submit
this
workbook when the data is entered and would prefer not to have any extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that somewhere
I
would have to tell VB that if "Langdon City" is chosen from the dropdown
box,
excel should enter "292" in its place in that cell.

Thanks for your help!


"Tom Ogilvy" wrote:

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a
value
that
is not necessarily in the list?
Example - the list would include city names and possibly the
corresponding
code, but I would like it to return only the corresponding three
digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation
feature
in
Excel, and I am more than willing to try it via VB, but I'm not
familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

If the lists are completely different, then you can just put all the choices
and corresponding results in the two existing arrays. Change the If
statement to

If Target.column = 7 or Target.column = 11 Then ' as an example.

If the choices overlap (both of the lists select cities, but you want to
return something else), then You can put in

Select Case Target.column
Case 7
' code and arrays for column 7
case 11
' code and arrays for column 11

end Select

So all you code has to go in the single change event, but you structure your
code to react accordingly.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Tom,

Is it possible to run the worksheet_change event more than once? Because
I
need different list info in another column with dropdowns, I attempted to
copy the code directly below itself and change the column and list
information, but I get the error "ambiguous name detected", I'm assuming
because these events are titled the same?


"Tom Ogilvy" wrote:

the cities were listed in column A of sheet2 and the corresponding code
in
column B of sheet2

You can try this adjustement which requires that you enter the data in
the
procedure. Sample data has been pre-entered. follow that pattern.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, v1, v2, idex as long, i as Long

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")
v2 = Array(292,396,442)
On Error GoTo ErrHandler
if Target.count 1 then exit sub
idex = -1
If Target.column = 7 Then
for i = lbound(v1) to Ubound(v1)
if lcase(v1(i)) = lcase(Target.value) then
idex = i
exit for
end if
Next
if idex < -1 then
Application.EnableEvents = True
Target.Value = v2(idex)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Also, I need to have this type of dropbox entered in more than one cell
in
the column- I need to have it available to all cells in column G.

Thanks again!
Tanya

"twirth" wrote:

Okay,

Is it possible to do the change worksheet event without creating a
named
range on a separate sheet? At the moment, I just have the list
entered
under
the Data/Validation "Source" box in the Excel menu. I have to submit
this
workbook when the data is entered and would prefer not to have any
extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that
somewhere
I
would have to tell VB that if "Langdon City" is chosen from the
dropdown
box,
excel should enter "292" in its place in that cell.

Thanks for your help!


"Tom Ogilvy" wrote:

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a
value
that
is not necessarily in the list?
Example - the list would include city names and possibly the
corresponding
code, but I would like it to return only the corresponding three
digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation
feature
in
Excel, and I am more than willing to try it via VB, but I'm not
familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

Thank you - I'm starting to wish I had paid more attention in VB class! I
guess at the time it didn't seem that useful, I mean when would you REALLY
need to make christmas tree bulbs flash different colors?!

One more question and hopefully I'll have this spreadsheet ready to go - one
of my columns has like 60 options for the drop down list - and when I get so
far down the array I get a message that I have too many lines in my array.
Is there any way to add more lines and keep going with the list?

Tanya
"Tom Ogilvy" wrote:

If the lists are completely different, then you can just put all the choices
and corresponding results in the two existing arrays. Change the If
statement to

If Target.column = 7 or Target.column = 11 Then ' as an example.

If the choices overlap (both of the lists select cities, but you want to
return something else), then You can put in

Select Case Target.column
Case 7
' code and arrays for column 7
case 11
' code and arrays for column 11

end Select

So all you code has to go in the single change event, but you structure your
code to react accordingly.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Tom,

Is it possible to run the worksheet_change event more than once? Because
I
need different list info in another column with dropdowns, I attempted to
copy the code directly below itself and change the column and list
information, but I get the error "ambiguous name detected", I'm assuming
because these events are titled the same?


"Tom Ogilvy" wrote:

the cities were listed in column A of sheet2 and the corresponding code
in
column B of sheet2

You can try this adjustement which requires that you enter the data in
the
procedure. Sample data has been pre-entered. follow that pattern.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, v1, v2, idex as long, i as Long

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")
v2 = Array(292,396,442)
On Error GoTo ErrHandler
if Target.count 1 then exit sub
idex = -1
If Target.column = 7 Then
for i = lbound(v1) to Ubound(v1)
if lcase(v1(i)) = lcase(Target.value) then
idex = i
exit for
end if
Next
if idex < -1 then
Application.EnableEvents = True
Target.Value = v2(idex)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Also, I need to have this type of dropbox entered in more than one cell
in
the column- I need to have it available to all cells in column G.

Thanks again!
Tanya

"twirth" wrote:

Okay,

Is it possible to do the change worksheet event without creating a
named
range on a separate sheet? At the moment, I just have the list
entered
under
the Data/Validation "Source" box in the Excel menu. I have to submit
this
workbook when the data is entered and would prefer not to have any
extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that
somewhere
I
would have to tell VB that if "Langdon City" is chosen from the
dropdown
box,
excel should enter "292" in its place in that cell.

Thanks for your help!


"Tom Ogilvy" wrote:

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a
value
that
is not necessarily in the list?
Example - the list would include city names and possibly the
corresponding
code, but I would like it to return only the corresponding three
digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation
feature
in
Excel, and I am more than willing to try it via VB, but I'm not
familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

If you have 60 options, I have a hard time believing they are not listed on
a sheet someplace. But whatever.

You don't have to do

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")


That was just to make it look nice. I am guessing you are are getting too
many line continuation characters. You can put a massive amount of choices
on each line

v1 = Array("City1","City2","City3","City4","City5","Cit y6","City7", _
"City8","City9","City10","City11","City12", . . .,"City250")

as an example.

--
regards,
Tom Ogilvy


"twirth" wrote in message
...
Thank you - I'm starting to wish I had paid more attention in VB class! I
guess at the time it didn't seem that useful, I mean when would you REALLY
need to make christmas tree bulbs flash different colors?!

One more question and hopefully I'll have this spreadsheet ready to go -
one
of my columns has like 60 options for the drop down list - and when I get
so
far down the array I get a message that I have too many lines in my array.
Is there any way to add more lines and keep going with the list?

Tanya
"Tom Ogilvy" wrote:

If the lists are completely different, then you can just put all the
choices
and corresponding results in the two existing arrays. Change the If
statement to

If Target.column = 7 or Target.column = 11 Then ' as an example.

If the choices overlap (both of the lists select cities, but you want to
return something else), then You can put in

Select Case Target.column
Case 7
' code and arrays for column 7
case 11
' code and arrays for column 11

end Select

So all you code has to go in the single change event, but you structure
your
code to react accordingly.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Tom,

Is it possible to run the worksheet_change event more than once?
Because
I
need different list info in another column with dropdowns, I attempted
to
copy the code directly below itself and change the column and list
information, but I get the error "ambiguous name detected", I'm
assuming
because these events are titled the same?


"Tom Ogilvy" wrote:

the cities were listed in column A of sheet2 and the corresponding
code
in
column B of sheet2

You can try this adjustement which requires that you enter the data in
the
procedure. Sample data has been pre-entered. follow that pattern.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, v1, v2, idex as long, i as Long

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")
v2 = Array(292,396,442)
On Error GoTo ErrHandler
if Target.count 1 then exit sub
idex = -1
If Target.column = 7 Then
for i = lbound(v1) to Ubound(v1)
if lcase(v1(i)) = lcase(Target.value) then
idex = i
exit for
end if
Next
if idex < -1 then
Application.EnableEvents = True
Target.Value = v2(idex)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Also, I need to have this type of dropbox entered in more than one
cell
in
the column- I need to have it available to all cells in column G.

Thanks again!
Tanya

"twirth" wrote:

Okay,

Is it possible to do the change worksheet event without creating a
named
range on a separate sheet? At the moment, I just have the list
entered
under
the Data/Validation "Source" box in the Excel menu. I have to
submit
this
workbook when the data is entered and would prefer not to have any
extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that
somewhere
I
would have to tell VB that if "Langdon City" is chosen from the
dropdown
box,
excel should enter "292" in its place in that cell.

Thanks for your help!


"Tom Ogilvy" wrote:

You could do it with data validation and the change worksheet
event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return
a
value
that
is not necessarily in the list?
Example - the list would include city names and possibly the
corresponding
code, but I would like it to return only the corresponding
three
digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data
Validation
feature
in
Excel, and I am more than willing to try it via VB, but I'm not
familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

Hi Tom

I've a similar problem. The difference is that I get the data from a
sql server database as XML and I'm able to bind the data to the
dropdown using addItem method. But I cant add the value fro the same.
can you help me in this regard?

Thanks
Jignesh