• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If Excel cell is Blank

S

SAP

New Member
Hello All,

I do not want a excel sheet to get saved if cell c3 is blank

I am trying to create a corporate template, and employees in my organisation are keeping the excel cell c3 blank and saving the excel. we do not want to continue in this fashion.


So we require our employees to fill in cell c3 and if c3 is blank, excel should not get itself saved

the possible values in cell c3 will be "A" and "V"


Please can you help us


Thank you and Kindestregards

Srikaanth
 
Hello Srikaanth,


To limit the values to A or V, you can use Data Validation.

However, Data Validation cannot prevent blanks.


To prevent blanks, you probably have to use a VBA macro.


The following (untested) should out and out prevent the workbook from being saved if that cell is blank:

[pre]
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(['Sheet name'!C3]) Then
Cancel = True
Msgbox "A value is required in cell C3 before the workbook can be saved."
End If
End Sub
[/pre]
Also, if you protect the worksheet (even without a password) and only allow editing of the cells they will fill in, if they use the TAB key it will advance to the next field they can edit and help draw attention to it.


Asa
 
HI Asa


Thanks for very very quick reply.

I am not good in VBA macro, please can you advise me how to load the VBA macro scripts (how to reach a place where I can write your advised scripts)


or


any links to learn to load the VBA macro scripts and achieve my requirements


Thank you and Kindest regards

Srikaanth
 
You're welcome, SAP... and welcome to Chandoo.org, by the way.


If you need help with the Data Validation or Conditional Formatting (i.e. to highligt blank/required cells), check out these articles by Chandoo on how to "Make Awesome Data Entry Forms by using Conditional Formatting + Data Validation", How to add drop down list to validate data and Excel Conditional Formatting Basics.


To implement the VBA macro, do this:

1. Open your workbook

2. Open the Visual Basic Editor (VBE). If you don't know where to find it on the menu in your version of Excel, you can hit ALT-F11 to bring it up. intro article on the VBE

3. Double-click on "ThisWorkbook" in the Project pane, since the code I gave you is a Workbook-level "event".

4. In the Code pane (large text editing pane), paste the code I gave you.

5. Change
Code: 
Sheet name in the code to the name of the worksheet with the cell you are checking.

6. You could change the

[code]If IsEmpty(['Sheet name'!C3]) Then

line to

If IsEmpty(['Sheet name'!C3]) Or IsEmpty(['Sheet name'!D3]) Then[/code]

or similar, to abort the save operation if any of several cells are blank.


Let me know if this works well for you!


Peace,

Asa
 
I've updated the macro to be a little more flexible and sophisticated, and tested it :)


A problem with the macro I first posted is that there was no way for YOU to save the workbook with blanks in it.


This revision among other things, allows the user to save the workbook if they insist. They are simply warned against it, with a default to cancel the save operation and return to the workbook.


Here's the revised code:

[pre]
Code: 
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Worksheet As Worksheet
Dim RequiredCells As Range
Dim EmptyCell As Range
Dim EmptyCells As Variant

Set Worksheet = Worksheets("Sheet Name")
Set RequiredCells = Worksheet.Range("C3,D3")

EmptyCells = Null

On Error Resume Next ' SpecialCells will generate an error if there are no blanks.
For Each EmptyCell In RequiredCells.SpecialCells(xlCellTypeBlanks).Cells
If IsNull(EmptyCells) Then ' FIRST empty cell found
EmptyCell.Select ' Move selection to the empty cell.
EmptyCell.Show ' Make sure it's in the window
End If
' Build a list of empty cells for the MsgBox
EmptyCells = (EmptyCells + " and ") & Replace(EmptyCell.AddressLocal, "$", "")
Next
On Error GoTo 0

If Not IsNull(EmptyCells) Then ' Empty cells found
' Cancel Save operation unless user clicks "YES" button
Cancel = MsgBox(Title:="Required data missing.", _
Prompt:="A value is required in " & EmptyCells & _
" before the workbook should be saved." & vbCrLf & _
vbCrLf & _
"Save anyway?", _
Buttons:=vbYesNo + vbQuestion + vbDefaultButton2 + vbMsgBoxSetForeground _
) = vbNo
End If

Worksheet.Activate

Set EmptyCell = Nothing
Set RequiredCells = Nothing
Set Worksheet = Nothing
End Sub
Change the following lines:

Set Worksheet = Worksheets("Sheet Name")
Set RequiredCells = Worksheet.Range("C3,D3")
[/pre]
as required to specify your worksheet name and the cell or cells that are required, separated by commas.


The code also specifies which cells were left empty in the message dialog and selects/focuses on the first of those cells to point the user in the right direction.


Hope this is helpful!

Asa
 
Hi!

I have a similar problem with a slight bit of an issue:

I have a financial analysis file, whereby if certain values go above a fixed % or amount, the user needs to provide a reason for the variation. So lets say Column A has 2012 figures, Column B has 2011 figures, and Column C and D are variation in amounts and % respectively. For the threshold, I have put value in Cell X1 and Cell Y1. Reasons are to be provided in Column E. I have put conditional formatting in Column E, so that if the values in C/D exceed the values in X/Y, the cell color becomes red thereby showing the user that reasoning entry is mandatory (for e.g. it may be mandatory in E5 and E7 only). Now I wish that file is not saved unless a value has been entered.


This part is straightforward and I hope the script in the thread above can help. BUT ... here comes the problem part.


In next month, when I update the figures, the red highlighted cells will change. Also, I have to prepare this file for many subsidiaries (25+). So I can't possibly, at each month, re-write the code for each of the 25+ companies to replace the cell no. in the scripts. The cells which should not be left blank will number around 15-20 in each sheet and my file will have 2 sheets. So it will be a lot of time consuming exercise.


Is there a way possible that in the script I can perhaps put that conditional formatting logic? That if those cells which are red (or those cells in column E for which the corresponding values in same row for column C/D are above threshold in X1,Y1) are left blank, then generate the error message for the save?
 
Hi alirazafazal,


Welcome to chandoo.org and we glad to have you here.


Please read the three green sticky posts at the begining to understand how this forum works/operates.We, in Chandoo.org, do not encourage cross postings as it tends to create lot of confusions.


I would request you to please start a new post giving a proper subject line to your problem. Somebody who reads the post, will definitely get back to you with a proper solution.


Looking forward to your co-operation in order to provide you the best possible solution as expected.


Best regards,

Kaushik
 
The revised code helped me alot, but I need something extra...


I want to add a "password" to save the spreadsheet when you click Yes. This will enable mangement to save the sheet should need be.


The Password does not have to be encrypted, it can be hardcoded. It would help if it is hashed in the input dialog.


Here is a Flow Chart to simplify:

-----------

Action= Save

Result= Confirm Dialog

Action= Yes

Result= Password Dialog

Action= Accept

(Assuming the Password is Correct)

Result= Do Save

(Assuming the Password is Incorrect)

Result= Do Cancel

Action= No

Result= Do Cancel
 
Here follows my second attempt at making the flow chart:


Action= Save

Result= Confirm Dialog

- | Action= Yes

- | Result= Password Dialog

- - - | Action= Accept

- - - | (Assuming the Password is Correct)

- - - | Result= Do Save

- - - | (Assuming the Password is Incorrect)

- - - | Result= Do Cancel

- | Action= No

- | Result= Do Cancel


Please Note: The Yes and No Option both belong to the confirm dialog...
 
You must log in or register to reply here.
Back
Top