The Show DataForm Sub Procedure

Using ActiveSheet.ShowDataForm means exposing yourself to one of the most dangerous of Excel's international issues. ShowDataForm is the VBA equivalent of the pre-2007 Data O Form menu item (which is not available by default in the Excel 2007 Ribbon, but can be added to the QAT by selecting the Form command from the All Commands list). It displays a standard dialog that allows the user to enter and change data in an Excel list or database. When run from Excel, the dates and numbers are displayed according to the WRS, and changes made by the user are interpreted according to the WRS, which fully complies with the user interaction rules stated previously.

When used in code, ActiveSheet.ShowDataForm displays dates and numbers according to U.S. formats but interprets them according to WRS. Hence, if you have a date of February 10, 2007, shown in the worksheet in the dd/mm/yyyy order of 10/02/2007, Excel will display it on the data form as 2/10/2007. If you change this to the 11th (2/11/2007), Excel will store November 2, 2007 in the sheet. Similarly, if you are using Norwegian number formats, a number of 1-decimal-234 will be displayed on the form as 1.234. Change that to read 1.235 and Excel stores 1235, one thousand times too big.

Because this is a rarely used feature, our suggestion is to leave it buried in the Ribbon command well and write your own data-entry UserForm.

+1 0

Post a comment