Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop-down list(or combo-box) for the cell | Excel Discussion (Misc queries) | |||
How do i use a combo box or drop down list to preform a calculatio | Excel Worksheet Functions | |||
Drop Down List Box, Combo Box | Excel Discussion (Misc queries) | |||
accessing graphics with combo box or drop down list | Excel Discussion (Misc queries) | |||
Drop down list or combo box help needed | Excel Discussion (Misc queries) |