How to force user input

[Ruth Davies]

Hi

I have 6 cells:

A1 = Case Number
B1 = Date of Hearing
C1 = Type of Case (V or N)
D1 = Date Information Faxed
E1 = In Target (Y or N)
F1 = Enter Reason

There are two types of cases V = Vulnerable and N = Non Vulnerable, Vulnerable cases need to have information faxed within one day from the date of Hearing and Non Vulnerbale Cases within three days of the date of hearing to be within Target (for eg. Date of Hearing today 02/02/05 then Vulnerable case out of target if information faxed 04/02/05). Therefore if a user inputs the data we require and the sum of cells (D1-B1) are greater than one (v) or three dependant on the case type, how can i FORCE the user to "enter a reason"
All I managed to do so far is enter into cell E1 the following formula:
IF(AND(C1="V",(D1-B1)>1),"enter reason",IF(AND(C1="N",(D1-B1)>3),"enter reason",""))
This alerts the user to insert a cooment into cell F1 BUT DOES NOT FORCE THEM TO DO THIS

If anyone can help me it would be appreciated

Ruth

 

[Damon Ostrander]

Hi Ruth,

I know of two possible approaches. The first does not require VBA code but relies on the necessity to fill in additional fields. For example, suppose the user must enter his/her name at the bottom of the form. You can make it so the user can't enter the name using data validation based on whether the reason cell has been filled in (and whether it is necessary to be filled in). It might be possible to cross-validate the cells so that if the user tries to fill in the name cell out of order, the C1 cell can't be filled in.

The second method uses VBA code to prevent the workbook from being saved unless the reason cell has been filled--again, if it is necessary. This is very easy to code, only requiring a few lines. This approach also allows the code to do additional checks, such as making sure all fields requiring input are filled in, etc., and that they are all filled in with consistent data.

But the trick here is forcing the user to enable macros upon opening the workbook so that the code can run. This can be done by making the workbook unusable until macros are enabled by locking the workbook and using a workbook Open event macro to unlock the workbook when the workbook first opens. Obviously the code can't unlock the workbook if macros haven't been enabled.

Do either of these approaches appeal to you?

Damon

 

[graphage]

Damon, I stumbled on this reply while searching for a problem I am having. Can you please post your code for your second solution. I would like to prohibit the user from saving the file until they have filled in the necessary fields. I'd also like your code that locks the sheet if they don't Enable macros. Thanks!

 

[Damon Ostrander]

Forcing user to enter data

Hi graphage,

Okay, here's some sample code for forcing the user to input data in cell B4 on Sheet before saving the file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'To save the test workbook even you the developer must put data in B4
If IsEmpty(Sheet1.Range("B4")) Then
Cancel = True
MsgBox "You must enter data into Sheet1 B4", _
vbCritical, "Did you forget?"
End If
End Sub

Private Sub Workbook_Open()
'This ensures desired cell will be empty when user first opens
Sheet1.Range("B4").ClearContents
End Sub

This code must be placed in the ThisWorkbook event code module. To do this right-click on the Excel icon at the left end of the Worksheet Menu Bar, select View Code, and paste this code in the code pane.

Note that the code will clear cell B4 when the workbook first opens. This means that in order to read the data that the user has entered and saved you must open the workbook without macros enabled. If you forget and enable macros it will clear it, but this is okay since you will not be able to accidentally save it without entering something in B4.

I will follow up with that method for forcing users to enable macros in a subsequent posting.

Keep Excelling.

Damon