Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
Use two cells:
The first cell will have a data validation pull-down to enter the text. The second cell will have a VLOOKUP() formula to lookup the correct number. -- Gary''s Student - gsnu2007j "Pete" wrote: is it possible to choose an item from a dropdown list which will enter a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 24 Jul, 18:11, Gary''s Student
wrote: Use two cells: The first cell will have a data validation pull-down to enter the text. The second cell will have a VLOOKUP() formula to lookup the correct number. -- Gary''s Student - gsnu2007j "Pete" wrote: is it possible to choose an item from a dropdown list which will enter a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete- Hide quoted text - - Show quoted text - Sheets is already designed, I only have one Cell to work with |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
Just use a LEFT(Cell Ref,1) to get the number
-- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Pete" wrote: On 24 Jul, 18:11, Gary''s Student wrote: Use two cells: The first cell will have a data validation pull-down to enter the text. The second cell will have a VLOOKUP() formula to lookup the correct number. -- Gary''s Student - gsnu2007j "Pete" wrote: is it possible to choose an item from a dropdown list which will enter a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete- Hide quoted text - - Show quoted text - Sheets is already designed, I only have one Cell to work with |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 24 Jul, 18:22, M Kan <tipsoftheweek at gmail dot com wrote:
Just use a LEFT(Cell Ref,1) to get the number -- Tips for Excel, Word, PowerPoint and Other Applicationshttp://www.kan.org/tips "Pete" wrote: On 24 Jul, 18:11, Gary''s Student wrote: Use two cells: The first cell will have a data validation pull-down to enter the text. The second cell will have a VLOOKUP() formula to lookup the correct number. -- Gary''s Student - gsnu2007j "Pete" wrote: is it possible to choose an item from a dropdown list which will enter a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete- Hide quoted text - - Show quoted text - Sheets is already designed, I only have one Cell to work with- Hide quoted text - - Show quoted text - I need to use a drop down list, I don't think I can use the Left(Cell Ref,1) command. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
You could use the Combo Box form tool. That would let you present a list but
output a number. Other than that, I'm not sure how to achieve what you want. http://www.kan.org/tips/excel_forms_tools.php -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Pete" wrote: On 24 Jul, 18:22, M Kan <tipsoftheweek at gmail dot com wrote: Just use a LEFT(Cell Ref,1) to get the number -- Tips for Excel, Word, PowerPoint and Other Applicationshttp://www.kan.org/tips "Pete" wrote: On 24 Jul, 18:11, Gary''s Student wrote: Use two cells: The first cell will have a data validation pull-down to enter the text. The second cell will have a VLOOKUP() formula to lookup the correct number. -- Gary''s Student - gsnu2007j "Pete" wrote: is it possible to choose an item from a dropdown list which will enter a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete- Hide quoted text - - Show quoted text - Sheets is already designed, I only have one Cell to work with- Hide quoted text - - Show quoted text - I need to use a drop down list, I don't think I can use the Left(Cell Ref,1) command. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
To enter the number in the same cell use this example sheet event code.
Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1") 'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) 'add more numbers For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then rr.Value = ival End If Next End Sub Gord Dibben MS Excel MVP On Thu, 24 Jul 2008 09:53:16 -0700 (PDT), Pete wrote: is it possible to choose an item from a dropdown list which will enter a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 24 Jul, 20:22, Gord Dibben <gorddibbATshawDOTca wrote:
To enter the number in the same cell use this example sheet event code. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1") *'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then * * Exit Sub End If vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers For Each rr In r * * ival = 0 * * For i = LBound(vals) To UBound(vals) * * * * If UCase(rr.Value) = vals(i) Then * * * * * * ival = nums(i) * * * * End If * * Next * * If ival 0 Then * * rr.Value = ival * * End If Next End Sub Gord Dibben *MS Excel MVP On Thu, 24 Jul 2008 09:53:16 -0700 (PDT), Pete wrote: is it possible to choose an item from a dropdown list which will enter a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete- Hide quoted text - - Show quoted text - Gord, you are THE man, perfect solution, thank you |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 24 Jul, 20:22, Gord Dibben <gorddibbATshawDOTca wrote:
To enter the number in the same cell use this example sheet event code. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1") *'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then * * Exit Sub End If vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers For Each rr In r * * ival = 0 * * For i = LBound(vals) To UBound(vals) * * * * If UCase(rr.Value) = vals(i) Then * * * * * * ival = nums(i) * * * * End If * * Next * * If ival 0 Then * * rr.Value = ival * * End If Next End Sub Gord Dibben *MS Excel MVP On Thu, 24 Jul 2008 09:53:16 -0700 (PDT), Pete wrote: is it possible to choose an item from a dropdown list which will enter a different value other than that chosen. Why I want to do this is as follows: We have 20 steps in our Process and each one is assigned a number e.g 1 = Forming 2 = Drying 3 = Paper 4 = Cutter etc etc I would like to display the Text in a Drop down for that Cell but enter the corresponding Number into the Cell. If I chose Drying I would like to see the number 2 appear in the Cell, or if I chose Cutter I would like the Number 4 to be entered in the Cell. Thanks Pete- Hide quoted text - - Show quoted text - Gord, A similar problem, I would like to enter a value in the C11 and convert it to another after the Value has been entered. E.g If I enter 100 in C11 I would like to Divide it by the Value I have in C17 under the Change Event Procedure. I would need to do this in the full Range e.g Divide E11 by E17 and G11 by G17 etc etc. upto AK11/AK17 CAn yuo show me how to adapt your previous code to do this too? Thanks Pete |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit Dim cell As Range On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < "" Then .Value = .Value * .Offset(6, 0).Value End If End With End If ws_exit: Application.EnableEvents = True End Sub Gord On Fri, 25 Jul 2008 11:58:53 -0700 (PDT), Pete wrote: A similar problem, I would like to enter a value in the C11 and convert it to another after the Value has been entered. E.g If I enter 100 in C11 I would like to Divide it by the Value I have in C17 under the Change Event Procedure. I would need to do this in the full Range e.g Divide E11 by E17 and G11 by G17 etc etc. upto AK11/AK17 CAn yuo show me how to adapt your previous code to do this too? Thanks Pete |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 26 Jul, 00:15, Gord Dibben <gorddibbATshawDOTca wrote:
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" * * 'add cells to suit Dim cell As Range * * On Error GoTo ws_exit * * Application.EnableEvents = False * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * With Target * * * * If .Value < "" Then * * * * * * .Value = .Value * .Offset(6, 0).Value * * * * * * End If * * * * End With * * End If ws_exit: * * Application.EnableEvents = True End Sub Gord On Fri, 25 Jul 2008 11:58:53 -0700 (PDT), Pete wrote: A similar problem, I would like to enter a value in the C11 and convert it to another after the Value has been entered. E.g If I enter 100 in C11 I would like to Divide it by the Value I have in C17 under the Change Event Procedure. I would need to do this in the full Range e.g Divide E11 by E17 and G11 by G17 etc etc. upto AK11/AK17 CAn yuo show me how to adapt your previous code to do this too? Thanks Pete- Hide quoted text - - Show quoted text - Thanks Gord, I will give it a try. I now have pieces of code in the Change Sub and they seem to affect each other. Can this piece of code you've just done for me work along side the earlier one you did? Pete |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 26 Jul, 21:13, Pete wrote:
On 26 Jul, 00:15, Gord Dibben <gorddibbATshawDOTca wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" * * 'add cells to suit Dim cell As Range * * On Error GoTo ws_exit * * Application.EnableEvents = False * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * With Target * * * * If .Value < "" Then * * * * * * .Value = .Value * .Offset(6, 0).Value * * * * * * End If * * * * End With * * End If ws_exit: * * Application.EnableEvents = True End Sub Gord On Fri, 25 Jul 2008 11:58:53 -0700 (PDT), Pete wrote: A similar problem, I would like to enter a value in the C11 and convert it to another after the Value has been entered. E.g If I enter 100 in C11 I would like to Divide it by the Value I have in C17 under the Change Event Procedure. I would need to do this in the full Range e.g Divide E11 by E17 and G11 by G17 etc etc. upto AK11/AK17 CAn yuo show me how to adapt your previous code to do this too? Thanks Pete- Hide quoted text - - Show quoted text - Thanks Gord, I will give it a try. I now have pieces of code in the Change Sub and they seem to affect each other. Can this piece of code you've just done for me work along side the earlier one you did? Pete- Hide quoted text - - Show quoted text - Gord, perfect again exacly what I want. If I post all the code I have in the Change Event sub, could you tidy it up for me so it works properly. I can get the ones you have done for me to work seperately, but when they are both in only the first one works. I also have Chip Pearson's Quick Time Entry code in there to and I could od with all 3 working. thanks in advance Pete |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
I am surprised you are not getting error messages about "ambiguous name
detected" More than one Worksheet_Chamge event in a sheet is not allowed. There are ways to work around it but I'm not the guy to be talking to. Browse through Chip's pages on Events to get a better idea of what's available. http://www.cpearson.com/excel/Events.aspx And check out David McRitchie's site http://www.mvps.org/dmcritchie/excel/event.htm Gord On Sat, 26 Jul 2008 13:29:01 -0700 (PDT), Pete wrote: Gord, perfect again exacly what I want. If I post all the code I have in the Change Event sub, could you tidy it up for me so it works properly. I can get the ones you have done for me to work seperately, but when they are both in only the first one works. I also have Chip Pearson's Quick Time Entry code in there to and I could od with all 3 working. |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 27 Jul, 01:34, Gord Dibben <gorddibbATshawDOTca wrote:
I am surprised you are not getting error messages about "ambiguous name detected" More than one Worksheet_Chamge event in a sheet is not allowed. There are ways to work around it but I'm not the guy to be talking to. Browse through Chip's pages on Events to get a better idea of what's available. http://www.cpearson.com/excel/Events.aspx And check out David McRitchie's site http://www.mvps.org/dmcritchie/excel/event.htm Gord On Sat, 26 Jul 2008 13:29:01 -0700 (PDT), Pete wrote: Gord, perfect again exacly what I want. If I post all the code I have in the Change Event sub, could you tidy it up for me so it works properly. I can get the ones you have done for me to work seperately, but when they are both in only the first one works. I also have Chip Pearson's Quick Time Entry code in there to and I could od with all 3 working.- Hide quoted text - - Show quoted text - I only have the one Worksheet_Change Event Sub but would like the 2 pieces of code you have done for me to be in it. Could you combine the 2 as below as the way I have it I think the first exit's before the second has been done. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit Dim cell As Range On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < "" Then .Value = .Value * .Offset(6, 0).Value End If End With End If ws_exit: Application.EnableEvents = True Set r = Range("A1") 'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) 'add more numbers For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then rr.Value = ival End If Next End Sub Pete |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
A bit cumbersome but can be done thisaway
Place these two events in the worksheet..........can have more than one if events are different. After you select an item from A1 dropdown just double-click on A1 to effect the change. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("A1") 'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) 'add more numbers For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then rr.Value = ival End If Cancel = True Next End Sub Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit Dim cell As Range On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < "" Then .Value = .Value * .Offset(6, 0).Value End If End With End If ws_exit: Application.EnableEvents = True End Sub Gord On Sun, 27 Jul 2008 00:48:32 -0700 (PDT), Pete wrote: I only have the one Worksheet_Change Event Sub but would like the 2 pieces of code you have done for me to be in it. Could you combine the 2 as below as the way I have it I think the first exit's before the second has been done. |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 27 Jul, 16:27, Gord Dibben <gorddibbATshawDOTca wrote:
A bit cumbersome but can be done thisaway Place these two events in the worksheet..........can have more than one if events are different. After you select an item from A1 dropdown just double-click on A1 to effect the change. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("A1") *'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then * * Exit Sub End If vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers For Each rr In r * * ival = 0 * * For i = LBound(vals) To UBound(vals) * * * * If UCase(rr.Value) = vals(i) Then * * * * * * ival = nums(i) * * * * End If * * Next * * If ival 0 Then * * rr.Value = ival * * End If * * Cancel = True Next End Sub Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit Dim cell As Range * * On Error GoTo ws_exit * * Application.EnableEvents = False * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * With Target * * * * If .Value < "" Then * * * * * * .Value = .Value * .Offset(6, 0).Value * * * * * * End If * * * * End With * * End If ws_exit: * * Application.EnableEvents = True End Sub Gord On Sun, 27 Jul 2008 00:48:32 -0700 (PDT), Pete wrote: I only have the one Worksheet_Change Event Sub but would like the 2 pieces of code you have done for me to be in it. Could you combine the 2 as below as the way I have it I think the first exit's before the second has been done.- Hide quoted text - - Show quoted text - Thanks Gord for the time you have put in on this so far. I take it then, that you can't just do an If Then Else depending on the Range the Cell is currently in? Peter |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
Can probably combine both into one change event.
I will work on it later today. Gord On Thu, 31 Jul 2008 03:03:49 -0700 (PDT), Pete wrote: On 27 Jul, 16:27, Gord Dibben <gorddibbATshawDOTca wrote: A bit cumbersome but can be done thisaway Place these two events in the worksheet..........can have more than one if events are different. After you select an item from A1 dropdown just double-click on A1 to effect the change. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("A1") *'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then * * Exit Sub End If vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers For Each rr In r * * ival = 0 * * For i = LBound(vals) To UBound(vals) * * * * If UCase(rr.Value) = vals(i) Then * * * * * * ival = nums(i) * * * * End If * * Next * * If ival 0 Then * * rr.Value = ival * * End If * * Cancel = True Next End Sub Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit Dim cell As Range * * On Error GoTo ws_exit * * Application.EnableEvents = False * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * With Target * * * * If .Value < "" Then * * * * * * .Value = .Value * .Offset(6, 0).Value * * * * * * End If * * * * End With * * End If ws_exit: * * Application.EnableEvents = True End Sub Gord On Sun, 27 Jul 2008 00:48:32 -0700 (PDT), Pete wrote: I only have the one Worksheet_Change Event Sub but would like the 2 pieces of code you have done for me to be in it. Could you combine the 2 as below as the way I have it I think the first exit's before the second has been done.- Hide quoted text - - Show quoted text - Thanks Gord for the time you have put in on this so far. I take it then, that you can't just do an If Then Else depending on the Range the Cell is currently in? Peter |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 31 Jul, 17:25, Gord Dibben <gorddibbATshawDOTca wrote:
Can probably combine both into one change event. I will work on it later today. Gord On Thu, 31 Jul 2008 03:03:49 -0700 (PDT), Pete wrote: On 27 Jul, 16:27, Gord Dibben <gorddibbATshawDOTca wrote: A bit cumbersome but can be done thisaway Place these two events in the worksheet..........can have more than one if events are different. After you select an item from A1 dropdown just double-click on A1 to effect the change. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("A1") *'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then * * Exit Sub End If vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers For Each rr In r * * ival = 0 * * For i = LBound(vals) To UBound(vals) * * * * If UCase(rr.Value) = vals(i) Then * * * * * * ival = nums(i) * * * * End If * * Next * * If ival 0 Then * * rr.Value = ival * * End If * * Cancel = True Next End Sub Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit Dim cell As Range * * On Error GoTo ws_exit * * Application.EnableEvents = False * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * With Target * * * * If .Value < "" Then * * * * * * .Value = .Value * .Offset(6, 0).Value * * * * * * End If * * * * End With * * End If ws_exit: * * Application.EnableEvents = True End Sub Gord On Sun, 27 Jul 2008 00:48:32 -0700 (PDT), Pete wrote: I only have the one Worksheet_Change Event Sub but would like the 2 pieces of code you have done for me to be in it. Could you combine the 2 as below as the way I have it I think the first exit's before the second has been done.- Hide quoted text - - Show quoted text - Thanks Gord for the time you have put in on this so far. I take it then, that you can't just do an If Then Else depending on the Range the Cell is currently in? Peter- Hide quoted text - - Show quoted text - many thanks Gord, I look forward to seeing your code Peter |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
I think this is it.
Tested on both the DV drowdown selection and the multiply by Offset(6, 0) codition. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit Dim cell As Range On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < "" Then .Value = .Value * .Offset(6, 0).Value End If End With End If Application.EnableEvents = True Set r = Range("A1") 'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then Exit Sub End If Application.EnableEvents = False vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) 'add more numbers For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then rr.Value = ival End If Next ws_exit: Application.EnableEvents = True End Sub Gord On Fri, 1 Aug 2008 14:15:00 -0700 (PDT), Pete wrote: many thanks Gord, I look forward to seeing your code Peter |
#20
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 1 Aug, 22:59, Gord Dibben <gorddibbATshawDOTca wrote:
I think this is it. Tested on both the DV drowdown selection and the multiply by Offset(6, 0) codition. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" * * 'add cells to suit Dim cell As Range * * On Error GoTo ws_exit * * Application.EnableEvents = False * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * With Target * * * * If .Value < "" Then * * * * * * .Value = .Value * .Offset(6, 0).Value * * * * * * End If * * * * End With * * End If * * Application.EnableEvents = True Set r = Range("A1") * * *'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then * * Exit Sub End If Application.EnableEvents = False vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers For Each rr In r * * ival = 0 * * For i = LBound(vals) To UBound(vals) * * * * If UCase(rr.Value) = vals(i) Then * * * * * * ival = nums(i) * * * * End If * * Next * * If ival 0 Then * * rr.Value = ival * * End If Next ws_exit: * * Application.EnableEvents = True End Sub Gord On Fri, 1 Aug 2008 14:15:00 -0700 (PDT), Pete wrote: many thanks Gord, I look forward to seeing your code Peter- Hide quoted text - - Show quoted text - many thanks Gord will give it a try |
#21
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List
On 5 Aug, 17:46, Pete wrote:
On 1 Aug, 22:59, Gord Dibben <gorddibbATshawDOTca wrote: I think this is it. Tested on both the DV drowdown selection and the multiply by Offset(6, 0) codition. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C11,E11,G11,I11,K11" * * 'add cells to suit Dim cell As Range * * On Error GoTo ws_exit * * Application.EnableEvents = False * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * With Target * * * * If .Value < "" Then * * * * * * .Value = .Value * .Offset(6, 0).Value * * * * * * End If * * * * End With * * End If * * Application.EnableEvents = True Set r = Range("A1") * * *'edit to suit the DV cell(s) If Intersect(Target, r) Is Nothing Then * * Exit Sub End If Application.EnableEvents = False vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers For Each rr In r * * ival = 0 * * For i = LBound(vals) To UBound(vals) * * * * If UCase(rr.Value) = vals(i) Then * * * * * * ival = nums(i) * * * * End If * * Next * * If ival 0 Then * * rr.Value = ival * * End If Next ws_exit: * * Application.EnableEvents = True End Sub Gord On Fri, 1 Aug 2008 14:15:00 -0700 (PDT), Pete wrote: many thanks Gord, I look forward to seeing your code Peter- Hide quoted text - - Show quoted text - many thanks Gord will give it a try- Hide quoted text - - Show quoted text - Thanks Gord, works fine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list dependant on previous drop down list | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
how do I use one drop-list to modify another drop-lists options? | Excel Discussion (Misc queries) | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) |