VBA code for buttons on an Excel stock trading sheet

[lucid]

Hi,

I am wondering if anyone can help me out with this. I have been asked by a professor to create an excel sheet that allows students to trade stocks with fake money. To do this, an excel sheet is linked to Thomson One software (can do that no probs). What I need VBA code for buttons for is:

1) To open up a box that asks the student to type in a stock symbol and quantity to be purchased. Once Ok is clicked, the symbol and quantity are placed in their approrpiate cells and Thomson One is called up to provide the real-time price at which the security is bought.

2) a button the same as number 1, but sells the secuirty (or sells short).

What would be pretty sweet would be to not have the first button complete the trade, but only preview it. Then, a third button would have to be clicked to complete the trade, which would then lock in the numbers and not allow for changes (except for reversal of the trade (e.g. sell if a stock was bought)). Would this be possible?

Any help will be much appreciated!

[Simon Lloyd]

You could create a command button on your worksheet or menu bar and assign this macro [VBA]Sub CellInput()
Dim T1 As Variant
T1 = InputBox("Enter Stock Symbol And Number", "Security Selection")
Sheets("Sheet1").Range("A1") = T1
End Sub[/VBA]all this does is pop up an input box then whatever is typed in the input box is then shown in cell A1.

Really it sounds like you should create a userform with two TextBox's on it one for the symbol and one for the quantity, or use combobox's and set the Row Source to equal a list you will have created with all the symbols in and another with quantities in.

Regards,
Simon

[lucid]

Thanks Simon!

Your second para is pretty much gibberish to me... I am very, very new to this!

I have modified what you gave me a little so that each time the button is pushed, the cell that is 'filled' with the symbol moves down one. This is the code:

Sub CellInput()
Dim T1 As Variant
ActiveCell.Select
ActiveCell.Offset(1, 0).Range("A1").Select
T1 = InputBox("Enter Stock Symbol", "Security Selection")
ActiveCell = T1

End Sub


Now, that brings up the symbol so that the last, bid, and ask prices are inputted in their respective cells (from Thomosn One). Now what I need is a 'buy' button. Is there anyway to do the following for the button's code:

Once pressed, a box pops up that asks what stock to buy. Once the user inputs the cell where that symbol is located (e.g. A4) and clicks ok, another box pops up with an input for quantity. Once this is entered, the quantity is written into a cell (e.g. G4) and the bought price is entered into E4, which is whatever the ask price was at that moment in time. The ask price is located in cell D4.

Does that make any sense at all?

Would it then be possible that, once the trade is completed, that those cells are locked and are not able to change contents unless the student has a password?

Thanks again Simon

[Simon Lloyd]

Lucid, if you look at the code you have modified you should be able to adapt it to produce the Sell "Pop ups" i.e add another input box for quantity for your "Sell" code like add T2=Inputbox...etc., as for locking those cells you can record a macro for locking/unlocking cells but unless you protect the worksheet the contents won't be locked, you can also record a macro for protecting and un -protecting. Try adapting the macro's to what you need with the inputboxes then post back what you have if it doesnt work and i will point you in the right direction if i can or one of the more experienced users in this forum will.

Regards,
Simon.

P.S it is always best to post your workbook or code for a gauranteed response!, when posting your code, once pasted highlight all of your code and click the Green box with VBA written in it at the top left of your reply window, this will wrap VBA tags around your code and make it easier to read.