1a) A former employee created a spreasheet that takes data from a column of cells and automatically graphs them. There is no evidence of a formula to be found anywhere in the cells telling it how to scale the graphs, but it seems to know. (I should add that this not being generated by a macro written in Visual Basic to run in Excel.) Is there a way to define some graphing rules, perhaps?
1b) And, oddly, the graph appears to “float” over the spreadsheet. For example, when I select a print area to include the graph, the shaded area passes BEHIND the graph. How is this done?
How does one create a little pull-down menu of options to appear next to a cell? For example, let’s say I have a cell saying “fruit”. When I click on it, a small pulldown menu appears listing my choices. I know the choices appear in a block of cells themselves, and Excel is pulling them into this menu…but how do I do that?
Last, the spreadsheet has a “Run Program” button - a cell which appears slightly “raised” from the surface of the Excel spreadsheet. The macro (yes, this time in Visual Basic) will not execute until this “button” is clicked on. How does one set this up?
If these questions are too lenghty to answer here, please suggest (a) key terms that I can use to search these topic via the online help and/or (b) good references to get fancy with Excel. (Again, good keywords would be appreciated to help me find these topics in the index of a text.)
1a) A chart (correct term) includes settings about scaling, which can be auto or fixed. rght click on the chart & choose “chart options” or “chart settings” to see lots of things which control the chart & which are not stored in cells, nor in macros.
1b) This is normal behavior. Charts can either be on a sheet by themselves, or float on top of a sheet of cells. When you create a chart, yuo get the choice.
1c) (You didn’t ask, but…) You can learn a lot about charts by fiddling with them a bit. Grab a few columns and adozen rows of numbers, paste into a clean spread sheet, and then select Insert >> Chart from the menu (at least in Excell 2003 & before). The wizard will give you a bit of a ghuided tour of many of the features available.
Put the choices in sucessive cells someplace out of the way in the spreadsheet you want to have the dropdowns or better yet, on another sheet in the same workbook.
Then select the cell you want to be the dropdown & choose Data >> Validation from the menu. Set the allow option to list & select the range of choices.
You can just type “Dropdown” into the Excel help & there is a pretty clear entry named “Create a drop-down list from a range of cells” (again in 2003).
3) Last, the spreadsheet has a “Run Program” button - a cell which appears slightly “raised” from the surface of the Excel spreadsheet. The macro (yes, this time in Visual Basic) will not execute until this “button” is clicked on. How does one set this up?
On a blank area of your menu bar, right click. This will bring up a list of toolbars. Click on Control Toolbox to show that toolbar. Click on the leftmost button, that looks a pencil with a draftman’s triangle. This will turn on Design mode. Double click on the Run Program button and it will take you to the VBA code that runs when it is pressed.
Alternatively you can press ALT-F11 to bring up the VBA development environment (this is in Excel 2003, not sure how 2007 behaves).
However, it will take a fair amount of study to learn how to code in VBA. Lots of resources out there, though. My favorite is www.ozgrid.com.
ETA: VBA is Visual Basic for Applications, a MS language. It is a flavor of VB specifically to run inside MS applications including Word, Outlook, Excel, Project, and PowerPoint. You cannot create standalone programs with it, you can only run it within one of those applications. The syntax is fairly straightforward but you have to learn the object model for each application.
ETA2: To create a button from scratch, go to the toolbar you just revealed and click on the little picture of a button. Then click and drag on your worksheet to create a button. Double click on it to take you to the code for it, which will start out as a blank Sub.
View >> ToolBars >> Control Toolbox. This will give you a small floating menu with all sorts of gizmos, including buttons. Click the button icon on the toolbar to select button-drawing mode then mouse a a rectangle on the grid to create a button there. Like charts, they float above the grid. Double-click the completed button to create the VBA code which you want to execute when the user clicks it.
Type “button” into help & read the entry “Add an ActiveX control” for more details.
Excel has some automatic features that adjust the scale to a size that is usually relevant to the data at hand. You can override the automatic scales. to become familiar with the choices try placing the mouse cursor over the graph and then right-click the mouse button and select “format plot area”.
1b. Do you want to print the graph? I suggest the “right-click” method again and select “move graph”. You can move it to its own page and then print it from there. There are a number of ways that your programmer may have set it up to “not print” on that page but moving it to it’s own page will definately give you a print option.
That little pull down menu is a form. Forms can be tricky as some Excel versions support two kinds of forms “form controls” and ActiveX Controls. These controls can manipulate data. If it’s a form control you can usually right-click and then choose format to see a menu where you can view and change the location of the “list” and the cell that holds the “choice”(btw right-click is a great way to explore in excel. It usually takes you to the second layer of excel use and you appear to be interested in going there. sometimes you have to right-click twice in order to first select the object and then enact its menu)
The button is either a form which has the ability to assign a macro (right click again) or its ActiveX which has a somewhat complicated method of calling a macro . ActiveX forms rely on procedures attached to the page that you can only see in Excels visual basic editor.
Google the name “John Walkenbach” and get thee to his website. If you want to start dabbling in VBA (Excel’s built in visual basic engine) I suggest John’s dummies book - Excel VBA Programming for Dummies
. He also has larger reference books which are targeted for the different versions of Excel. I work with Excel 2007 so I have his Excel 2007 - Power Programming with VBA and Excel 2007 Bible.
I have other Excel books but JW’s seem to be the easiest to follow.
I may have caused more trouble than help[ since some of my suggestions were specific to Excel 2007. For Excel 2003 -
1 - options pop up when you right click over the graph. you don’t have to choose format
2 - your buttons and drop down list are probably ActiveX and you can see the choice list (ListFillRange) and target cell (LinkedCell) in the list box by right click and then choosing “properties”
Some Active forms can be used without macros via adjusting the properties parameters.