Drop Down Menu autofilling
 

I can make a drop down menu just fine,. My problem is that I want the choice
for the drop down menu to then fill in something other than what the choice
is called. For instance, one of the choices could be Physics and when
selected, instead of filling in Physics, it fills in P310. I swear that I
have done this before with Data Validation, but I don't remember how. Thank
you for your time.

Drop Down Menu autofilling
 

You can use programming to do this. There's a sample file here that
fills in a product code, after a product is selected. You could adapt
the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Aaron wrote:
I can make a drop down menu just fine,. My problem is that I want the choice
for the drop down menu to then fill in something other than what the choice
is called. For instance, one of the choices could be Physics and when
selected, instead of filling in Physics, it fills in P310. I swear that I
have done this before with Data Validation, but I don't remember how. Thank
you for your time.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down Menu autofilling
 

I am not very familiar with programing at all. I am sorry to waste your time,
but could explain the terms in the code? If you don't have the time I can
wander down to our computer science department, someone there should be able
to help me if you can't. Thank you though, I do appreciate your help.

"Debra Dalgleish" wrote:

You can use programming to do this. There's a sample file here that
fills in a product code, after a product is selected. You could adapt
the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Aaron wrote:
I can make a drop down menu just fine,. My problem is that I want the choice
for the drop down menu to then fill in something other than what the choice
is called. For instance, one of the choices could be Physics and when
selected, instead of filling in Physics, it fills in P310. I swear that I
have done this before with Data Validation, but I don't remember how. Thank
you for your time.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down Menu autofilling
 

The code is in the Worksheet_Change event procedure, so it runs when
something on this worksheet changes, such as selecting an item from one
of the data validation dropdowns in column B.
Target is the cell, or cells, that have been changed.

First, the code counts the number of cells selected, and stops running
if more than one cell is selected.
If Target.Cells.Count 1 Then GoTo exitHandler

Next, the code checks which column the Target cell is in. If the column
number is 2 (column B), then the rest of the code runs.
If Target.Column = 2 Then

Next, it checks the value entered in the Target cell. If the value is ""
(an empty string, which means no value), then the code stops running.
If Target.Value = "" Then GoTo exitHandler

Next EnableEvents is turned off, so no other code will be triggered by
this code.
Application.EnableEvents = False

Next, the value in the Target cell is changed to the product code that
matches the selected product name. ProdList is a named range on the
Codes sheet.
The Match function is used to find the selected product in that range,
and return a number. For example, Product A, would return a 1, because
it's the first item. That number is used in the Offset property.
In this example, the Offset property finds the value in the cell, that's
1 row down, and zero columns over from cell A1 on the Codes sheet.

Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)

Finally, EnableEvents is turned on, so the events will continue to work
in Excel.
Application.EnableEvents = True

Aaron wrote:
I am not very familiar with programing at all. I am sorry to waste your time,
but could explain the terms in the code? If you don't have the time I can
wander down to our computer science department, someone there should be able
to help me if you can't. Thank you though, I do appreciate your help.

"Debra Dalgleish" wrote:


You can use programming to do this. There's a sample file here that
fills in a product code, after a product is selected. You could adapt
the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Aaron wrote:

I can make a drop down menu just fine,. My problem is that I want the choice
for the drop down menu to then fill in something other than what the choice
is called. For instance, one of the choices could be Physics and when
selected, instead of filling in Physics, it fills in P310. I swear that I
have done this before with Data Validation, but I don't remember how. Thank
you for your time.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down Menu autofilling
 

Ok, I think I have a handle on the code being use. The main purpose of the
first two are safety mechanisms to make use the code does not activate
accidentally. I think that it is these that am having trouble with, but not
sure. The target cells is one that has been merged and centered and takes up
20 columns. I think that this voids both mechanisms even with changing the
column code to the appropriate one (EO = 171 if I am right). This being said,
I tried deleting them both and still nothing happened so it might be a
different issue. Thank you for your continued help on this!

"Debra Dalgleish" wrote:

The code is in the Worksheet_Change event procedure, so it runs when
something on this worksheet changes, such as selecting an item from one
of the data validation dropdowns in column B.
Target is the cell, or cells, that have been changed.

First, the code counts the number of cells selected, and stops running
if more than one cell is selected.
If Target.Cells.Count 1 Then GoTo exitHandler

Next, the code checks which column the Target cell is in. If the column
number is 2 (column B), then the rest of the code runs.
If Target.Column = 2 Then

Next, it checks the value entered in the Target cell. If the value is ""
(an empty string, which means no value), then the code stops running.
If Target.Value = "" Then GoTo exitHandler

Next EnableEvents is turned off, so no other code will be triggered by
this code.
Application.EnableEvents = False

Next, the value in the Target cell is changed to the product code that
matches the selected product name. ProdList is a named range on the
Codes sheet.
The Match function is used to find the selected product in that range,
and return a number. For example, Product A, would return a 1, because
it's the first item. That number is used in the Offset property.
In this example, the Offset property finds the value in the cell, that's
1 row down, and zero columns over from cell A1 on the Codes sheet.

Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)

Finally, EnableEvents is turned on, so the events will continue to work
in Excel.
Application.EnableEvents = True

Aaron wrote:
I am not very familiar with programing at all. I am sorry to waste your time,
but could explain the terms in the code? If you don't have the time I can
wander down to our computer science department, someone there should be able
to help me if you can't. Thank you though, I do appreciate your help.

"Debra Dalgleish" wrote:


You can use programming to do this. There's a sample file here that
fills in a product code, after a product is selected. You could adapt
the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Aaron wrote:

I can make a drop down menu just fine,. My problem is that I want the choice
for the drop down menu to then fill in something other than what the choice
is called. For instance, one of the choices could be Physics and when
selected, instead of filling in Physics, it fills in P310. I swear that I
have done this before with Data Validation, but I don't remember how. Thank
you for your time.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down Menu autofilling
 

EO is column 145, so if you change that it should work.

Aaron wrote:
Ok, I think I have a handle on the code being use. The main purpose of the
first two are safety mechanisms to make use the code does not activate
accidentally. I think that it is these that am having trouble with, but not
sure. The target cells is one that has been merged and centered and takes up
20 columns. I think that this voids both mechanisms even with changing the
column code to the appropriate one (EO = 171 if I am right). This being said,
I tried deleting them both and still nothing happened so it might be a
different issue. Thank you for your continued help on this!

"Debra Dalgleish" wrote:


The code is in the Worksheet_Change event procedure, so it runs when
something on this worksheet changes, such as selecting an item from one
of the data validation dropdowns in column B.
Target is the cell, or cells, that have been changed.

First, the code counts the number of cells selected, and stops running
if more than one cell is selected.
If Target.Cells.Count 1 Then GoTo exitHandler

Next, the code checks which column the Target cell is in. If the column
number is 2 (column B), then the rest of the code runs.
If Target.Column = 2 Then

Next, it checks the value entered in the Target cell. If the value is ""
(an empty string, which means no value), then the code stops running.
If Target.Value = "" Then GoTo exitHandler

Next EnableEvents is turned off, so no other code will be triggered by
this code.
Application.EnableEvents = False

Next, the value in the Target cell is changed to the product code that
matches the selected product name. ProdList is a named range on the
Codes sheet.
The Match function is used to find the selected product in that range,
and return a number. For example, Product A, would return a 1, because
it's the first item. That number is used in the Offset property.
In this example, the Offset property finds the value in the cell, that's
1 row down, and zero columns over from cell A1 on the Codes sheet.

Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)

Finally, EnableEvents is turned on, so the events will continue to work
in Excel.
Application.EnableEvents = True

Aaron wrote:

I am not very familiar with programing at all. I am sorry to waste your time,
but could explain the terms in the code? If you don't have the time I can
wander down to our computer science department, someone there should be able
to help me if you can't. Thank you though, I do appreciate your help.

"Debra Dalgleish" wrote:



You can use programming to do this. There's a sample file here that
fills in a product code, after a product is selected. You could adapt
the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Aaron wrote:


I can make a drop down menu just fine,. My problem is that I want the choice
for the drop down menu to then fill in something other than what the choice
is called. For instance, one of the choices could be Physics and when
selected, instead of filling in Physics, it fills in P310. I swear that I
have done this before with Data Validation, but I don't remember how. Thank
you for your time.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down Menu autofilling
 

In addition, to assist in future with figuring out colomn letters and numbers,
here are a couple of Functions.

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=GetColNum("EO") returns 145

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function

=GetCollet(145) returns EO


Gord Dibben MS Excel MVP

On Sun, 30 Mar 2008 16:01:49 -0400, Debra Dalgleish
wrote:

EO is column 145, so if you change that it should work.

Aaron wrote:
Ok, I think I have a handle on the code being use. The main purpose of the
first two are safety mechanisms to make use the code does not activate
accidentally. I think that it is these that am having trouble with, but not
sure. The target cells is one that has been merged and centered and takes up
20 columns. I think that this voids both mechanisms even with changing the
column code to the appropriate one (EO = 171 if I am right). This being said,
I tried deleting them both and still nothing happened so it might be a
different issue. Thank you for your continued help on this!

"Debra Dalgleish" wrote:


The code is in the Worksheet_Change event procedure, so it runs when
something on this worksheet changes, such as selecting an item from one
of the data validation dropdowns in column B.
Target is the cell, or cells, that have been changed.

First, the code counts the number of cells selected, and stops running
if more than one cell is selected.
If Target.Cells.Count 1 Then GoTo exitHandler

Next, the code checks which column the Target cell is in. If the column
number is 2 (column B), then the rest of the code runs.
If Target.Column = 2 Then

Next, it checks the value entered in the Target cell. If the value is ""
(an empty string, which means no value), then the code stops running.
If Target.Value = "" Then GoTo exitHandler

Next EnableEvents is turned off, so no other code will be triggered by
this code.
Application.EnableEvents = False

Next, the value in the Target cell is changed to the product code that
matches the selected product name. ProdList is a named range on the
Codes sheet.
The Match function is used to find the selected product in that range,
and return a number. For example, Product A, would return a 1, because
it's the first item. That number is used in the Offset property.
In this example, the Offset property finds the value in the cell, that's
1 row down, and zero columns over from cell A1 on the Codes sheet.

Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)

Finally, EnableEvents is turned on, so the events will continue to work
in Excel.
Application.EnableEvents = True

Aaron wrote:

I am not very familiar with programing at all. I am sorry to waste your time,
but could explain the terms in the code? If you don't have the time I can
wander down to our computer science department, someone there should be able
to help me if you can't. Thank you though, I do appreciate your help.

"Debra Dalgleish" wrote:



You can use programming to do this. There's a sample file here that
fills in a product code, after a product is selected. You could adapt
the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Aaron wrote:


I can make a drop down menu just fine,. My problem is that I want the choice
for the drop down menu to then fill in something other than what the choice
is called. For instance, one of the choices could be Physics and when
selected, instead of filling in Physics, it fills in P310. I swear that I
have done this before with Data Validation, but I don't remember how. Thank
you for your time.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html