Using Validation to force entry into cells?

M

Mark

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.
 
M

Mark

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).
 
D

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.
 
D

Dave Peterson

And just to make sure you're tab behaves nicely, make sure you have:

Tools|options|transition tab|Transition Navigation Keys
unchecked.
 
M

Mark

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.
 
D

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.
 
M

Mark

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.
 
D

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???).)
 
M

Mark

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.
 
D

Dave Peterson

I like to keep the discussions in the newsgroups.

It keeps me honest and I get to learn more.
 
M

Mark

OK, I understand and respect that. I'll try to illustrate my file by
describing how it's laid out and how we use it. This is for a bank.
----
Row 1 is limited to on cell that will display the balance between the D and
E columns
Row 2 is where all the following columns are setup:
A = Account Name - this columns is just plain text
B = Account Number - entry here is all digits ranging anywhere from 4-13
characters
C = TC - This is the column I have validation connected to. The list for
this validation is tied to hidden column L. There are currently 36 codes
which may expand in the future.
D = Debit Amount - I have used Name Definitions here to tie certain "TC"s to
only allow entry into this column or Column E. This is what my overall
desire for this coding you have shown me - to bypass entry into the incorrect
column more easily. As it is I have an error message pop up if someone tries
to put an amount in the Debit column if the TC is related to a Credit, and
vice versa. By using the code you have shown me, I am trying to make it so
if someone enters a Credit TC that it will skip right over the D column and
if it's a Debit TC that it will move the cursor to the D column and then
tabbing will skip over the E column.
E = Credit Amount - refer to Column D description for info
F = Difference - this column is to be forcefully skipped. This column is
only here to maintain the "schema" of another program we copy this
information into and it is automatically caluclated there, so we do not want
entry into this column in our form.
G = check # - this column is rarely used but we do want the ability for
users to type in here for the rare occasion it's needed
H = Date - another column that is rarely used but we want to allow it for
those times
I = Description - a text cell to describe what this entire row was done for
K is a hidden column that does some calculation that carries up into that
Row 1 area.

Worksheet 2 is where I had initially setup the Name Definitions, so I'm
leaving it there and have hidden that worksheet.


Now, for what we are trying to accomplish.

We use this form because only 2 people are allowed to use the "official"
program that these entries are put into for processing. We've created this
Excel file to reflect the layout of it. This program operates as I described
how I wish to have the columns setup - if someone enters a TC that is for
Debits, it skips to the debit column for entry and then a Tab will take you
past the Credit column so no entry can be made there, and likewise for the
Credit "TC"s.

My goal is to have it so this validation list I have used for Column C lists
all the TCs we use on a regular basis. Once a TC is selected, that's when I
wish this coding to pickup for the rest of that row.

Here's an example using one of the codes for the C column:
Entry into row A and B are arbitrary. When a user either uses the dropdown
or types 90 into the C column, the code should kick in and then force the
next entry to be in column E for a credit. After that the next TAB would
skip over column F (which would be global for any entry in column C) and
allow entry into G, H and I. Once something is typed in I and TAB is
pressed, I would like it to drop down to the next row and allow entry of the
next line. As things are now it does drop down to the next row with some
information you have given me, but further tabbing makes things attempt to go
back over the previous entries (for example it will drop me down to A4 but
once I tab it takes me to C3 - this is with previous code I've used where
it's coded as Me.Range("e1,g1,h1,i1") ).

It seems once this code kicks in it has me locked into those cells (E3, G3,
H3, I3, A4) no matter how many times i hit TAB or ENTER.

Again, here's the code I've been using recently that gets these results:

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("e1,g1,h1,i1")
Case 90: Set myRng = Me.Range("e1,g1,h1,i1")
Case 61: Set myRng = Me.Range("d1,g1,h1,i1")
Case 95: Set myRng = Me.Range("d1,g1,h1,i1")
End Select

If myRng Is Nothing Then
Target.Select
Else
Union(Intersect(Target.EntireRow, myRng.EntireColumn), Target.Offset(1,
-2)).Select
End If


End Sub
 
D

Dave Peterson

I would have thought that making a change in column I in this version would have
been sufficient:

(Merged your ranges with a suggestion from a couple of posts ago):

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: Set myRng = Me.Range("e1,g1,h1,i1")
Case 90: Set myRng = Me.Range("e1,g1,h1,i1")
Case 61: Set myRng = Me.Range("d1,g1,h1,i1")
Case 95: Set myRng = Me.Range("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

I didn't see where you included this portion:

If Not Intersect(Target, Me.Range("I3:I500")) Is Nothing Then
Me.Cells(Target.Row + 1, "A").Select
End If

This says if you make a change in column I, then come down to the next row and
select the cell in column A.
 
M

Mark

Works like a charm now. Thanks for everything Dave. I do intend on
shortening things up by combining the similar rule to the ranges, I just
didn't have it set in this copy of the file when I pasted the code.

Again, thanks for all the help, it's a lot of work to do for a stranger and
it's people like you that help build communities like this.
 
M

Mark

Uh oh...I run into a problem now with every copy of this worksheet.

I've used the following for the code:

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, 1, 23, 70, 74, 76, 77, 79, 83, 84, 88, 90, 314, 315,
316, 317: Set myRng = Me.Range("e1,g1,h1,i1")
Case 21, 22, 31, 32, 37, 45, 55, 60, 61, 95, 351, 364, 369, 370,
371, 372, 373, 374, 375, 376: Set myRng = Me.Range("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
_______

Things work fine when I first create the worksheet. I test it out with
everything and it acts exactly like it should. I save the file and exit
Excel. I immediately go back into that file and the code doesn't do anything
anymore, and along with that my Name Definitions aren't even active anymore
for some reason (my intial way of limiting people to putting data in the
correct column).

Any ideas?
 
D

Dave Peterson

My first guess is that you opened the workbook with macros disabled.

Is that possible?

My second guess is that you have another event that turns off .enableevents and
doesn't turn it back on.


My second guess is that when you copied the worksheet, you copied the cells--not
the worksheet itself.

===
And if you need this same code for every worksheet in the workbook, you can
remove the code behind each worksheet and use a workbook_sheetchange event.
Note that Me. has been replaced with Sh.

This code goes under the ThisWorkbook module.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Sh.Range("c3:c500")) Is Nothing Then
Select Case Target.Value
Case 6, 1, 23, 70, 74, 76, 77, 79, 83, 84, 88, _
90, 314, 315, _
316, 317: Set myRng = Sh.Range("e1,g1,h1,i1")
Case 21, 22, 31, 32, 37, 45, 55, 60, 61, 95, _
351, 364, 369, 370, 371, 372, 373, 374, _
375, 376: Set myRng = Sh.Range("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, Sh.Range("I3:I500")) Is Nothing Then
Sh.Cells(Target.Row + 1, "A").Select
End If
End If

End Sub
 
M

Mark

ahhh, didn't realize it was the macro security...changing it to low allowed
it to work, making it medium is giving me the prompt now and things are
working. I'll have to work on a certificate to it can trust it and not
prompt people.

Thanks yet again for the info!!
 
Top