Create your button and paste in this macro

Sub Button1_Click()
Worksheets("sheet1").Activate
End Sub

"Hendri Adriaens" wrote:

Hi,

I want to create a button in excel using vba. I found some code to create
it, and also to assign it some function, but it returns errors. The button
needs to do only one thing, namely:
Sheets("Start").Activate

All help is welcome. Thanks,
-Hendri.

Create your button and paste in this macro

Thanks, but I want my vba code to create the button.

Do you have any idea how to do that and assign the function?

Thanks,
-Hendri.

Turn on the macro recorder while you add the button manually. then turn off
the macro recorder. Look at the code recorded. You will get the code for
adding the button. Then post that code and someone can tell you how to
handle the macro to associate with it.

--
Regards,
Tom Ogilvy

"Hendri Adriaens" wrote in message
l...
Create your button and paste in this macro

Thanks, but I want my vba code to create the button.

Do you have any idea how to do that and assign the function?

Thanks,
-Hendri.

Turn on the macro recorder while you add the button manually. then turn
off the macro recorder. Look at the code recorded. You will get the code
for adding the button. Then post that code and someone can tell you how
to handle the macro to associate with it.

Yes, I know that, but that is not the whole story. I want to attach a
function to the click event. So, say that I have CommandButton1 on Sheet1,
how do we attach the function that I wrote previously?

Thank you, best regards,
-Hendri.

You have to write the event in the sheet module using code (or have it
already there)

http://www.cpearson.com/excel/vbe.htm

shows how to write code with code.

--
Regards,
Tom Ogilvy


"Hendri Adriaens" wrote in message
...
Turn on the macro recorder while you add the button manually. then turn
off the macro recorder. Look at the code recorded. You will get the
code for adding the button. Then post that code and someone can tell you
how to handle the macro to associate with it.

Yes, I know that, but that is not the whole story. I want to attach a
function to the click event. So, say that I have CommandButton1 on Sheet1,
how do we attach the function that I wrote previously?

Thank you, best regards,
-Hendri.

You have to write the event in the sheet module using code (or have it
already there)

http://www.cpearson.com/excel/vbe.htm

shows how to write code with code.

Ok, so I have now the following, but it gives error 57017. Can you spot the
error?

Thanks,
-Hendri.

Sub Macro1()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=211.5, Top:=92.25, Width:=72, Height:=
_
24).Select
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub

It shouldn't be in the Thisworkbook module, but it should be in the code
module associated with the Activesheet.

--
Regards,
Tom Ogilvy


"Hendri Adriaens" wrote in message
l...
You have to write the event in the sheet module using code (or have it
already there)

http://www.cpearson.com/excel/vbe.htm

shows how to write code with code.

Ok, so I have now the following, but it gives error 57017. Can you spot
the error?

Thanks,
-Hendri.

Sub Macro1()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=211.5, Top:=92.25, Width:=72,
Height:= _
24).Select
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub

It shouldn't be in the Thisworkbook module, but it should be in the code
module associated with the Activesheet.

Thanks, I got it working now (see below), but it also opens the vb script
editor. I don't like that as people should just be able to click the button
without seeing all the code that is behind it.

Can I surpress the code editor to open when assigning the function to the
button?

Thanks,
-Hendri.

Sub Macro1()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=211.5, Top:=92.25, Width:=72, Height:=
_
24).Select
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Blad1").Cod eModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub

Can I surpress the code editor to open when assigning the function to the
button?

Sorry, found the working answer on the page you mentioned before.

Thanks a lot for your help!

Best regards,
-Hendri.