How To Require Input

[SkippyPB]

I have several Excel 2003 worksheets that are set up for users to
input data into. I use data validation to ensure what is entered is
proper and I use a VBA Worksheet_Change (ByVal) to edit those things
that data validation can't.

What I don't have is a way to make the user enter data in the row,
columns that must have data in them. Currently they can press the
arrow keys or enter key to bypass a cell and everyone (but me) is
happy.

I thought of using an OnKey event, but can't seem to work that out. I
don't see an Event that would get activated if a cell is skipped.

Can anyone offer any suggestions?

Note: I am not using userforms and I don't think I have the time to
convert to those if that is a suggestion.

Thanks.
////
(o o)
-oOO--(_)--OOo-

"My luck is so bad that if I bought a cemetery,
people would stop dying."
-- Rodney Dangerfield
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[Bob Phillips]

You could use a selection change to not allow them to select if the previous
is not compete. Something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address(False, False)
Case "H3": If Me.Range("G3").Value = "" Then Me.Range("G3").Select
Case "J3": If Me.Range("H3").Value = "" Then Me.Range("H3").Select
'etc.
End Select
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SkippyPB]

You could use a selection change to not allow them to select if the previous
is not compete. Something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address(False, False)
Case "H3": If Me.Range("G3").Value = "" Then Me.Range("G3").Select
Case "J3": If Me.Range("H3").Value = "" Then Me.Range("H3").Select
'etc.
End Select
End Sub

That might be what I need, but how would I do it with columns and rows
being variable? Would something like:

Case Target.Row Target.Column: If Me.Range (Target.Column - 1,
Target.Row).Value = "" then Me.Range(Target.Column - -1,
Targe.Row).Select
...

Would that work and place the pointer in the empty cell?

Thanks.
////
(o o)
-oOO--(_)--OOo-

"My luck is so bad that if I bought a cemetery,
people would stop dying."
-- Rodney Dangerfield
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[Bob Phillips]

Not sure what you mean.

The code I gave is specific cell based, so you would plug in all the
relevant cell addresses. Are you asking for particular columns and rows to
be monitored?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SkippyPB]

Not sure what you mean.

The code I gave is specific cell based, so you would plug in all the
relevant cell addresses. Are you asking for particular columns and rows to
be monitored?

Yes and no. The spreadsheet can consist of many rows of data but each
column is the same. For example column 1 contains an id field. Column
2 contains a name field etc. etc. There are a total of 20 columns and
17 of them must contain data.

The key is if a row has some data in it, then a check should be done
to ensure that all cells in that row that require data have data. I
like the idea you proposed in your code, but I'm having trouble making
it work within my scenario.

Thanks.
////
(o o)
-oOO--(_)--OOo-

"My luck is so bad that if I bought a cemetery,
people would stop dying."
-- Rodney Dangerfield
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve