writing program code in excel visual basic

[Matthew Leggett]

Hi How do I write the program code to create a button in excel visual basic, can anyone help me? Sorry if I posted this in the wrong place. Matt

[_Damian S_]

In Excel 2003, right click the menu bar and select "Control Toolbar" from the list. Click on a command button and draw one on the screen. Double click the button you drew and you will be taken to the VBA code screen. Enter code there!

-------------------------------------------------------- Knowledge is knowing that the tomato is a fruit. Wisdom is not putting it in fruit salad!!

[Matthew Leggett]

this allows me to create a button to run the script but how do I write code to create the button within a currently running program. example I am currently running a script to create a NEW worksheet from scratch and then display certain information based on data the user enters. I want the script to then create a series of button on this NEW worksheet to allow the user to then modify values that they have already entered. As a result I need to hardcode creating the button into initial script, and then add the various code to the button to allow them to update values. Hopefully this makes sense and someone can help me with my dilema

[ChandraRam]

Maybe this is what you are looking for...

With ActiveWorkbook.Sheets("SheetName").Buttons.Add(10, 10, 100, 25)
.OnAction = "FunctionName"
.Caption = "New Button"
.Name = "cmdNewButton"
End With

[Matthew Leggett]

Cheers exactly what I needed I have figured out how to create the button, and place on the screen at the desired location, and I have created the script I want the button to perform. Brief example of my layout Private Sub Month_Click() With ActiveWorkbook.Sheets(SetMonth).Buttons.Add(500, 50, 100, 25) .OnAction = "FunctionName" .Caption = "Foreign" .Name = "cmdNewButton" End With End Sub Sub FunctionName() Amount1 = Application.InputBox("Enter Amount") Worksheets(SetMonth).Range("A1").Value Amount1 End Sub The problem is it won't work, any ideas as to what I am doing wrong?

[ChandraRam]

Is this the exact code you have in your Excel macro? If yes, then you have not assigned Amount1 to Value... I mean, you are missing a "=". I tried after inserting = and it works for me :)

[Matthew Leggett]

doesn't for me :sigh: I get a message saying The macro 'Finance2.xls!FunctionName' cannot be found. when I click on the button

[ChandraRam]

I had both the button creation code and the FunctionName in a module when I tried it out - maybe that is what is needed.

[Matthew Leggett]

:) your a star, you was right.