How to force user input

Ruth Davies

New Member
Joined
Feb 2, 2005
Messages
1
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 (n) 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
:oops:
Ruth
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Mar 7, 2002
Messages
87
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,333
Messages
5,601,008
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top