How do I require data entry in a cell before moving to the next ce

[CindyB]

I'm feeling really frustrated here, but can someone help me with requiring a
user to enter data in a cell before they can leave the cell?

I have a drop down box for the cell, and have unchecked 'ignore blank' in
the data validation, but I can still move past the cell without picking
something from the list.

I need to be able to force the user to fill in specific cells in the row,
from drop down boxes, as they move across the worksheet. If they do not
choose from the drop-down box, I need the cursor to stay in that cell and
force them to choose.

Can I do that in Excel?

Warning: My VBA skills are really, really bad.........

thanks - Cindy Butler

 

[Gary''s Student]

Put the following line in a standard module:

Public checkit As Boolean

Put the following worksheet event macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set b9 = Range("B9")
Set t = Target
If Intersect(t, b9) Is Nothing Then
If checkit Then
If b9.Value = "" Then
Application.EnableEvents = False
b9.Select
Application.EnableEvents = True
Else
checkit = False
End If
End If
Else
checkit = True
End If
End Sub

This is just an example using cell B9. Once he user has clicked on B9, data
must be entered. If the user tries to move away from B9 without entering
data, they are moved back to B9.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[CindyB]

Gary - Thank you!!

I have this code in now and am testing it, however I know it won't work
until I do that standard module thing. But I don't know what that means.

Can you be a little more specific about the following statements:
"Put the following line in a standard module:

"Public checkit As Boolean

What is a standard module and how to I get there?

Thanks!!

Cindy Butler

 

[MiataDiablo]

Gary - Thank you!!

I have this code in now and am testing it, however I know it won't work
until I do that standard module thing. But I don't know what that means.

Can you be a little more specific about the following statements:
"Put the following line in a standard module:

"Public checkit As Boolean

What is a standard module and how to I get there?

Thanks!!

Cindy Butler
















- Show quoted text -

This is something I've been looking all over for, but I have a really
stupid question. How do I update the code so that it knows to check
the entire column, not just cell B9?