I WANT TO CREATE MANDATORY CELLS IN WORKSHEET.
IF THE CELLS ARE BLANK THEN NOT ALLOW TO FORWARD.

Please explain what do you mean by mandatory cells?
--
R. Khoshravan
Please click "Yes" if it is helpful.


"LOCK CELLS" wrote:

I WANT TO CREATE MANDATORY CELLS IN WORKSHEET.
IF THE CELLS ARE BLANK THEN NOT ALLOW TO FORWARD.

Hi,

Since Excel does not have a before forward command the best you could do
with any ease would be to use Before Save. I suppose you could write a
program that disabled the File Menu's Send command until the appropriate
cells had been filled in, but that doesn't mean that the user would be
forwarding the file from inside of Excel.

Here is sample code for a before save event:

Here is an example of a Before Save event which hides some rows and columns
and protects the spreadsheet with a password:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
On Error Resume Next
For Each cell In Range("Required")
If cell = "" Then
MsgBox "You haven't completed the required entries."
Cancel = True
Exit Sub
End If
Next cell
End Sub

You will also need to select the cells requiring entry and name them Required.

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"LOCK CELLS" wrote:

I WANT TO CREATE MANDATORY CELLS IN WORKSHEET.
IF THE CELLS ARE BLANK THEN NOT ALLOW TO FORWARD.

Hi!

You can also try with this code:

Option Explicit
Const cstrMandatory As String = "A:A" 'For column "A" only.
Dim fIsBlank As Boolean
Dim strAddress As String

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not Application.Intersect(.Cells(1), _
Range(cstrMandatory)) Is Nothing Then
fIsBlank = .Value = ""
End If
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If fIsBlank Then
Application.EnableEvents = False
Range(strAddress).Select
MsgBox "This cell required!", vbExclamation
Application.EnableEvents = True
Else
If Not Application.Intersect(.Cells(1), _
Range(cstrMandatory)) Is Nothing Then
If .Count = 1 Then
strAddress = Target.Address
fIsBlank = .Value = ""
End If
End If
End If
End With
End Sub

Creates a "CellTrap" and you can not get out until type something.
Paste this code in code module of your sheet.

Cheers,
John John


Ο χÏ�ήστης "LOCK CELLS" Î*γγÏ�αψε:

I WANT TO CREATE MANDATORY CELLS IN WORKSHEET.
IF THE CELLS ARE BLANK THEN NOT ALLOW TO FORWARD.