Data validation causing problems when using a data form in Excel 2

I have an Excel 2007 workbook that includes data validation set on a number 
of cells. When using a data form to enter data and I enter an invalid value 
on the form field corresponding to one of those cells I receive the 
validation error dialogue that prompts me to retry.  I enter the correct data 
into that field on the form then close the form.  My worksheet only has the 
data relating to the corrected field entered. All other data entered via the 
form is not entered onto the worksheet.

Is this a bug in Excel 2007?  I previously was using Excel 2002 and found 
that when using a data form in that version, the data validation did not 
apply and only worked when entering data directly into the cdells on the 
worksheet.

Has anyone else come across this problem?
-- 
Peter WA
0
Utf
11/26/2009 6:15:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1644 Views

Similar Articles

[PageSpeed] 56

Yes, the data form in Excel 2007 wipes out all the data in columns 
before the data validation error column.

You could try John Walkenbach's free enhanced Data Entry form for Excel 
2007. It seems to ignore the data validation rules, and enter all the data.

http://spreadsheetpage.com/index.php/dataform/home


Peter wrote:
> I have an Excel 2007 workbook that includes data validation set on a number 
> of cells. When using a data form to enter data and I enter an invalid value 
> on the form field corresponding to one of those cells I receive the 
> validation error dialogue that prompts me to retry.  I enter the correct data 
> into that field on the form then close the form.  My worksheet only has the 
> data relating to the corrected field entered. All other data entered via the 
> form is not entered onto the worksheet.
> 
> Is this a bug in Excel 2007?  I previously was using Excel 2002 and found 
> that when using a data form in that version, the data validation did not 
> apply and only worked when entering data directly into the cdells on the 
> worksheet.
> 
> Has anyone else come across this problem?


-- 
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

0
Debra
11/27/2009 12:00:55 AM
Reply:

Similar Artilces:

How to handle all unhandled exceptions when using Task Parallel Library?
I'm using the Task Parallel Library (TPL) in .NET 4.0. I want to centralize the handling logic of all unhandled exceptions by using the Thread.GetDomain().UnhandledException event. However, in my application, the event is never fired for threads started with TPL code, e.g. Task.Factory.StartNew(...). The event is indeed fired if I use the traditional Thread(threadStart).Start(). This MSDN article (http://msdn.microsoft.com/en-us/library/dd997415%28v=VS.100%29.aspx) suggests to use Task#Wait() to catch the AggregateException when working with TPL, but that is not I want b...

Ingenico i6550 on 2.0.0114
Hi All, I am having a hard time finding any information on installation and configuration of the i6550. Would anyone out there be willing to share their documentation and/or procedures with me? I followed the KB article 935588 but all I get is a blank screen once the device boots. I have gleaned that I need several things to make this work: Form Designer for the device Ingenico OPOS driver newer than 2.4 (?) Better configuration documentation Sample receipt template (?) I would appreciate any detailed documentation that I can get my hands on. Thanks all very much! I installed 7 of th...

Show only specific Series in the data table
Any way to show series A - D in the graph and not on the data table and series E-H on the data table and not on the graph? The second part is easy: format the data series to have no Line and no Makers I know of no way to achieve the first part best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters REMEMBER: Microsoft is closing the newsgroups; We will all meet again at http://answers.microsoft.com/en-us/office/default.aspx#tab=4 "Legends" <Legends@discussions.microsoft.com> wrote in message ne...

Can't Reference A Custom Control On a Custom Form
Does anyone know why I can't reference a custom control (combo box) on a custom form? I use the following: Set FormPage = Item.GetInspector.ModfiedFormPages ("Newform") [Works OK] Set ControlX = FormPage.Controls("cboRequester") [DOES NOT WORK. CODE STOPS AT THIS POINT] I am using Windows 2000 (as a non-ADMIN user) and Outlook 2000. All help is appreciated. ...

Strange(?) OWA login problems
Sorry for this rather long message - but I feel lost. I have for a long time had the problem that some of my users can not use OWA to log on to the Exchange server. First one user could not logon, then the next until 4 had permanent problems. The logs showed virtually nothing. (The tests below are made on the internal network to make sure there are no routing or firewall issues. I have Win2k (SP4), Ex2k (SP?), IIS5. The server is the domain controller. IIS has 5 domains (none is the 2000 domain). The test were run from an XP Pro with user A logged in to the domain) Today I decided to take a...

Comparing 2 columns
Column A is full of names (none repeating). Column B has names as well, and the same name MIGHT already be in column A. I want a formula in column C that will first look at cell B1, then search all of Column A, and either answer YES or NO if it is found. I though an If statement would work, but it isnt. I wrote the following - =IF(B1=(A1:A25),"Yes","No") However, this is only returning NO as an answer, even if the name in B1 IS located in the range of A1:A25. Am I doing something wrong, or does this only work for values and not text ? Using Excel 2000, SP2 ly...

sheet tab #2
I want to change font size of sheet 's name. What should I do? thanks ---------------- 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" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=46907739-afa1-4f71-aca6-a3a341bef4ec&dg=microsoft.public.exc...

Excel files won't come up
I am having trouble bringing up any Excel file. I get an error message with a error signature like this AppName:Excel.exe AppVer 9.0.0.2719 ModName:mso9:11 ModVer:9.0.0.2720 Offset:00045909 When I look up the tech information at the bottom I get this c:\docume~1\HarryG~1\Locals~1\temp\2958_appcompat.txt I sent this message last week and the remark was to double click and copy the error and reply. I did and I haven't seen any reply yet. I really need to use the Excel files which I use often. All other ofice files OK Help!!! I don't have a guess about what's ...

Create Pareto Chart in Excel? #2
Thanks for the responses. The data is in a sorted frequenc distribution. I have the data analysis add-in installed, but i couldn' locate the data analysis pull down or the histogram with pareto option I'm running excel 2000, I can still create a pareto with this versio correct -- Tycon2 ----------------------------------------------------------------------- Tycon22's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1626 View this thread: http://www.excelforum.com/showthread.php?threadid=27698 The bottom entry on the Tools menu should be "Data Analysi...

Using Drag and Drop for non Adjacent Cells
Hi, I have an Excel work book which has a row of totals in cells which are 6 columns apart. Eg the 1st cell is K71, the next Q71 the next W71etc etc up to a maximum of 25 cells. In another sheet within the same book I have a totals sheet which I need to display the totals from the 1st sheet. I have copied the 1st 2 references from the original sheet into cells which are adjacent to each other in my totals sheet with the intention of dragging and dropping for the next 23 or so cells hoping that Excel would pick up on the fact that the referenced cells are 6 rows apart. But this does not ha...

Entering data
Please help me enter data into cells that seem to be locked. In article <15a201c43e09$dbea4e10$3501280a@phx.gbl>, "Alicia Contreras" <anonymous@discussions.microsoft.com> wrote: > Please help me enter data into cells that seem to be > locked. Hi Alicia, try: Tools > Protect > Unprotect Hope this helps! ...

Hi I'm New and need help with Excel Basics
Please can anyone help me answer any of these questions: How would you add the contents of the cells from A1 to A10. Give 2 ways to do this. (hint: Look up "Examples of common formulas" in Excel help How do you format a cell to have dollar signs next to the numbers? How do you add the contents of cells A1 through A10 on sheet 1 and make the answer show up on sheet 2? How do you rename a sheet? Can I delete the sheets that I don't need? If so how? How do I freeze rows or columns of the sheet so that they don't' move when you scroll up or down? Example: My n...

Outlook 2000 (Win2K Pro) connection problems
Hi Everyone, I have a few workstations that cannot connect to an Exchange 2000 server from Outlook 2000 (Win2k Pro). Access is still available via OWA and everything was working as of this past Friday. I receive a "The logon credentials supplied were incorrect. Make sure your username and domain are correct, then type your password again." error message when the correct credentials are supplied and tried to use this http://support.microsoft.com/kb/321652/en-us KB with no success. On my XP Pro workstation with Office 2000, I am able to connect as normal. So far I have tried, re...

Can't see 2 open workbooks listed in the Window tab
Using Excel 2003 When I have two workbooks open I think I should see both of the workbooks listed in the Window tab on the Menu Bar. I only see the workbook file I am in. What can I do to correct this? This is not a question on charting, but let's be nice for a change :-) The 'problem' you describe pops up when you open Excel twice, what might happen when you double-click the file from the Explorer to open it while Excel already is open. It will not happen if you have Excel open and open the file from within. HTH, Henk "TomD" wrote: > Using Excel 2003 &...

Can't save new Excel Doc
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Help! I have created an excel spreadsheet (with multiple sheets in one doc) but can't save the document. I keep getting an error message that either: <br> - file name or path name does not exist <br> - file is being used by another program <br> - name is the same as another file <br><br>Can anyone tell me why I can't save. (To clarify, none of the &quot;reasons&quot; it won't save are applicable as I have double checked each of those). <br><br>Thanks, <br> Moll...

Simple problem
I am doing something obviously stupid but can't see what In a spreadsheet I have data in cols A and B In cell B1 I have =(a1-0.375) I have copied this to b2.b14 with the result shown: 1 0.625 2 0.625 3 0.625 4 0.625 5 0.625 6 0.625 7 0.625 8 0.625 9 0.625 10 0.625 11 0.625 12 0.625 13 0.625 14 0.625 If I go to cell b2 and then left click in the formula bar then hit enter, b2 evaluates to 1.625. Same with b3, b4 etc. What have I done to stop the formula evaluating correctly after I have copied it? Thanks...

New in Money'04: Online Services Valid Two or Three Years from first use
From the new Money 2004 pages: http://www.microsoft.com/money/info/comparison.asp "Online services valid two or three years from first use. Online bill pay, downloading transactions from your financial institution, downloading stock quotes, and the ability to synchronize with MSN� Money Plus or MSN Money expire two years from first use of Microsoft Money Standard or September 1, 2006, whichever is earlier. Online bill pay, downloading transactions from your financial institution, downloading stock quotes, and the ability to synchronize with MSN� Money Plus or MSN Money expire three year...

Format Data Entry
Is there a way to format data so that when entering someone's height as 511 it will come up as 5'11" and when entering 63 it would come up as 6'3" ? Thanks. There may be a way, but I wouldn't do this. Instead, use two cells (feet and inches). Or use one cell and enter total inches. It'll make any further arithmetic much, much easier. Ayuda wrote: > > Is there a way to format data so that when entering someone's height as 511 > it will come up as 5'11" and when entering 63 it would come ...

Adding button using wizard in Access 07
Hi, I'm trying to add a simple button to advance the record page to a new blank record using the wizard. Here's what I've done: Created the form which works fine. I've added records to test it. In Design view I click on Use Controls Wizards to activate it. Click on Button to select it. Click on the form to place the button and activate wizard. This does happen. In the wizard I elect Record Operations under Categories and then Add New Record for the Action. Click Next and then select Text to identify the button. Click Next again and change name to Add Record. Clic...

named excel lists
I'm somewhat of a newbie, so I may not be asking this question with the correct terminology. I'd like to define a name and attach it to the range of an "excel (2003) list". If I add a new row or column to the "excel list", then the range is suitable extended. How? Try Debra Dalgleish's nice coverage of Dynamic Ranges at: http://www.contextures.com/xlNames01.html#Dynamic Look for "Create a Dynamic Range" -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com ---- "hidden" <hidden123456@hotmail.com...

Excel startup with buttons
I was wondering if it is possible to have a blank screen displayed when a user opens up excel, and have them select a button to take them to a particular worksheet in the excel workbook. I have figured out how to use code in visual basic to create the buttons, but when I open the workbook, it displays the "button screen" overtop of whatever the last page was when the workbook was last used. I'd rather not have any of the worksheets visible behind the button choosing screen. Any suggestions? -- alanad ------------------------------------------------------------------------ a...

how to use collapse/ expand fuction in excel like outlook
...

Problems with syncing with MSN since udating Bank of America
I am a Bank of America customer so I updated the account per last weekends instructions. Every since then I cannot sync with MSN Money on line. Also a number of other accounts no longer updata including ING Direct, Chase, Circuit City. I uninstalled and reinstalled Money 2006 Deluxe. I tried earlier versions of my back up files to before the update but nothing works. Oh by the way I installed the Beta of IE 7 but have since uninstalled it. I am not sure if the problems started when I did the Bank update or when I did the IE 7 beta. I have tried phone support but no luck. Any sugg...

Using CHttpConnection without caching under CE
I'm pulling XML pages from a web server and have run into a problem. It seems that CHttpConnection under Windows CE doesn't support the INTERNET_FLAG_DONT_CACHE flag. In fact, in inet.cpp, OpenRequest(), there's this piece of code: #if defined(_WIN32_WCE) ASSERT((dwFlags & ~(INTERNET_FLAG_NO_AUTO_REDIRECT | INTERNET_FLAG_SECURE)) == 0); I've tried every alternative I could think of, like trying a no-cache flag in SetOption(), m_pHttp->SetOption(INTERNET_OPTION_CONNECT_RETRIES, 1, INTERNET_FLAG_DONT_CACHE); which fails another inet.cpp assertion, or adding a H...

Data Migration Manager
Hi, Installed CRM 4 to server which already had MOSS installed so used port 5555 for CRM. Now when I try to run DMM it can't connect to the server (installed on the server) when I use the URL http://crm:5555. The URL works for IE. Anybody have any ideas on this? Ta. Pete. -- Peter Clements Certified Microsoft Dynamics CRM Professional Capgemini UK ...