programming button

[Hendri Adriaens]

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.

 

[Guest]

Create your button and paste in this macro

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

 

[Hendri Adriaens]

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.

 

[Tom Ogilvy]

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.

 

[Hendri Adriaens]

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.

 

[Tom Ogilvy]

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.

 

[Hendri Adriaens]

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.CommandButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=211.5, Top:=92.25, Width:=72, Height:=
_
24).Select
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub

 

[Tom Ogilvy]

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

 

[Hendri Adriaens]

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.CommandButton.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").CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub

 

[Hendri Adriaens]

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.