how do I make a cell required to have input?

  • Thread starter Thread starter Michele
  • Start date Start date
M

Michele

I have a spreadsheet that I want to require the user to add input before
going to the next cells. Does anyone have any idea how to do this?

thanks
 
1. press ALT+F11 to go VBA window
2. in Project-VBA Project window double click on the name of your
worksheet
3. go slightly to the right where you should see (General) inscription
and click on it
4. you should be able to select "Worksheet" and so do it
5. copy/paste the following code there


Private Sub Worksheet_Activate()

start:
If Len([a1]) = 0 And Now() > [Sheet2!a1] Then ins = InputBox("Before
going to other cells in this worksheet insert value into cell A1 or
otherwise you will not be able to continue", "Insert value")

[a1] = ins
[Sheet2!a1]=Now()

If Len([a1]) = 0 Then GoTo start

End Sub


the macro does the following:
1. checks whether A1 in Sheet1 is empty
2. if so it checks cell A1 in Sheet2 where time and date of last
change to cell A1 in Sheet1 was made
3. if present time/date > than time/date of last change macro shows an
Inputbox where the user is required to insert data
4. if the user does not insert the data before pressing OK and cell A1
in Sheet1 is still empty then Inputbox shows again (until sth. is
inserted)

it would be the best if Sheet2 was made xlVeryHidden so that the user
cannot reveal time/date of last change

if you wish I may send you a file with this macro. write me to
(e-mail address removed)
 
If you force the user to input something then you can expect a lot of junk to
be entered. Generally speaking a better option is to flag the cell in an
obvious way such that the user is aware of the requirement. Use formulas,
conditional formatting and data validation for that. You can also use some
code to prompt the user to make the input at some point (like switching
sheets) that the input is required.
 
Back
Top