Thus, this approach protects any formatting, not just enforcing an Accounting format.
They result in something that doesn't so much force a particular format, but it prevents the formatting of a cell from being changed from whatever it was before. For instance, the following examples use both the SelectionChange and Change events of the Worksheet object. If you wanted a bit more flexibility, then you could use a different set of event handlers. If you want to force the format on a different cell range, then just change the assignment line. In this example, the cell that you want to retain the Accounting format in is E2, as assigned to the rngToCheck variable. Private Sub Worksheet_Change(ByVal Target As Range) For instance, you could include an event handler that looks at where data was entered and ensures that any changes to those cells retain the desired formatting.
If you don't want to change how parsing is done, the best approach may be to add some event handlers to your worksheet. This is why I mentioned that this approach may be acceptable for simple worksheets you'll need to make the determination if your worksheet qualifies. Because Excel subsequently parses any entries according to Lotus rules, your users may conclude that your worksheet doesn't work properly since it doesn't follow the same rules that other Excel worksheets do. There are drawbacks to this approach, though. Because it was not parsed as a date, then the Accounting format is left associated with the cell, as desired. Thus, if someone enters 11-16-13 into a cell, then it is parsed as "eleven minus sixteen minus thirteen" and shown in the cell as -18. This means that dates are no longer parsed as dates, but as a formula. This particular option causes Excel to evaluate (parse) entered information in the same way that Lotus 1-2-3 did. Make sure the Transition Formula Entry option is selected.The Advanced options in the Excel Options dialog box. Scroll to the very bottom of the advanced options.At the left side of the dialog box click Advanced.In Excel 2010 or a later version display the File tab of the ribbon and then click Options.) (In Excel 2007 click the Office button and then click Excel Options. If you want a quick way to prevent the formatting change in simple worksheets, follow these steps:
#Format date cells in excel not working how to
Carol wonders if anyone has any ideas on why this happens and how to correct it. This is fine if the user sees the error and corrects it, but many times this is happening in a template with "boilerplate" text and the template is protected with no access to formatting cells. However, if she tries to enter a dollar amount in that cell again, it will not go back to the "Accounting" format the cell remains in the date format. In a worksheet, Carol has a cell formatted to "Accounting." If someone accidentally enters a date (mm/dd/yy) in that cell, Excel automatically changes the formatting of the cell to show the date correctly.
0 kommentar(er)
