Method and system for linking controls with cells of a spreadsheet

• US 5,721,847 A
• Filed: 02/03/1997
• Issued: 02/24/1998
• Est. Priority Date: 10/21/1994
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Johnson, Jeffrey J.
• Primary Examiner(s): SIPOS, JOHN
• Assistant Examiner(s): SIPOS, JOHN
• Application Number:
  US08/789,076
• Time in Patent Office: 386 Days
• Field of Search: 395/326, 395/327, 395/328, 395/329, 395/330, 395/331, 395/332, 395/333, 395/334, 395/335, 395/339
• US Class Current: 715/786
• CPC Class Codes: G06F 3/0481 based on specific properties of the displayed interaction object or a metaphor-based environment, e.g. interaction with desktop elements like windows or icons, or assisted by a cursor's changing behaviour or appearance
• CPC Class Codes: G06F 3/04847 Interaction techniques to control parameter settings, e.g. interaction with sliders or dials
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 9/45512 Command shells

Abstract

A method and apparatus for inserting a selected graphic control within a spreadsheet, chart, or dialog sheet. The graphic control can be selected from a Forms button toolbar and then positioned within a desired workspace of a spreadsheet program. The graphic controls provided on the Forms button toolbar include a check box, an option box, a list box, a drop-down box, a combination list-edit box, a combination drop-down list-edit box, a scrollbar, and a spinner. A Format Object properties dialog box is also provided on the Forms button toolbar to enable the user to set various parameters for the graphic control. These parameters include an identification of the cell within the spreadsheet to which the graphic control is bi-directionally linked. If the user manipulates the graphic control, a value in the spreadsheet cell that is linked to the graphic control changes in a corresponding fashion. Similarly, any change to the spreadsheet cell linked to the graphic control is reflected within the graphic control. With this tool, the user can apply graphic controls to the spreadsheet program without requiring the user be skilled in programming.

Claims

1. A method for enabling a user to graphically control a cell of a spreadsheet, comprising the steps of:

(a) providing a toolbar on which is disposed a graphic representation of a control;

(b) enabling the user to select the graphic representation of the control on the toolbar and position a copy of the graphic representation of the control in a workspace selected by the user on a display, said workspace comprising one of the spreadsheet, a dialog sheet, a chart, and a macro sheet; and

(c) enabling the user to link said cell of the spreadsheet to the control without requiring the user to enter code to effect the link, linkage of said cell to the control causing a value in said cell to change as the user varies a corresponding parameter of the control by graphically manipulating the control with a user interface.

2. The method of claim 1, further comprising the step of enabling the user to size the graphic representation of the control in the workspace, by selecting a first position in the workspace as a first corner of the control, and then dragging a cursor in the display to a second position in the workspace to indicate a diagonally opposite corner of the control.

3. The method of claim 1, further comprising the step of enabling the user to select a menu item that opens a dialog box in which the user identifies said cell to which the control is linked, said link comprising a property of the control.

4. The method of claim 3, wherein the user is enabled to identify said cell to which the control is linked in the dialog box by selecting said cell in the spreadsheet.

5. The method of claim 1, further comprising the step of enabling the user to identify the cell to which the control is linked by selecting a dialog box used for entry of a formula in a toolbar, and then selecting said cell in the spreadsheet while the control is selected.

6. The method of claim 1, wherein the control comprises one of a check box, an option box, a list box, a drop-down box, a combination list-edit box, a combination drop-down list-edit box, a scrollbar, and a spinner.

7. The method of claim 1, wherein a change in the value of said cell to which the control is linked causes a corresponding change in the parameter of the control.

8. The method of claim 1, wherein the control comprises one of a list box, a drop-down box, a combination list-edit box, and a combination drop-down list-edit box, further comprising the step of enabling the user to indicate a range of cells in the spreadsheet as line entries in the control.

9. The method of claim 8, wherein selection of one of the line entries in the range of cells causes a numeric value to be returned in said cell to which the control is linked, said numeric value corresponding to an ordinal number of the line entry selected in the line entries.

10. The method of claim 1, wherein the control comprises a scrollbar, further comprising the step of enabling the user to specify a minimum value and a maximum value for a range of values encompassed by the scrollbar as the user graphically manipulates a slider in the scrollbar from one extreme position to an opposite extreme position.

11. The method of claim 10, further comprising the step of enabling the user to specify an incremental change in the value each time that the user clicks on an arrow of the scrollbar with a pointing device.

12. The method of claim 10, further comprising the step of enabling the user to specify an incremental change in the value each time that the user pages the scrollbar.

13. The method of claim 1, wherein the control comprises a spinner, further comprising the step of enabling the user to specify a start value and an end value for a range of values encompassed by the spinner as the user selects one of two arrows displayed in the spinner.

14. The method of claim 13, further comprising the step of enabling the user to specify an incremental change in the value that will occur each time that the user graphically manipulates one of the arrows.

15. The method of claim 1, wherein the control comprises an option box in a group of option boxes, the value in said cell to which the control is linked comprising an ordinal number of the option box within said group.

16. The method of claim 1, wherein the control comprises a check box, said cell to which the check box is linked having a true value if the user enters a check into the check box and a false value if the check is not entered in the check box.

17. The method of claim 1, wherein the control comprises a check box, said check box displaying a check if the user enters a non-zero number in said cell to which the check box is linked, but not displaying a check if the user enters a zero in said cell to which the check box is linked.

18. The method of claim 1, further comprising the step of enabling the user to link one cell of the spreadsheet to a plurality of controls.

19. The method of claim 18, wherein a change to the value of said one cell causes said plurality of controls to change in a corresponding manner.

20. The method of claim 1, wherein if any other cell in the spreadsheet includes a formula that depends upon the value of said cell to determine a result, said result is repetitively recalculated as the user continues to manipulate the control linked to said cell.

21. A method for controlling a value in a selected cell of a spreadsheet, comprising the steps of:

(a) inserting a control in a workspace comprising one of the spreadsheet, a chart, a dialog sheet, and a macro sheet;

(b) linking the control to the selected cell of the spreadsheet without requiring the user to enter code to effect the link; and

(c) varying a parameter of the control by graphically manipulating the control, a value in said selected cell changing in a corresponding manner as the parameter changes as a result of the linking of the control to the selected cell.

22. The method of claim 21, wherein the control comprises one of a check box, an option box, a list box, a combination list-edit box, a combination drop-down list-edit box, a scrollbar, and a spinner.

23. The method of claim 21, wherein a change in the value of the specific cell causes a corresponding change in the parameter of the control.

24. The method of claim 21, further comprising the step of repetitively recalculating all formulas that depend upon the value of the specific cell as the control parameter continues to change.

25. The method of claim 21, further comprising the step of repetitively updating any chart that depends upon the value of the specific cell in the spreadsheet.

26. The method of claim 21, further comprising the step of linking the specific cell in the spreadsheet to a plurality of controls, so that a change in the parameter of any of the plurality of controls caused by manipulating the control causes a corresponding change in the value of the specific cell and a change in the value of the specific cell causes a corresponding change in the parameters of each of the plurality of controls.

27. The method of claim 21, wherein the control comprises one of a list box, a drop-down box, a combination list-edit box, and a combination drop-down list-edit box, further comprising the step of specifying a range of cells in the spreadsheet that are user selectable entries in said one control.

28. The method of claim 21, further comprising the step of specifying incremental changes in the parameter as the control is manipulated.

29. A spreadsheet system comprising:

a personal computer having a central processing unit, a memory, a display screen, a user interface, and a non-volatile storage unit, said memory storing program instructions that control operation of the central processing unit so that it functions as a spreadsheet application, said program instructions further causing said central processing unit to;

(a) display a plurality of control icons on the display screen in order to enable a user to select a control icon with the user interface and to position a graphic control represented by the control icon in a workspace comprising one of the spreadsheet, a chart, a dialog sheet, and a macro sheet, appearing on the display screen;

(b) specify a cell in the spreadsheet to which the graphic control selected by the user is linked without requiring the user to enter code to effect the link; and

(c) vary a value of said cell as the user changes a parameter of the graphic control by manipulating it with the user interface, said value varying as the control is manipulated because said cell is linked to the graphic control.

30. The spreadsheet system of claim 29, wherein the program instructions are further operative to cause the parameter of the graphic control to change as the user changes the value in said cell of the spreadsheet that is linked to the graphic control.

31. The spreadsheet system of claim 29, wherein the program instructions further cause any other cells of the spreadsheet that depend upon the value of said cell to repetitively recalculate as the user continues to change the parameter of the graphic control.

32. The spreadsheet system of claim 29, wherein the program instructions enable the user to set a plurality of variables that define properties of the graphic control using a dialog box that is opened on the display screen, said properties being retained with the spreadsheet when the spreadsheet is stored as a file on the non-volatile storage unit.

33. The spreadsheet system of claim 29, wherein the program instructions cause any chart that depends upon the value of said cell that is linked to the graphic control to change as the user manipulates the graphic control with the user interface, to vary the parameter.

34. The spreadsheet system of claim 29, wherein the control comprise one of a check box, an option box, a list box, a drop-down box, a combination list-edit box, a combination drop-down list-edit box, a scrollbar, and a spinner.

35. The spreadsheet system of claim 29, wherein the program instructions further enable the user to assign a procedure to the graphic control that is linked to said cell, the value of said cell being updated in response to the user manipulating the graphic control, and the central processing unit then executing the procedure using said value.