Hi,

I was trying to program a button with vba (excel) and got the following
advice:

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

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


It seemed to work, namely in excel 200. Unfortunately, that doesn't work in
excel 2003. The reason is the extra security option "Trust access to vb
project". Even if enabled, it still doesn't allow code to be written, while
it does in 2000, even without changing any option.

Is there no way to just say:
CommandButton1.OnClick="..."
instead of needing to write in the editor?

It's very suprising that such a simple thing as adding a button with a
function first of all, needs such complicated coding (writing in the editor)
and then also leads to security issues...

I pasted the code that I use below.

Anyway, any help is welcome!

Thanks, regards,
-Hendri.

I pasted the code that I use below.

Sorry, it was missing. Here it is:

Private Sub Add_start_button(cl)
Dim VBEHwnd As Long
Dim StartLine As Long
Dim cmdBtn As CommandButton
On Error GoTo ErrH:
Application.VBE.MainWindow.Visible = False
VBEHwnd = FindWindow("wndclass_desked_gsk",
Application.VBE.MainWindow.Caption)
If VBEHwnd Then
LockWindowUpdate VBEHwnd
End If
Set cmdBtn =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", _
Left:=Range(cl).Left + 1, Top:=Range(cl).Top + 1, Width:=130,
Height:=24).Object
cmdBtn.Caption = "Ga naar startpagina"
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name).CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, "Sheets(""Start"").Activate"
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

and at the start of the module, we have:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal ClassName As String, ByVal WindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "user32" _
(ByVal hWndLock As Long) As Long

-Hendri.

Hendri,

I have just created a button in 2003 and assigned an event procedure to it
no problems.

Here is the code that I used

'-----------------------------------------------------------------
Sub CreateControlButton()
'-----------------------------------------------------------------
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", _
Left:=200, Top:=100, Width:=80, Height:=32)

'To set with a cell
'With Range("H2")
' Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", _
' Left:=.Left, Top:=.Top, Width:=.Width,
Height:=.Height)
'End With

With oOLE
.Object.Caption = "Run myMacro"
.Name = "myMacro"
End With

With ThisWorkbook.VBProject.VBComponents(oWs.CodeName). CodeModule
.InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _
vbTab & "If Range(""A1"").Value 0 Then " & vbCrLf & _
vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
vbTab & "End If"

End With

End Sub




--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Hendri Adriaens" wrote in message
l...
I pasted the code that I use below.

Sorry, it was missing. Here it is:

Private Sub Add_start_button(cl)
Dim VBEHwnd As Long
Dim StartLine As Long
Dim cmdBtn As CommandButton
On Error GoTo ErrH:
Application.VBE.MainWindow.Visible = False
VBEHwnd = FindWindow("wndclass_desked_gsk",
Application.VBE.MainWindow.Caption)
If VBEHwnd Then
LockWindowUpdate VBEHwnd
End If
Set cmdBtn =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", _
Left:=Range(cl).Left + 1, Top:=Range(cl).Top + 1, Width:=130,
Height:=24).Object
cmdBtn.Caption = "Ga naar startpagina"
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name).CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, "Sheets(""Start"").Activate"
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

and at the start of the module, we have:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal ClassName As String, ByVal WindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "user32" _
(ByVal hWndLock As Long) As Long

-Hendri.

Hi Bob,

I have just created a button in 2003 and assigned an event procedure to it
no problems.

Here is the code that I used

Yes, that works indeed. But unfortunately, I still need to enable "trust
access to visual basic project" to make it work. I prefer not to force the
user to enable that.

Do you think that is possible?

Thanks, best regards,
-Hendri Adriaens.

No, that is one thing that you have to do I am afraid.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Hendri Adriaens" wrote in message
l...
Hi Bob,

I have just created a button in 2003 and assigned an event procedure to
it no problems.

Here is the code that I used

Yes, that works indeed. But unfortunately, I still need to enable "trust
access to visual basic project" to make it work. I prefer not to force the
user to enable that.

Do you think that is possible?

Thanks, best regards,
-Hendri Adriaens.

No, that is one thing that you have to do I am afraid.

Unfortunately, but thanks for your answer. I will consider using a hyperlink
instead.

Best regards,
-Hendri.