prevent blank cell #2

How do I prevent users of my workbook from making a cell blank? I want them 
to be able to edit it so have left it unprotected but I donot  want them to 
be able to leave it blank. It looks as if the  Data Validation command should 
do it, but  I can't get it to work.
0
Stratford (2)
3/26/2005 8:17:02 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
237 Views

Similar Articles

[PageSpeed] 43

Your repeat post appears as a reply to the thread in my browser.  As a 
result, I almost didn't read your post on the assumption that your 
question had already been answered.  I suspect many others did ignore 
your post for the same reason.

Assuming that you know how to set up the validation condition (uncheck 
"Ignore blank", set up a "Custom" validation, such as =ISNUMBER(A1) 
where A1 is the cell to validate, etc.) then I presume that you are 
hitting a known weakness of Data Validation, where deleting data from 
the cell (without replacing with anything new) leaves the cell empty, so 
the Data Validation event does not fire (presumably the faulty logic was 
that there is no data to validate ...).  You can roll your own patch in 
VBA along the lines of
http://groups-beta.google.com/group/microsoft.public.excel.misc/msg/e2945c7bccd60819

Jerry

Ken Stratford wrote:

> How do I prevent users of my workbook from making a cell blank? I want them 
> to be able to edit it so have left it unprotected but I donot  want them to 
> be able to leave it blank. It looks as if the  Data Validation command should 
> do it, but  I can't get it to work.

0
post_a_reply (1395)
3/26/2005 12:27:25 PM
I like to use an adjacent column and put a formula like:

=if(trim(a1)<>"","","<-- Don't leave blank")

I bold it in big red letters.



Ken Stratford wrote:
> 
> How do I prevent users of my workbook from making a cell blank? I want them
> to be able to edit it so have left it unprotected but I donot  want them to
> be able to leave it blank. It looks as if the  Data Validation command should
> do it, but  I can't get it to work.

-- 

Dave Peterson
0
ec357201 (5290)
3/26/2005 12:30:09 PM

"Jerry W. Lewis" wrote:

> Your repeat post appears as a reply to the thread in my browser.  As a 
> result, I almost didn't read your post on the assumption that your 
> question had already been answered.  I suspect many others did ignore 
> your post for the same reason.
> 
> Assuming that you know how to set up the validation condition (uncheck 
> "Ignore blank", set up a "Custom" validation, such as =ISNUMBER(A1) 
> where A1 is the cell to validate, etc.) then I presume that you are 
> hitting a known weakness of Data Validation, where deleting data from 
> the cell (without replacing with anything new) leaves the cell empty, so 
> the Data Validation event does not fire (presumably the faulty logic was 
> that there is no data to validate ...).  You can roll your own patch in 
> VBA along the lines of
> http://groups-beta.google.com/group/microsoft.public.excel.misc/msg/e2945c7bccd60819
> 
> Jerry
> 
> Ken Stratford wrote:
> 
> > How do I prevent users of my workbook from making a cell blank? I want them 
> > to be able to edit it so have left it unprotected but I donot  want them to 
> > be able to leave it blank. It looks as if the  Data Validation command should 
> > do it, but  I can't get it to work.
> 
> Thanks - a good solution - I knew there had to be a way! Ken
0
3/27/2005 2:11:02 PM

"Dave Peterson" wrote:

> I like to use an adjacent column and put a formula like:
> 
> =if(trim(a1)<>"","","<-- Don't leave blank")
> 
> I bold it in big red letters.
> 
> 
> 
> Ken Stratford wrote:
> > 
> > How do I prevent users of my workbook from making a cell blank? I want them
> > to be able to edit it so have left it unprotected but I donot  want them to
> > be able to leave it blank. It looks as if the  Data Validation command should
> > do it, but  I can't get it to work.
> 
> -- 
> 
> Dave Peterson
> Thanks Dave - that's a useful way round it..
Ken
0
3/27/2005 2:11:02 PM
Ken Stratford wrote:
....

>Thanks - a good solution - I knew there had to be a way! Ken


You're welcome, glad it helped.

Jerry

0
post_a_reply (1395)
3/28/2005 12:08:07 PM
Reply:

Similar Artilces:

Video blanking
I have a problem sometimes when i launch outlook 2003 , my video screen goes blank and i have to reset my laptop. what would be causing this problem. I have tried to repair and detect many times and have even reformatted and reinstalled my os xp pro and then outlook 2003 and still having the same problem.. Any help is greatly appreciated and need. thank you for your time Specify blank; you see th eapplication hang, the screen turn off...? What is logged to the Event Viewer? Updated you're videocard driver already? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of ...

EMAIL ADDRESS #2
WHEN I GOT A JOB AT ALLSTATE, OUTLOOK STILL HAD THE EMAIL ADDRESS OF THE PERSON THAT WORKED HERE BEFORE ME. HOW DO I CHANGE IT TO A NEW ADDRESS??? SOMEONE PLEASE HELP!!!!! First, you'll need to turn off the Caps Lock key, which looks like shouting, and tell us what version of Outlook you're using. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "SAMANTHA" <NIKKISTONE@ALLSTATE.COM> wrote in message news:46a101c4734d$e2d67cc0$a301...

Removing Blank rows in Pivot Tables
Hi guys. I have created a pivot table, from a range of data which looks like this: Name Subject Subject Subject a result result b result c result result result d result result Hopefully that comes out OK. You can see not everyone has a result for each subject. I want to list each result for each subject they take ONLY. At present, the table supplies each subject, with a value of nil. I just want it to display those they have results for! Hope you can help, Tom. PS The actuall data will be about 300 student...

How do I restore format cells menu?
I am working in Excel 2002, multi-page workbook. On some pages "format cell" is not operational - neither from the toolbar or by right-clicking mouse. Command is there but produces no result. On other pages in the same book all is well. There are no protected pages, and this is happening only in one particular workbook. Have tried reset to no avail. When copy and paste material to properly functioning page problem is carried as well. Any ideas would be muchly appreciated. ...

Prevent the sales from Tendering when another Item is Scan at POS
Hello, We have serveral items that our Retail does have price assign to them. The cashier will scan the next item. They will not notice the last item they did not enter a price. Now the 2nd item is the price for the 1st item. Then the cashier will tender out the sales without noticing they have an incorrect total for this transaction. Then the store manager have to performed a post void for this transaction, which hold up the customer awaiting to check out. Have anyone else encounter this and know a way to limited sale from being total out of an acceptable sales amount...

CRM 1.2 Crystal Reports
Hello, Running into some problems with Installing the Crystal Reports that comes with CRM. I get the error that I currently hace Cryatsl Reports 9.2.0 and I need to upgrade to Crystal Reports 9.2.2 before I install. I have Visual Studio 2003 installed on this machine and it is the only item that I could have on this machine with Crystal Reports version 9.2.0 (otherwise a formatted clean system). Any one have any idea what i need to do to install this? Thanks, Chris If you contact crystal decisions they can give you a download location for version 9.2.2 and then you use your own access...

preventing date from changing
I have an If condition that sets the date to "today()" true or "today() + 1" if false. My question is how do I prevent the date from changing? If the date is set for today, then tomorrow I don't want to "update". How can this be done? thanks You could copy the cell, and paste as values, before you close the file. (Edit>Paste Special, Values) Or, you could enter today's date in a cell in the row (Ctrl+; ), then refer to that cell in the formula. For example, instead of: =IF(A2="Local",TODAY(),TODAY()+1) enter: =IF(A2="Local...

outlook feature to prevent forgetting attachments
Often people forget to send attachments with emails. Very often they will write something along the lines of "I have attached the minutes from our meeting" and then forget to atcually attach a file. Is there a way therefore that outlook can check the text of an email for words like attach, attached, attachment and query the user before sending if there does not appear to be an attachment? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree"...

how to cell reference a Chart title
I want to reference a cell for chart title so that excel gets it automatically from a cell without typing in Title tab/Chart title box. I tried to use indirect command but it didn't help also I search help for charting in Excel help file but couldn't find anything. Is it possible? TIA, Rasoul Khoshravan, Tabriz, Iran See Dynamic Chart Title http://www.tushar-mehta.com/excel/newsgroups/dynamic_chart_title/index.html "Rasoul Khoshravan Azar" wrote: > I want to reference a cell for chart title so that excel gets it > automatically from a cell without typing in Title t...

How to remove blank space?
I have created a line chart from the data source that contains either blank space or is null. I tried to remove blank space or cell has value is null from this line chart but could not. Please help! Thanks, Daniel Replace the empty cells by #N/A. Either =IF(A1="",NA(),A1), or modify an existing formula appropriately. -- David Biddulph "Daniel" <Daniel@discussions.microsoft.com> wrote in message news:A05DE8FC-BE80-42EF-9330-13010670F751@microsoft.com... >I have created a line chart from the data source that contains either blank > space or is null. I t...

Grouping rows based on like data in a cell and then separating them
Lets say you have a 15,000 line spreadsheet covering about 8,000 different items. Some items have multiple lines devoted to them. Each line is a separate transaction related to that item. After sorting the list by the item name, I now wish to have excel insert a blank line between each different name. So basically this: alpha alpha beta beta beta gamma delta delta would become this: alpha alpha beta beta beta gamma delta delta Is there any way to do that other than manually inserting rows? An automated process would save a LOT of time. Sub InsertRow_At_Change() Dim i As Long ...

Pivot table field #2
Hi, I have data in a RDBMS table as storeid,month,product, sales,returns, quarter to date, year to date . I want to calculate a report so that the months should show up as n,n-12,n-24 etc so that I can compare the same data year over year without selecting the month from the drop down and checking/unchecking of months. Else What is an alternate design of the table that renders itself to pivot if I want to capture the above metrics Thanks Sd ...

plus sign before cell reference in formula
what is the difference between this formula: =+C31*$D$14 and this one: =C31*$D$14 both formula give the same results. what is the purpose of the plus sign? The plus sign serves no useful purpose whatsoever It does tell us that maybe the originator of the workbook began life as a Lotus 123 users where formulas began with +. And that they have not yet found that Excel does not require them (old habits die hard - or not at all) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "KrispyData" <KrispyData@discussions.microsoft....

multi-currency #2
I am using Integration Manager to import GL entries from a system that uses Canadian currency. Will I be bringing in US balances and a Canadian exchange rate to get to the original Canadian value or will I need to bring in an exchange rate for each JE? You have 2 ways to approach this: 1) You can import the transactions in originating currency (Canadian dollars), but making sure you have previously setup an exchange rate in GP for each date the transactions occurred. That way, all you will need to specify in your integration is the transaction currency. 2) You can import the transa...

Previous Cell Comments Appearing when Mousing over cells in one column
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I am seeing a previous cell comment when I mouse over another cell in a different column of cells. It only happens in the one column and its the same cell comment. How can this situation be fixed so I will not see this comment? Thanks. You actually can delete comments. Go to help menu and click on search type in comment or commenting and see if you can find deleting comments. > Thanks but this is not the problem. I don't want to delete the comments. For the moment I reverted to a previous version that I had...

cell reference in a formula is called
Please confirm the correct answer to the below question cell reference in a formula is called? I'm not sure what you are asking for. In a user defined function in VBA, Application.Caller will return a Range reference to the cell which called the function. In a formula, ADDRESS(ROW(),COLUMN()) will return the address of the cell containing the function. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "???????????" <???????????@discussions.microsoft.com> wrote in message news:22EA36DA-A680-4414-87DF-EBD3BA96820...

moving and dragging multiple cells together without merging?
is it possible to do this say i have a row, A2-F7 there is data in each cell can i make it so i move any of the cells (lets say C2) up or down and the whole row will all move in a unit as if it was merged? i dont want to officially merge though because then that will screw up the data inside the cells -- pete321 ------------------------------------------------------------------------ pete321's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35354 View this thread: http://www.excelforum.com/showthread.php?threadid=551972 Instead of a cell, use the row count...

prevent user from deleting a tab prevent running macro from menu
how can one prevent a user from 1. deleting a specific tab in a workbook? 2. prevent a user from running a macro from the tools menu (where user can only run a macro from a command button)? thanks! #1. Protect the workbook's structure. In xl2003 menus: tools|protection|protect workbook|check structure #2. Make the sub private: Sub Testme() becomes Private Sub testme() This will stop the user from seeing that subroutine--but if they know the name, they can still type it and run it. joemeshuggah wrote: > > how can one prevent a user from...

Mail Delivery #2
I have a user who doesn't always receive his email from a certain user(different server, same site). One day he will receive the mail message and then he will not receive it the next. The person that is cc'd does receive it though. Any ideas? I do not see any error messages in the event log. Thanks in advance, Mary ...

Saving Worksheet #2
Can you save a single worksheet instead of the entire workbook. It is to big as a workbook. open a new workbook>go back to the original>right click >sheet tab>move or copy>create a copy>to book -- Don Guillett SalesAid Software donaldb@281.com "Bobby28" <Bobby28@discussions.microsoft.com> wrote in message news:0F253FD4-11A4-4941-B629-68B13AC7E659@microsoft.com... > Can you save a single worksheet instead of the entire workbook. It is to big > as a workbook. use the "copy or move sheet" in <edit> and select "to new workbook&quo...

Re: preventing distribution lists from printing
I work for a large corporation and I often get outlook mail with ver long distribution lists. Is there any way to prohibit these lenght list from printing so that I only get the message? Thank - buddy ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message548641.htm ...

cells not right when save as html
When I save my workbook as an html page the formatting on my 45 degree cells at the top of the page are no longer at a 45 degree angle. Is it possible for this formatting? ...

Prevent calculation of fields in split view
Hi all! I have following problem: Most of my forms are displayed in split view, within some of them i am doing different calculations, displaying the calculated values in text boxes. Since some of those calculations are quite time-consuming, i dont want Access to calc. the values for _each_ record in the split-view (which it is doing right now). I want it to just calc the values for the record currently viewed, is there any way to do this? best regards, --Michael ...

Setting column width for blank columns
I would like to add something to a macro that will make all blank columns a set width of (1"). Any suggestions? -- gmr7 ...

Merging two profiles #2
Hi there I just had a new profile installed on my computer for an exchange server. My previous profile is for my personal POP3 accounts. Is there any neat way to merge the two profiles and retain access to all my existing emails? Thanks very much Lloyd pinkfloydfan <lloyd.greensite@googlemail.com> wrote: > I just had a new profile installed on my computer for an exchange > server. My previous profile is for my personal POP3 accounts. > > Is there any neat way to merge the two profiles and retain access to > all my existing emails? No need to ask more than once. Ope...