Using Drop Down List or Combo Box
 

I not sure how to do this - if I can use Data Validation with a Drop Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter in column
1 to show in the cell after selection. However, I want the user to see the
descriptive text also when holding down the selection arrow. The list will
be typed in a hidden area of the worksheet and will be used for drop downs in
3 other sheets in the workbook. These identify expenses, and will be in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

Hi Dana,
Check Debra web

http://contextures.com/xlUserForm02.html

"Dana M" wrote:


I not sure how to do this - if I can use Data Validation with a Drop Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter in column
1 to show in the cell after selection. However, I want the user to see the
descriptive text also when holding down the selection arrow. The list will
be typed in a hidden area of the worksheet and will be used for drop downs in
3 other sheets in the workbook. These identify expenses, and will be in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

There is a sample file here that does what you want:

http://contextures.com/excelfiles.html#DataVal

Look for DV0004 - Data Validation Change

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...

I not sure how to do this - if I can use Data Validation with a Drop Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter in
column
1 to show in the cell after selection. However, I want the user to see
the
descriptive text also when holding down the selection arrow. The list
will
be typed in a hidden area of the worksheet and will be used for drop downs
in
3 other sheets in the workbook. These identify expenses, and will be in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

Wow, thank you - this is a very interesting procedure - but probably more
complex than I need or can follow. I'm not creating a database - more like
identifying expense lines on a spreadsheet.

"Eduardo" wrote:

Hi Dana,
Check Debra web

http://contextures.com/xlUserForm02.html

"Dana M" wrote:


I not sure how to do this - if I can use Data Validation with a Drop Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter in column
1 to show in the cell after selection. However, I want the user to see the
descriptive text also when holding down the selection arrow. The list will
be typed in a hidden area of the worksheet and will be used for drop downs in
3 other sheets in the workbook. These identify expenses, and will be in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

I think this is very close to what I want. However, I can't make it work. I
copied the code into a VBE module in my workbook. My workbook is a template
for budget analysts. It has several sheets with other drop down boxes and a
lot of underlying macros. However the requirements for this set of drop down
boxes was different, in that the request was to capture the letter "code" in
the cell, but show the Expense Type Name when the box was selected, as in
your suggested solution.

The underlying code in the solution is:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Since my drop down boxes are in column "C", I changed
If Target.Column = 2 Then TO
If Target.Column = 3 Then

Do I need to assign a macro to the drop down boxes, or how do I activate the
code? The instructions seem to indicate that when I make a selection, it
triggers an event - that's not happening. After I select the Expense Type,
it shows the Expense Type, not the Expense ID.
"T. Valko" wrote:

There is a sample file here that does what you want:

http://contextures.com/excelfiles.html#DataVal

Look for DV0004 - Data Validation Change

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...

I not sure how to do this - if I can use Data Validation with a Drop Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter in
column
1 to show in the cell after selection. However, I want the user to see
the
descriptive text also when holding down the selection arrow. The list
will
be typed in a hidden area of the worksheet and will be used for drop downs
in
3 other sheets in the workbook. These identify expenses, and will be in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

That is a worksheet event macro. It doesn't go into a general module. Select
the sheet where you want this happen. Right click the sheet tab and select
View code. Paste the code into the window that opens.

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
I think this is very close to what I want. However, I can't make it work.
I
copied the code into a VBE module in my workbook. My workbook is a
template
for budget analysts. It has several sheets with other drop down boxes and
a
lot of underlying macros. However the requirements for this set of drop
down
boxes was different, in that the request was to capture the letter "code"
in
the cell, but show the Expense Type Name when the box was selected, as in
your suggested solution.

The underlying code in the solution is:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Since my drop down boxes are in column "C", I changed
If Target.Column = 2 Then TO
If Target.Column = 3 Then

Do I need to assign a macro to the drop down boxes, or how do I activate
the
code? The instructions seem to indicate that when I make a selection, it
triggers an event - that's not happening. After I select the Expense
Type,
it shows the Expense Type, not the Expense ID.
"T. Valko" wrote:

There is a sample file here that does what you want:

http://contextures.com/excelfiles.html#DataVal

Look for DV0004 - Data Validation Change

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...

I not sure how to do this - if I can use Data Validation with a Drop
Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter in
column
1 to show in the cell after selection. However, I want the user to see
the
descriptive text also when holding down the selection arrow. The list
will
be typed in a hidden area of the worksheet and will be used for drop
downs
in
3 other sheets in the workbook. These identify expenses, and will be
in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

OK, thanks. I will need it to work on several worksheets. So will it be OK
to paste the code into each of the sheets?

"T. Valko" wrote:

That is a worksheet event macro. It doesn't go into a general module. Select
the sheet where you want this happen. Right click the sheet tab and select
View code. Paste the code into the window that opens.

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
I think this is very close to what I want. However, I can't make it work.
I
copied the code into a VBE module in my workbook. My workbook is a
template
for budget analysts. It has several sheets with other drop down boxes and
a
lot of underlying macros. However the requirements for this set of drop
down
boxes was different, in that the request was to capture the letter "code"
in
the cell, but show the Expense Type Name when the box was selected, as in
your suggested solution.

The underlying code in the solution is:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Since my drop down boxes are in column "C", I changed
If Target.Column = 2 Then TO
If Target.Column = 3 Then

Do I need to assign a macro to the drop down boxes, or how do I activate
the
code? The instructions seem to indicate that when I make a selection, it
triggers an event - that's not happening. After I select the Expense
Type,
it shows the Expense Type, not the Expense ID.
"T. Valko" wrote:

There is a sample file here that does what you want:

http://contextures.com/excelfiles.html#DataVal

Look for DV0004 - Data Validation Change

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...

I not sure how to do this - if I can use Data Validation with a Drop
Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter in
column
1 to show in the cell after selection. However, I want the user to see
the
descriptive text also when holding down the selection arrow. The list
will
be typed in a hidden area of the worksheet and will be used for drop
downs
in
3 other sheets in the workbook. These identify expenses, and will be
in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

It should be ok.

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
OK, thanks. I will need it to work on several worksheets. So will it be
OK
to paste the code into each of the sheets?

"T. Valko" wrote:

That is a worksheet event macro. It doesn't go into a general module.
Select
the sheet where you want this happen. Right click the sheet tab and
select
View code. Paste the code into the window that opens.

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
I think this is very close to what I want. However, I can't make it
work.
I
copied the code into a VBE module in my workbook. My workbook is a
template
for budget analysts. It has several sheets with other drop down boxes
and
a
lot of underlying macros. However the requirements for this set of
drop
down
boxes was different, in that the request was to capture the letter
"code"
in
the cell, but show the Expense Type Name when the box was selected, as
in
your suggested solution.

The underlying code in the solution is:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0),
0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Since my drop down boxes are in column "C", I changed
If Target.Column = 2 Then TO
If Target.Column = 3 Then

Do I need to assign a macro to the drop down boxes, or how do I
activate
the
code? The instructions seem to indicate that when I make a selection,
it
triggers an event - that's not happening. After I select the Expense
Type,
it shows the Expense Type, not the Expense ID.
"T. Valko" wrote:

There is a sample file here that does what you want:

http://contextures.com/excelfiles.html#DataVal

Look for DV0004 - Data Validation Change

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...

I not sure how to do this - if I can use Data Validation with a Drop
Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter
in
column
1 to show in the cell after selection. However, I want the user to
see
the
descriptive text also when holding down the selection arrow. The
list
will
be typed in a hidden area of the worksheet and will be used for drop
downs
in
3 other sheets in the workbook. These identify expenses, and will
be
in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

Thanks so very much for a helpful solution and follow-up answers.

"T. Valko" wrote:

It should be ok.

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
OK, thanks. I will need it to work on several worksheets. So will it be
OK
to paste the code into each of the sheets?

"T. Valko" wrote:

That is a worksheet event macro. It doesn't go into a general module.
Select
the sheet where you want this happen. Right click the sheet tab and
select
View code. Paste the code into the window that opens.

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
I think this is very close to what I want. However, I can't make it
work.
I
copied the code into a VBE module in my workbook. My workbook is a
template
for budget analysts. It has several sheets with other drop down boxes
and
a
lot of underlying macros. However the requirements for this set of
drop
down
boxes was different, in that the request was to capture the letter
"code"
in
the cell, but show the Expense Type Name when the box was selected, as
in
your suggested solution.

The underlying code in the solution is:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0),
0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Since my drop down boxes are in column "C", I changed
If Target.Column = 2 Then TO
If Target.Column = 3 Then

Do I need to assign a macro to the drop down boxes, or how do I
activate
the
code? The instructions seem to indicate that when I make a selection,
it
triggers an event - that's not happening. After I select the Expense
Type,
it shows the Expense Type, not the Expense ID.
"T. Valko" wrote:

There is a sample file here that does what you want:

http://contextures.com/excelfiles.html#DataVal

Look for DV0004 - Data Validation Change

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...

I not sure how to do this - if I can use Data Validation with a Drop
Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter
in
column
1 to show in the cell after selection. However, I want the user to
see
the
descriptive text also when holding down the selection arrow. The
list
will
be typed in a hidden area of the worksheet and will be used for drop
downs
in
3 other sheets in the workbook. These identify expenses, and will
be
in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

Using Drop Down List or Combo Box
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
Thanks so very much for a helpful solution and follow-up answers.

"T. Valko" wrote:

It should be ok.

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
OK, thanks. I will need it to work on several worksheets. So will it
be
OK
to paste the code into each of the sheets?

"T. Valko" wrote:

That is a worksheet event macro. It doesn't go into a general module.
Select
the sheet where you want this happen. Right click the sheet tab and
select
View code. Paste the code into the window that opens.

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...
I think this is very close to what I want. However, I can't make it
work.
I
copied the code into a VBE module in my workbook. My workbook is a
template
for budget analysts. It has several sheets with other drop down
boxes
and
a
lot of underlying macros. However the requirements for this set of
drop
down
boxes was different, in that the request was to capture the letter
"code"
in
the cell, but show the Expense Type Name when the box was selected,
as
in
your suggested solution.

The underlying code in the solution is:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"),
0),
0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Since my drop down boxes are in column "C", I changed
If Target.Column = 2 Then TO
If Target.Column = 3 Then

Do I need to assign a macro to the drop down boxes, or how do I
activate
the
code? The instructions seem to indicate that when I make a
selection,
it
triggers an event - that's not happening. After I select the
Expense
Type,
it shows the Expense Type, not the Expense ID.
"T. Valko" wrote:

There is a sample file here that does what you want:

http://contextures.com/excelfiles.html#DataVal

Look for DV0004 - Data Validation Change

--
Biff
Microsoft Excel MVP


"Dana M" wrote in message
...

I not sure how to do this - if I can use Data Validation with a
Drop
Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying
letter
in
column
1 to show in the cell after selection. However, I want the user
to
see
the
descriptive text also when holding down the selection arrow. The
list
will
be typed in a hidden area of the worksheet and will be used for
drop
downs
in
3 other sheets in the workbook. These identify expenses, and
will
be
in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related