If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Validation to force entry into cells?
Hope my topic isn't too confusing.
What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. |
#2
|
|||
|
|||
Debra Dalgleish has some notes how to use Data|Validation and dependent lists
at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson |
#3
|
|||
|
|||
Thanks for the reply, Dave.
If what I needed was that "simple", I'd have no problem. However, I'm looking for something a bit more complex I guess. =) Using that page as an example, say that in column A they had to choose Fruit or Vegetable. Column B and Column C would each be "Item" and would only display/allow entry into that column dependant on what is selected in A. So if you choose Fruit, then you are "forced" to move to column B and type/select something from the Fruit list. If Vegetable, the same for column C. I'm looking to have a large list of codes in my "column A" and depending on which of those is selected, the "cursor" would then automatically move to the appropriate column (in my example either Debit or Credit). "Dave Peterson" wrote: Debra Dalgleish has some notes how to use Data|Validation and dependent lists at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson |
#4
|
|||
|
|||
Oops. I misunderstood. (I thought you were asking something different--but
that site has good info about dependent lists, huh?). If you select multiple cells (click, ctrl-click, ctrl-click, etc), you can tab between them. So how about this: Right click on the worksheet tab that should have this behavior. Select view code. Paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a2:a65536")) Is Nothing Then Exit Sub Select Case Target.Value Case 61: Set myRng = Me.Range("b1,d1,e1") Case 62: Set myRng = Me.Range("c1,e1,g1") Case 63: Set myRng = Me.Range("f1") Case Else: Set myRng = Nothing End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, 0)).Select End If End Sub You'll have to adjust the range that allows the codes--I used A2:A65536. You'll have to adjust the columns that correspond to the values that you type. (I used b,d,e for 61.) Once you enter your value in the cell, it selects the columns that you specified plus the cell below--Just keep tabbing and you'll get to the next row. If you tab by one of the cells, just keep tabbing. And if you're using xl97, be aware of a minor problem--From Debra Dalgleish's site (again!): http://www.contextures.com/xlDataVal08.html#Change In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. Mark wrote: Thanks for the reply, Dave. If what I needed was that "simple", I'd have no problem. However, I'm looking for something a bit more complex I guess. =) Using that page as an example, say that in column A they had to choose Fruit or Vegetable. Column B and Column C would each be "Item" and would only display/allow entry into that column dependant on what is selected in A. So if you choose Fruit, then you are "forced" to move to column B and type/select something from the Fruit list. If Vegetable, the same for column C. I'm looking to have a large list of codes in my "column A" and depending on which of those is selected, the "cursor" would then automatically move to the appropriate column (in my example either Debit or Credit). "Dave Peterson" wrote: Debra Dalgleish has some notes how to use Data|Validation and dependent lists at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
And just to make sure you're tab behaves nicely, make sure you have:
Tools|options|transition tab|Transition Navigation Keys unchecked. Dave Peterson wrote: Oops. I misunderstood. (I thought you were asking something different--but that site has good info about dependent lists, huh?). If you select multiple cells (click, ctrl-click, ctrl-click, etc), you can tab between them. So how about this: Right click on the worksheet tab that should have this behavior. Select view code. Paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a2:a65536")) Is Nothing Then Exit Sub Select Case Target.Value Case 61: Set myRng = Me.Range("b1,d1,e1") Case 62: Set myRng = Me.Range("c1,e1,g1") Case 63: Set myRng = Me.Range("f1") Case Else: Set myRng = Nothing End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, 0)).Select End If End Sub You'll have to adjust the range that allows the codes--I used A2:A65536. You'll have to adjust the columns that correspond to the values that you type. (I used b,d,e for 61.) Once you enter your value in the cell, it selects the columns that you specified plus the cell below--Just keep tabbing and you'll get to the next row. If you tab by one of the cells, just keep tabbing. And if you're using xl97, be aware of a minor problem--From Debra Dalgleish's site (again!): http://www.contextures.com/xlDataVal08.html#Change In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. Mark wrote: Thanks for the reply, Dave. If what I needed was that "simple", I'd have no problem. However, I'm looking for something a bit more complex I guess. =) Using that page as an example, say that in column A they had to choose Fruit or Vegetable. Column B and Column C would each be "Item" and would only display/allow entry into that column dependant on what is selected in A. So if you choose Fruit, then you are "forced" to move to column B and type/select something from the Fruit list. If Vegetable, the same for column C. I'm looking to have a large list of codes in my "column A" and depending on which of those is selected, the "cursor" would then automatically move to the appropriate column (in my example either Debit or Credit). "Dave Peterson" wrote: Debra Dalgleish has some notes how to use Data|Validation and dependent lists at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
Wow, that is some awesome work there, Dave, thanks.
I only have one remaining issue. When we finish up a row (we use columns A through I, of which column F is restricted from being used and column D or E is where I want the validation to force the tab to move to). When I use your code, it does the job amazingly but it never takes me to the next row to start all over. Here's the code I've put in for my use, perhaps you can show me how to make it so when the user tabs through to Column I it will move down a row and end the code part until it gets back to column C(in other words, I want this code to pickup after an entry is made into column C since column A is for an account name and column B is for an account number they're not dependant on anything else). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("c3:c500")) Is Nothing Then Exit Sub Select Case Target.Value Case 6: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") Case 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), Target.Offset(1, 0)).Select End If End Sub _____ Thanks for the great advice so far, and I do agree, www.contextures is a great reference site! BTW, I use Excel 2000. "Dave Peterson" wrote: Oops. I misunderstood. (I thought you were asking something different--but that site has good info about dependent lists, huh?). If you select multiple cells (click, ctrl-click, ctrl-click, etc), you can tab between them. So how about this: Right click on the worksheet tab that should have this behavior. Select view code. Paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a2:a65536")) Is Nothing Then Exit Sub Select Case Target.Value Case 61: Set myRng = Me.Range("b1,d1,e1") Case 62: Set myRng = Me.Range("c1,e1,g1") Case 63: Set myRng = Me.Range("f1") Case Else: Set myRng = Nothing End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, 0)).Select End If End Sub You'll have to adjust the range that allows the codes--I used A2:A65536. You'll have to adjust the columns that correspond to the values that you type. (I used b,d,e for 61.) Once you enter your value in the cell, it selects the columns that you specified plus the cell below--Just keep tabbing and you'll get to the next row. If you tab by one of the cells, just keep tabbing. And if you're using xl97, be aware of a minor problem--From Debra Dalgleish's site (again!): http://www.contextures.com/xlDataVal08.html#Change In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. Mark wrote: Thanks for the reply, Dave. If what I needed was that "simple", I'd have no problem. However, I'm looking for something a bit more complex I guess. =) Using that page as an example, say that in column A they had to choose Fruit or Vegetable. Column B and Column C would each be "Item" and would only display/allow entry into that column dependant on what is selected in A. So if you choose Fruit, then you are "forced" to move to column B and type/select something from the Fruit list. If Vegetable, the same for column C. I'm looking to have a large list of codes in my "column A" and depending on which of those is selected, the "cursor" would then automatically move to the appropriate column (in my example either Debit or Credit). "Dave Peterson" wrote: Debra Dalgleish has some notes how to use Data|Validation and dependent lists at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
I copied your code into a new worksheet module and typed a 6 in C8 (for
testing). The range that was selected was: A8,B8,E8,g8,h8,i8 and C9 (the next row) And those are the cells that were activated (in that order) when I hit the tab. Maybe you just didn't notice that last cell and didn't tab just one more time???? Am I missing the order that you wanted? == By the way, since you're duplicating the ranges (6 and 90, 61 and 95 are common). You could use this. Select Case Target.Value Case 6, 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61, 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select And if you wanted the change to column C to select column A of the next row (not column C), you could use this: Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, -2)).Select Target.offset(1,-2) means to come down one row and go left 2 cells. Mark wrote: Wow, that is some awesome work there, Dave, thanks. I only have one remaining issue. When we finish up a row (we use columns A through I, of which column F is restricted from being used and column D or E is where I want the validation to force the tab to move to). When I use your code, it does the job amazingly but it never takes me to the next row to start all over. Here's the code I've put in for my use, perhaps you can show me how to make it so when the user tabs through to Column I it will move down a row and end the code part until it gets back to column C(in other words, I want this code to pickup after an entry is made into column C since column A is for an account name and column B is for an account number they're not dependant on anything else). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("c3:c500")) Is Nothing Then Exit Sub Select Case Target.Value Case 6: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") Case 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), Target.Offset(1, 0)).Select End If End Sub _____ Thanks for the great advice so far, and I do agree, www.contextures is a great reference site! BTW, I use Excel 2000. "Dave Peterson" wrote: Oops. I misunderstood. (I thought you were asking something different--but that site has good info about dependent lists, huh?). If you select multiple cells (click, ctrl-click, ctrl-click, etc), you can tab between them. So how about this: Right click on the worksheet tab that should have this behavior. Select view code. Paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a2:a65536")) Is Nothing Then Exit Sub Select Case Target.Value Case 61: Set myRng = Me.Range("b1,d1,e1") Case 62: Set myRng = Me.Range("c1,e1,g1") Case 63: Set myRng = Me.Range("f1") Case Else: Set myRng = Nothing End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, 0)).Select End If End Sub You'll have to adjust the range that allows the codes--I used A2:A65536. You'll have to adjust the columns that correspond to the values that you type. (I used b,d,e for 61.) Once you enter your value in the cell, it selects the columns that you specified plus the cell below--Just keep tabbing and you'll get to the next row. If you tab by one of the cells, just keep tabbing. And if you're using xl97, be aware of a minor problem--From Debra Dalgleish's site (again!): http://www.contextures.com/xlDataVal08.html#Change In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. Mark wrote: Thanks for the reply, Dave. If what I needed was that "simple", I'd have no problem. However, I'm looking for something a bit more complex I guess. =) Using that page as an example, say that in column A they had to choose Fruit or Vegetable. Column B and Column C would each be "Item" and would only display/allow entry into that column dependant on what is selected in A. So if you choose Fruit, then you are "forced" to move to column B and type/select something from the Fruit list. If Vegetable, the same for column C. I'm looking to have a large list of codes in my "column A" and depending on which of those is selected, the "cursor" would then automatically move to the appropriate column (in my example either Debit or Credit). "Dave Peterson" wrote: Debra Dalgleish has some notes how to use Data|Validation and dependent lists at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
OK, one last "problem." When I'm done with one row, I'd like it to go to the
next row and "release" the restrictions. For example, if I type all my entries on Row 3 (even possibly leaving some of those cells blank, as is something that occurs with this file), I want the Tab on I1 to send me to A4 and then any subsequent Tabs take me along that row instead of going back up to row 3. Also, I notice if I use: Case 6: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") ...that it kills things and these rules in the code don't work (well..it kills things if I go back in and insert the a1,b1 because I don't actually have them in my code anymore). My code looks like this: Case 6: Set myRng = Me.Range("e1,g1,h1,i1") It works great, I tab after inputting something for C and it then sends me to column E like the rule says. We usually don't put any entry in column G and H, but we want to make it possible for them to make entries there for the rare occasions that it's needed. Problems arise when something is typed in column I, then it tabs down to the next row in column A (which is what I want), but then hitting tab from there takes me back to C of the row I started in, even if a value is there. "Dave Peterson" wrote: I copied your code into a new worksheet module and typed a 6 in C8 (for testing). The range that was selected was: A8,B8,E8,g8,h8,i8 and C9 (the next row) And those are the cells that were activated (in that order) when I hit the tab. Maybe you just didn't notice that last cell and didn't tab just one more time???? Am I missing the order that you wanted? == By the way, since you're duplicating the ranges (6 and 90, 61 and 95 are common). You could use this. Select Case Target.Value Case 6, 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61, 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select And if you wanted the change to column C to select column A of the next row (not column C), you could use this: Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, -2)).Select Target.offset(1,-2) means to come down one row and go left 2 cells. Mark wrote: Wow, that is some awesome work there, Dave, thanks. I only have one remaining issue. When we finish up a row (we use columns A through I, of which column F is restricted from being used and column D or E is where I want the validation to force the tab to move to). When I use your code, it does the job amazingly but it never takes me to the next row to start all over. Here's the code I've put in for my use, perhaps you can show me how to make it so when the user tabs through to Column I it will move down a row and end the code part until it gets back to column C(in other words, I want this code to pickup after an entry is made into column C since column A is for an account name and column B is for an account number they're not dependant on anything else). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("c3:c500")) Is Nothing Then Exit Sub Select Case Target.Value Case 6: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") Case 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), Target.Offset(1, 0)).Select End If End Sub _____ Thanks for the great advice so far, and I do agree, www.contextures is a great reference site! BTW, I use Excel 2000. "Dave Peterson" wrote: Oops. I misunderstood. (I thought you were asking something different--but that site has good info about dependent lists, huh?). If you select multiple cells (click, ctrl-click, ctrl-click, etc), you can tab between them. So how about this: Right click on the worksheet tab that should have this behavior. Select view code. Paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a2:a65536")) Is Nothing Then Exit Sub Select Case Target.Value Case 61: Set myRng = Me.Range("b1,d1,e1") Case 62: Set myRng = Me.Range("c1,e1,g1") Case 63: Set myRng = Me.Range("f1") Case Else: Set myRng = Nothing End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, 0)).Select End If End Sub You'll have to adjust the range that allows the codes--I used A2:A65536. You'll have to adjust the columns that correspond to the values that you type. (I used b,d,e for 61.) Once you enter your value in the cell, it selects the columns that you specified plus the cell below--Just keep tabbing and you'll get to the next row. If you tab by one of the cells, just keep tabbing. And if you're using xl97, be aware of a minor problem--From Debra Dalgleish's site (again!): http://www.contextures.com/xlDataVal08.html#Change In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. Mark wrote: Thanks for the reply, Dave. If what I needed was that "simple", I'd have no problem. However, I'm looking for something a bit more complex I guess. =) Using that page as an example, say that in column A they had to choose Fruit or Vegetable. Column B and Column C would each be "Item" and would only display/allow entry into that column dependant on what is selected in A. So if you choose Fruit, then you are "forced" to move to column B and type/select something from the Fruit list. If Vegetable, the same for column C. I'm looking to have a large list of codes in my "column A" and depending on which of those is selected, the "cursor" would then automatically move to the appropriate column (in my example either Debit or Credit). "Dave Peterson" wrote: Debra Dalgleish has some notes how to use Data|Validation and dependent lists at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
|
|||
|
|||
That was the point of tabbing to column C (one row down).
As soon as you typed in that cell and hit enter, then a new selection would take place. Do you want to base your selection on changes in column A or column C? The bad news is that since you're not changing the selection when you're tabbing through the selected cells, you can't hook into when you tab to A(row+1). But if you know that you're last entry is always in column I, you can use that to select the next row column A. I'm lost on the columns per value, but kind of... like this Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("c3:c500")) Is Nothing Then Select Case Target.Value Case 6, 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61, 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select If myRng Is Nothing Then Target.Select Else Intersect(Target.EntireRow, myRng.EntireColumn).Select End If Else If Not Intersect(Target, Me.Range("I3:I500")) Is Nothing Then Me.Cells(Target.Row + 1, "A").Select End If End If End Sub (And with this, there isn't much reason to select column A when you change column C (is there???).) Mark wrote: OK, one last "problem." When I'm done with one row, I'd like it to go to the next row and "release" the restrictions. For example, if I type all my entries on Row 3 (even possibly leaving some of those cells blank, as is something that occurs with this file), I want the Tab on I1 to send me to A4 and then any subsequent Tabs take me along that row instead of going back up to row 3. Also, I notice if I use: Case 6: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") ..that it kills things and these rules in the code don't work (well..it kills things if I go back in and insert the a1,b1 because I don't actually have them in my code anymore). My code looks like this: Case 6: Set myRng = Me.Range("e1,g1,h1,i1") It works great, I tab after inputting something for C and it then sends me to column E like the rule says. We usually don't put any entry in column G and H, but we want to make it possible for them to make entries there for the rare occasions that it's needed. Problems arise when something is typed in column I, then it tabs down to the next row in column A (which is what I want), but then hitting tab from there takes me back to C of the row I started in, even if a value is there. "Dave Peterson" wrote: I copied your code into a new worksheet module and typed a 6 in C8 (for testing). The range that was selected was: A8,B8,E8,g8,h8,i8 and C9 (the next row) And those are the cells that were activated (in that order) when I hit the tab. Maybe you just didn't notice that last cell and didn't tab just one more time???? Am I missing the order that you wanted? == By the way, since you're duplicating the ranges (6 and 90, 61 and 95 are common). You could use this. Select Case Target.Value Case 6, 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61, 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select And if you wanted the change to column C to select column A of the next row (not column C), you could use this: Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, -2)).Select Target.offset(1,-2) means to come down one row and go left 2 cells. Mark wrote: Wow, that is some awesome work there, Dave, thanks. I only have one remaining issue. When we finish up a row (we use columns A through I, of which column F is restricted from being used and column D or E is where I want the validation to force the tab to move to). When I use your code, it does the job amazingly but it never takes me to the next row to start all over. Here's the code I've put in for my use, perhaps you can show me how to make it so when the user tabs through to Column I it will move down a row and end the code part until it gets back to column C(in other words, I want this code to pickup after an entry is made into column C since column A is for an account name and column B is for an account number they're not dependant on anything else). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("c3:c500")) Is Nothing Then Exit Sub Select Case Target.Value Case 6: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") Case 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), Target.Offset(1, 0)).Select End If End Sub _____ Thanks for the great advice so far, and I do agree, www.contextures is a great reference site! BTW, I use Excel 2000. "Dave Peterson" wrote: Oops. I misunderstood. (I thought you were asking something different--but that site has good info about dependent lists, huh?). If you select multiple cells (click, ctrl-click, ctrl-click, etc), you can tab between them. So how about this: Right click on the worksheet tab that should have this behavior. Select view code. Paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a2:a65536")) Is Nothing Then Exit Sub Select Case Target.Value Case 61: Set myRng = Me.Range("b1,d1,e1") Case 62: Set myRng = Me.Range("c1,e1,g1") Case 63: Set myRng = Me.Range("f1") Case Else: Set myRng = Nothing End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, 0)).Select End If End Sub You'll have to adjust the range that allows the codes--I used A2:A65536. You'll have to adjust the columns that correspond to the values that you type. (I used b,d,e for 61.) Once you enter your value in the cell, it selects the columns that you specified plus the cell below--Just keep tabbing and you'll get to the next row. If you tab by one of the cells, just keep tabbing. And if you're using xl97, be aware of a minor problem--From Debra Dalgleish's site (again!): http://www.contextures.com/xlDataVal08.html#Change In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. Mark wrote: Thanks for the reply, Dave. If what I needed was that "simple", I'd have no problem. However, I'm looking for something a bit more complex I guess. =) Using that page as an example, say that in column A they had to choose Fruit or Vegetable. Column B and Column C would each be "Item" and would only display/allow entry into that column dependant on what is selected in A. So if you choose Fruit, then you are "forced" to move to column B and type/select something from the Fruit list. If Vegetable, the same for column C. I'm looking to have a large list of codes in my "column A" and depending on which of those is selected, the "cursor" would then automatically move to the appropriate column (in my example either Debit or Credit). "Dave Peterson" wrote: Debra Dalgleish has some notes how to use Data|Validation and dependent lists at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
|
|||
|
|||
Dave, would it be OK if I e-mail you? I don't want to just do it out of the
blue and figure we can perhaps get more into my issue and I can figure out a way to get my file to you to show you how it operates. Thanks. "Dave Peterson" wrote: That was the point of tabbing to column C (one row down). As soon as you typed in that cell and hit enter, then a new selection would take place. Do you want to base your selection on changes in column A or column C? The bad news is that since you're not changing the selection when you're tabbing through the selected cells, you can't hook into when you tab to A(row+1). But if you know that you're last entry is always in column I, you can use that to select the next row column A. I'm lost on the columns per value, but kind of... like this Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("c3:c500")) Is Nothing Then Select Case Target.Value Case 6, 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61, 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select If myRng Is Nothing Then Target.Select Else Intersect(Target.EntireRow, myRng.EntireColumn).Select End If Else If Not Intersect(Target, Me.Range("I3:I500")) Is Nothing Then Me.Cells(Target.Row + 1, "A").Select End If End If End Sub (And with this, there isn't much reason to select column A when you change column C (is there???).) Mark wrote: OK, one last "problem." When I'm done with one row, I'd like it to go to the next row and "release" the restrictions. For example, if I type all my entries on Row 3 (even possibly leaving some of those cells blank, as is something that occurs with this file), I want the Tab on I1 to send me to A4 and then any subsequent Tabs take me along that row instead of going back up to row 3. Also, I notice if I use: Case 6: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") ..that it kills things and these rules in the code don't work (well..it kills things if I go back in and insert the a1,b1 because I don't actually have them in my code anymore). My code looks like this: Case 6: Set myRng = Me.Range("e1,g1,h1,i1") It works great, I tab after inputting something for C and it then sends me to column E like the rule says. We usually don't put any entry in column G and H, but we want to make it possible for them to make entries there for the rare occasions that it's needed. Problems arise when something is typed in column I, then it tabs down to the next row in column A (which is what I want), but then hitting tab from there takes me back to C of the row I started in, even if a value is there. "Dave Peterson" wrote: I copied your code into a new worksheet module and typed a 6 in C8 (for testing). The range that was selected was: A8,B8,E8,g8,h8,i8 and C9 (the next row) And those are the cells that were activated (in that order) when I hit the tab. Maybe you just didn't notice that last cell and didn't tab just one more time???? Am I missing the order that you wanted? == By the way, since you're duplicating the ranges (6 and 90, 61 and 95 are common). You could use this. Select Case Target.Value Case 6, 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61, 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select And if you wanted the change to column C to select column A of the next row (not column C), you could use this: Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, -2)).Select Target.offset(1,-2) means to come down one row and go left 2 cells. Mark wrote: Wow, that is some awesome work there, Dave, thanks. I only have one remaining issue. When we finish up a row (we use columns A through I, of which column F is restricted from being used and column D or E is where I want the validation to force the tab to move to). When I use your code, it does the job amazingly but it never takes me to the next row to start all over. Here's the code I've put in for my use, perhaps you can show me how to make it so when the user tabs through to Column I it will move down a row and end the code part until it gets back to column C(in other words, I want this code to pickup after an entry is made into column C since column A is for an account name and column B is for an account number they're not dependant on anything else). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("c3:c500")) Is Nothing Then Exit Sub Select Case Target.Value Case 6: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 90: Set myRng = Me.Range("a1,b1,e1,g1,h1,i1") Case 61: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") Case 95: Set myRng = Me.Range("a1,b1,d1,g1,h1,i1") End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), Target.Offset(1, 0)).Select End If End Sub _____ Thanks for the great advice so far, and I do agree, www.contextures is a great reference site! BTW, I use Excel 2000. "Dave Peterson" wrote: Oops. I misunderstood. (I thought you were asking something different--but that site has good info about dependent lists, huh?). If you select multiple cells (click, ctrl-click, ctrl-click, etc), you can tab between them. So how about this: Right click on the worksheet tab that should have this behavior. Select view code. Paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a2:a65536")) Is Nothing Then Exit Sub Select Case Target.Value Case 61: Set myRng = Me.Range("b1,d1,e1") Case 62: Set myRng = Me.Range("c1,e1,g1") Case 63: Set myRng = Me.Range("f1") Case Else: Set myRng = Nothing End Select If myRng Is Nothing Then Target.Select Else Union(Intersect(Target.EntireRow, myRng.EntireColumn), _ Target.Offset(1, 0)).Select End If End Sub You'll have to adjust the range that allows the codes--I used A2:A65536. You'll have to adjust the columns that correspond to the values that you type. (I used b,d,e for 61.) Once you enter your value in the cell, it selects the columns that you specified plus the cell below--Just keep tabbing and you'll get to the next row. If you tab by one of the cells, just keep tabbing. And if you're using xl97, be aware of a minor problem--From Debra Dalgleish's site (again!): http://www.contextures.com/xlDataVal08.html#Change In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. Mark wrote: Thanks for the reply, Dave. If what I needed was that "simple", I'd have no problem. However, I'm looking for something a bit more complex I guess. =) Using that page as an example, say that in column A they had to choose Fruit or Vegetable. Column B and Column C would each be "Item" and would only display/allow entry into that column dependant on what is selected in A. So if you choose Fruit, then you are "forced" to move to column B and type/select something from the Fruit list. If Vegetable, the same for column C. I'm looking to have a large list of codes in my "column A" and depending on which of those is selected, the "cursor" would then automatically move to the appropriate column (in my example either Debit or Credit). "Dave Peterson" wrote: Debra Dalgleish has some notes how to use Data|Validation and dependent lists at: http://www.contextures.com/xlDataVal02.html Mark wrote: Hope my topic isn't too confusing. What I've done is create a drop-down validation list for a row in a worksheet. What I would like to do is force the entry of the corresponding value into a particular cell (in that row). For instance, If a user chooses "61" from the drop down box, they should only be allowed to type an amount in one of 2 particular cells in the next 2 columns. This is all related to banking. So say someone chooses "61", I want the dollar amount to force them to input in the "Credit" column and skip right over the "Debit" column when hitting Tab. This sheet has about 8 columns of data that needs to be allowed, but I need to tie this "code" column and whatever value they choose from the drop-down list to send the entry point to the appropriate column for data entry. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel 2000 vs 2002 Sheet protection & Validation cells | Andre Croteau | General Discussion | 4 | July 17th, 2004 03:31 PM |
Data validation based upon another cell's data | joannanpa | Worksheet Functions | 0 | May 10th, 2004 06:24 PM |
Locking other cells based on values in a cell | Worksheet Functions | 3 | January 13th, 2004 12:53 AM | |
Grams to Pounds | Grams to Pounds | Worksheet Functions | 10 | December 12th, 2003 07:32 PM |