Data Form

Excel has a built-in form that you can use to view, find, and edit data in a list. The feature is not available on the Ribbon, so you need to add it to the Quick Access menu if you want to use it through the user interface. Right-click the Quick Access menu and choose Customize Quick Access Toolbar to open the dialog box shown in Figure 6-12. Select the Customization button, if necessary, and from the drop-down above the left list box, select Commands Not in the Ribbon. Find the Form command and add it to the Quick Access menu.

Personalize Formulas Proofing Save

Advanced

Customization

Add-In s

Trust Center Resources

Figure 6-12

Customize the Quick Access Toolbar.

Choose commands from* • Commands Not in the Ribbon

» • 1

Fit Drawing to Contents

Jtk Flip Horizontal

Flip Vertical

Os|

^ Form...

(J, Free Rotate

i- Freeze Panes

J Group Box (Form Control]

0

[H Place Quick AccessToolbarbelovvthe Ribbon

[H Place Quick AccessToolbarbelovvthe Ribbon

Customize Quick Access Toolbar i

For all documents (default)

Customize Quick Access Toolbar i

For all documents (default)

Save

0 Undo

o Repeat

L^ Quick Print

Tjl Form,.,

This feature can be used with a normal range of data or a Table. If you select a single cell in the data, or select the entire list, and click the Form button, you will see a form like the one in Figure 6-13.

If you record this process, you will get code like the following:

Range("B2").Select ActiveSheet.ShowDataForm

—' ll*' Heine. insert

. : wiem

mm**)

Table TddIs-

Page laycul Form u 1 as

Dots

\Ttsw Ci'iiEltcer

Dsig.ri

■<w -

*■ ï

1

E3

ftl

Roberts

ï

A ; s

c

&

e

f

G

h

1 J K

L Alg

4

5

BH-H

- iL^H -

imrTTinThi' iLmm

•mawnni.i

¡¡sa».

¡saa-

111©

6

Jan 01, 2MB Robefts

NSW

Oranges

S03

15

13545

lof^iß

7

Jan 01. 2QQ6 Roberts

TAS

Oranges

33+

15

4965

Datei

beeeeb te i

y

Jan 02 2006 Smith

qld

Mangoes

299

20

5980

Customer

New 1

9

Jan OB 20061 Roberts

~iqld

Oranges

612

15

9130

10

Jan OS, 2006 Roberts

VIC

Apples

307

12.5

11337 5

State;

|nsw

Delete |

11

Jan OB. 2006 ftradastt

TAS

Pea ra

107

16

1926

Product:

1 Oranges

SSSKSC t i

Vi

Jan 10, 2006 Roberts

VIC

Apples

770

12.5

3S25

15

Jan 14. 2006 Smith Jan 14 20OB Smith Jan 15. 2006 Pradesh

MT VIC QLD

Apples

223

15

27875 1930 10035

Rnd Prev |

Oranges

66 S

Price;

[7i

16

Jan 17, 2006 Roberts

NSW

Mangoes

6B1

20

17620

Revenue;

13545

Jan 21 2006 K«f

SA

Pears

624

15

11232

Criteria

18

Jan 22 200B Roberts

QLD

Mangoes

193

20

3860

19

Jan 23. 2006 Smith

SA

Mangoes

255

20

5100

Close 1

20

Jan 27 2D0B Kas

QLD

Mangoes

S

20

120

21

Jan 27, 2006 Kee

vie

Mangoes

31 î

20

6220

0

22

Jan 2B 2006 Roberts

NT

Oranges

3

15

135

23

Jan 28, 2006 Kee

TAS

Apples

70S

12.5

8625

?4

Jan 23, 2006 Kes

NT

Mangoes

441

20

8820

25

Jan :•■•:: 2006 Kee

WA

Oranges

936

15

14040

26

Feb 03, 2006 Kee

NSW

Oranges

301

15

13515

27

Feb 04 2006 Smith

NT

Oranges

631

15

9465

Feb 06, 2006 Pradesh

NT

Apples

181

125

22S25

-

n

^ M Sales ' J

M M

Figure 6-13

If your list starts in A1, and you record selecting the first cell and showing the Data Form, then the recorded macro works. If your list starts in any cell other than A1, and you record while selecting the top-left corner and showing the Data Form, the recorded macro will give an error message when you try to run it. You can overcome this problem by applying the name Database to your list.

If you don't work entirely with U.S. date and number formats, the Data Form feature is quite dangerous when displayed by VBA code using the ShowDataForm method. The Data Form, when invoked by VBA, displays dates and numbers only in U.S. format. On the other hand, any dates or numbers typed in by a user are interpreted according to the regional settings in the Windows Control Panel. Therefore, if you set the date in the British format (dd/mm/yyyy), when you use the Data Form, the dates become corrupted. See Chapter 25 for more details.

0 0

Post a comment