Excel Button Help?

Question

I am trying to make a workbook with a number of pages all linked to a single input page.

1.) How do I set up a page or even form so that someone would just paste in the table of values and my other pages that are linked to that page populate. (I already have the linked page section down) I want to make it so somone can simply enter data and nothing else

2.) How do I set up a button that can be clicked in excel after the data has been entered in order to graph it?

i.e. 200 values put in and could be graphed by pressing a button or 500 values put in or X values put in and then a macro would graph it?

How do I make a plot that the number of values will change and have the graph still display correctly

Answer 1

For question one type:

='Worksheet Name'!A1

If the worksheet name has a space in it, then surround it with apostrophes. If it doesn't you don't need the apostrophes.

Another way of doing it is to just type = in the cell where you want the data to be put after you enter it. Then select the cell where the user will type the value in and hit enter.


For question 2, that is a little more difficult.

Go to View > Toolbars

Then make sure you have the Control Toolbox check so you can view it.

Click the command button on the tool bar and place a command button where you want one on your workbook.

Next you need to record a macro. Go to:

Tools > Macro > Record New Macro...

Click OK on the window that comes up. Then do just the steps it takes to make your graph. As soon as you are done with the graph, click the stop button and you are done recording your macro.

Now go to:

Tools > Macro > Macros...

Then select the Macro you just created and Click the edit button. Select and copy the code between the first and last lines of code. Don't select the first and last lines of code which contain the word Sub.

Next select your command button and right click it. Then from the shortcut menu select View Code. Paste the code you copied into where the cursor is.

In the Properties Window section of the Visual Basic Editor window, go to the line named Caption and change the caption to whatever text you want to display on the command button. Then click the Save button in Visual Basic Editor and close it out.

Now go back to the Control Toolbox tool bar and click the button that looks like a little drafting triangle. That will take you out of design mode for the button. Click it again if you need to go back into design mode. When the button is out of design mode, clicking the button will perform the exact same actions that you did when you recorded the macro and created the graph to a T. Depending on what you want the graph to do you may have to go back and change the code for the button to get it to do exactly what you want.

I'd help you with that, but I don't want to write a book on here. Feel free to ask a question later if you want.

Answer 2

Looking to have a single workbook that links and charts data from multiple workbooks?

The best way to do this, is to create a number of array formulas instead of simply using cell and range links, as array formulas do not require link updating. They also do not require the source workbook to be open or local.

In answer to:
1.) provide a protected workbook with cell validation

2.) graphing from an activex or vba scripted button is not necessary when using array formulas as they update automatically if recalculation is set to automatic

Visit microsoft.com for assistance with workbook protection, cell validation, and array formulas or repost to answers.yahoo.com

Answer 3

1.) ok suppose you have a cell on sheet 2 and you want it to draw the numbers input by the user on sheet 1 you would do this 'sheet 1'!A1 and it would take the value from sheet 1 cell A1.
2. I dont really know how to make a button sorry.

Answer 4

hi, u circulate to substantial menu->venture->References then pick MS Excel Library eleven.0 now come to button and write here code private Sub Command1_Click() Dim xl As New Excel.application MsgBox xl.evaluate("usual(3,3,8)") end Sub now see xl is an MS-Excel merchandise. u furnish with any excel function it is going to evaluate and supply the tip result. As usual has been presented. Now u would desire to furnish all the numbers in ur pool to the final function, as above, u would get the final.