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.

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

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

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

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

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

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

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

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

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