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
443 Views

Similar Articles

[PageSpeed] 42

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:

How to refer to active cell on spreadsheet?
Excel 2000 on Windows 2000 I'm looking for functionality similar to "ActiveCell" in VBA. I have week-ending dates in Column A. Columns B through H are the days of the week, Mon - Fri, so cell B1 = "Mon", C1 = "Tue", etc. I want B2 to be whatever the date value in Column A in the current row is, minus 6 (my weeks end on Sunday.) So if my active cell is B6; A6 = 1/19/03; and then I want B2 to read 1/13/03 (Monday's date), or 1/19/03 - 6. A B C D E F G H --------------------------------------------------...

Calendar Issue #2
Hello, We are running Exchange 5.5 and using Outlook 2000. We have an issue. When somone sends a meeting invitation to a specific person another user that was not invited gets the invite as well. Any ideas why this user would be getting invited? There are no forwards set up either. Thanks! On Wed, 26 Apr 2006 17:32:18 -0400, "Kevin Thomas" <kthomas00@verizon.net> wrote: >Hello, > >We are running Exchange 5.5 and using Outlook 2000. We have an issue. When >somone sends a meeting invitation to a specific person another user that was >not invited g...

MS CRM 1.2 CrystalReportViewer Error
Hello, I have installed the german MSDN version of the MS CRM 1.2 - most parts are doing their work, but have invested now hours of searching, but still not found a solution to get the crystal reports running. Always when opening a report (e.g. the activity list report, http://<server name>/reports/viewer/html/viewer.aspx?id=189) I'm receving an error message, like this: "Fehler im Abfragemodul. Datei c8036e4ef934e114.rpt." Translated into english it can be called maybe "Error in query module. File c8036e4ef934e114.rpt." - but didn't found some error mes...

How do I embed an object into a specific cell in Excel?
I wanted to know is there anyway you could embed an icon for a particular word document into a specific cell in Excel. I have no problems inserting a document using the insert--->object function but when i perform filters for example it is not associated with the record and so remains in the location in which it was inserted. I would really appreciate any help with this, and would be happy to clarify further if required. ...

PCI Compliance #2
Got letter from processing company. Claims our system has to be tested quaterly for PCI compliance. $99 year, if it isn't hen a fee of $12.95 is added to bill. I was wondering, since we are not a custom application, shouldn't MS be able to privide us with a letter of PCI Compliance? Using RMS, 2.0.0126, on XP. Bud -- Bud, I get that same letter, and I use a terminal supplied by my processor! I have no way of knowing for sure, but it seems like a scam just to grab some money. I have no idea why I would have to pay a third party to certify the processor supplied and maintai...

invoices #2
In Money 2003, when I receive payment on an invoice, it automatically records it to my register. Is there any way around this? The reason is that I like to make one large deposit, and I want this large deposit to show in my register, not the individual ones. Thanks!! In microsoft.public.money, t rech wrote: >In Money 2003, when I receive payment on an invoice, it >automatically records it to my register. Is there any >way around this? > >The reason is that I like to make one large deposit, and >I want this large deposit to show in my register, not the >indivi...

Current Cost #2
Hello: Aside from using Field-Level Security or Modifier (and forcing the client to have buy either tool), is there any sort of workaround for the fact that the Current Cost field in Item Maintenance can be edited and updated secretly by the end user. A client is complaining that the Current Cost field can be changed by an end user and update Cost of Goods Sold. Any ideas? childofthe1980s The client could always just acquire Customization Site License (far cheaper) but I know of no other solution. This field is generally useless except for visual presentation since the cost layer c...

Change tab color when a tab is used #2
Version 1 -- eluehman ----------------------------------------------------------------------- eluehmann's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1309 View this thread: http://www.excelforum.com/showthread.php?threadid=26242 you can only do this in office 2002 onwards. this goes into the sheet' code module. Private Sub Worksheet_Activate() Me.Tab.ColorIndex = 3 'this would give "Sheet1" a red underline End Sub i tried this, and i dont think it is too useful. as it is, when yo click on a tab, that tab's name is shown with a whit...

2 Macro Questions
If I assign a macro to a forms button on the sheet if I copy the book and load it on my friends computer do the macros go with them. Can a macro button be assign to a cell so when clicked it will print (72,8) from that cell, and what formula would I need? -- Thanks in advance for your help....Bob Vance .. .. .. .. Hi Bob 1. Yes, the macro code is copied as well 2. You can add some code to the Doubleclick event of your button. I'm not so sure what you mean with 'print'? Insert a value in a specific cell. If yes, try the following code: Private Sub CommandButton1_Click() Me...

Saving a worksheet #2
My problem is that when I save a worksheet, the file name I've typed in is automatically converted to a single letter. For example, 'worklog.xls' is converted to 'w.xls'. I don't know why this is happening or what to do to prevent it from happening. I've got Excel '97, and I've made sure I am saving it as an 'Excel '97' document. Pleeeze help! Check to see if Excel is running in Windows 95 compatibility mode. There was a post that said: When I disabled compatibility mode the file name problem went away. http://google.com/groups?threa...

Microsofts way of gainig PROFIT #2
I'm asking you following. When you are selling your product Windows XP you are=20 promising a lot of extras that is in the product. But when the user wants to use them it all of sudden costs=20 700 dollars to get information of how to set it up. Is it Microsofts policy to hide all the costs so that=20 first when the user is hooked on the Program. He will=20 start to pay. Now in Sweden it's like this, that it's illegal according=20 to Swedisch consumer law to suppress information like=20 this. In Sweden you can't tell that some option is=20 included in the price but not tellin...

compare 2 lists and common ones display on the 3rd column
Hi, Is there a way to compare data in 2 columns, and display the same ones that occur in both lists to a 3rd column? Thanks. Elaine. Use a macro:- Sub CompareAndPutinColumn3() j = 1 For i = 1 To Application.WorksheetFunction.CountA(Columns(1)) If Application.WorksheetFunction.CountIf(Columns(2), Cells(i, 1)) > 0 Then Cells(j, 3) = Cells(i, 1) j = j + 1 End If Next i End Sub somethinglikeant elaine wrote: > Hi, > > Is there a way to compare data in 2 columns, and display the same ones > that occur in both lists to a 3rd column? > > Thanks. >...

Delay Calculation of A Cell
I have a cell with the following calculation =IF(AND(C62-C56>0,C70-C66>0),"Yes - Loan Should be Approved",IF(AND(C62-C56<0,C70-C66<0),"No - Speak to your Advisor.","Possible - Depending on Detailed Application, speak to your Banker")) Now it works ok but I want to stop it calculating until the "user" requests an answer. The second part is how do i tell the spreadsheet to remove all data in "unprotected cells"? The idea the user opens the spreadsheet completes a series of cells then selects answer to display the...

Force text to extend beyond cell boundaries
Is there any way to force Excel to display text beyond cell boundaries? I have over two hundred columns that are only 5 pixels wide, and would like to enter a formula in each cell that evaluates to text if condition is met, and evaluates to "" if not. The problem is that th text will not display beyond the right boundary of the cell if there i a formula in the next cell - even if the formula evaluates to "". For a picture, see: http://www.thezac.com/temp/excel.gif In the picture, the text only extends beyond the cell because the cell to the right of it do not contain t...

format a cell to read zero for any negative value
I would like some advise to show a zero value in a cell when ever the sum value of this cell is a negative value Hi, how about: =IF(SUM(A1:A10)<0,0,SUM(A1:A10)) HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261 View this thread: http://www.excelforum.com/showthread.php?threadid=494490 Use a Custom Format, such as General;\0 -- HTH RP (remove nothere from the email address if mailing direct) "Tony" <Tony@discussions.microsoft.co...

clearing a cell and keeping the formula
hi Im new to excel I seem to lose my formula's everytime I go back to enter some data that I may enter wrong for example I have put the formula in and go back and put a number in the cell and find it's the wrong # so I backspace or hit delete and I lose my formula and clear the cell what am I doing wrong??:) -- genevieveg ------------------------------------------------------------------------ genevieveg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30333 View this thread: http://www.excelforum.com/showthread.php?threadid=499995 A cell can contain ...

converting files to blank DVD used to work but now does not.
This is a multi-part message in MIME format. ------=_NextPart_000_008C_01CACA8F.A23D9780 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit I used to be able to download a movie, then put it through 'Koyote Soft' to convert the files ready to be put on a blank DVD. This DVD would then be able to be played on my regular DVD player and come out on the television screen. I have since downloaded an update for this software which is 'Koyote Soft 2010 Version 3.2.0. Since downloading this u...

MS Great Plains 10.0 with RMS 2.0...help please
Hi, I'm looking for somebody who can answer a simple question regarding Dynamics GP RMS integration into GP. I noticed in the RMS integration setup that only one checkbook can be specified, however my client needs to import payments that hit different checkbooks. The import is integrating the payments to the correct cash GL accounts, but all the payments are getting linked with the same default checkbook specified in the RMS integration setup. Is there a workaround for this? Thanks -- MS Great Plains consultant Tee Lopez Hi, Currently we are doing a GP integration...

outlook webaccess does not show contact names #2
Greetings, I have just installed exchange 2003 and do not see any names in the contact list while using outlook web access. Using regular outlook I have many many names in my contact list but it is not showing when I use OWA. Normally i create new mail, click "To" button and my list of contacts appear. Is there anything on the server end I need to set for thit to work in OWA? ...

Blank Image Box
I have a form that is designed to display several OBL based images. I have found as my DB files grow, this is going to present an issue. MY QUESTION is, how would I add an image box with out an image in it. Would I have to have all image box filled with a default image. Reason for this is that some files (records) will have 2-3 images, while other may have up to 6 (6 is the total number). Any and all recommendations are greatly appreciated. -- Work is sometimes hard....but someone has to do it. ...

How to open 2 excel instances by using XP schedule tasks?
I am using XP with SP3 and Excel 2003, when I setup a schedule task to open file1.xls at 9 am, and open file2.xls at 9:15 am, does anyone know whether schedule task will 2 excel instances or 1 excel instance? Thanks in advance for any suggestions Eric Dunno.... But have you tried setting up a shedule to open file1 in 5 minutes from now and file2 in, say 7 minutes? There are, I think at least 3 ways that you can see how many instances you are running: 1) I think its true in 2003 that when they are both open, if you look in Windows/switch windows only the file running in t...

Can't see autoshapes in cells but they do print
Using autoshapes, I put a gold star in some cells to identify where I accomplished what I wanted. When I exit the spreadsheet and then re-enter the spreadsheet, I no longer see the star but if I print the spreadsheet, it prints out. It must be something with what I allow to be viewed but I can't figure it out. ...

Formula with criteria of blank cell
Help! How do I write the formula for the following? I am in "cell I6" and I want to say: If G6 is Blank, then Blank, Els I5 minus G6 plus H6 -- Richard Pit ----------------------------------------------------------------------- Richard Pitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1533 View this thread: http://www.excelforum.com/showthread.php?threadid=26966 =if(g6="","",i5-g6+h6) or =if(g6="","",(i5-g6)+h6) -- Don Guillett SalesAid Software donaldb@281.com "Richard Pitt" <Richard.Pitt.1...

Money 2005 Update #2
I've just recently installed 2005, and have seen the update pop-up boxes appear over the past couple of days. However, I'm not sure if I actually got the update. No box ever gave the message that it was finished or being installed. How can I tell if I have the most recent update? Thanks for your help. In microsoft.public.money, Elizabeth wrote: >I've just recently installed 2005, and have seen the update pop-up boxes >appear over the past couple of days. However, I'm not sure if I actually got >the update. No box ever gave the message that it was finished...

beginner VBA to create 2 columns
Hi, I haven't written in vba in Access before so I'm not sure what to write or what my best approach is to solve my dilemma I am starting a db it'll have a main table with several columns of data. Each month I will have data to import into the larger table. The problem is that I need to be able to create 2 fields that compare from other fields. I know it's not best to have calculated fields in your tables but for our purposes we need it. You have a dataset where everything is collected (currently all in Excel and this is manually done) The data has severa...