programming button again

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hendri Adriaens - 14 Feb 2007 09:29 GMT

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.

Reply to this Message

Hendri Adriaens - 14 Feb 2007 09:41 GMT

> 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.CommandButton.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.

Reply to this Message

Bob Phillips - 14 Feb 2007 10:33 GMT

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.CommandButton.1", _
Left:=200, Top:=100, Width:=80, Height:=32)

'To set with a cell
'With Range("H2")
' Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.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

Signature

---
HTH

Bob

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

>> I pasted the code that I use below.
>
[quoted text clipped - 33 lines]
>
> -Hendri.

Reply to this Message

Hendri Adriaens - 14 Feb 2007 10:52 GMT

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.

Reply to this Message

Bob Phillips - 14 Feb 2007 10:55 GMT

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

Signature

---
HTH

Bob

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

> Hi Bob,
>
[quoted text clipped - 11 lines]
> Thanks, best regards,
> -Hendri Adriaens.

Reply to this Message

Hendri Adriaens - 14 Feb 2007 11:35 GMT

> 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.

Reply to this Message