hey guys, just wanted to know if we can generate a button on an excel cell!
also, if we can, how to do we handle its actions like onButtonClick( ) etc?!

Hi,

You can try this to get you going maybe.

View|Toolbars - Forms

On the toolbar click the button Icon and and the worksheet point-left click
and drag to get a button the size you want.

In the popup click 'NEW' and enter the following code

msgbox "Hello World"

Close VB editor and return to your sheet and click the button. All you need
to do now is add some meaningful code to that button.

Mike


"NA_AB" wrote:

hey guys, just wanted to know if we can generate a button on an excel cell!
also, if we can, how to do we handle its actions like onButtonClick( ) etc?!

Use the Controls Toolbar Button to draw a command button where you'd
like.

Right-click on the button and View Code to see where the macro code
should go. It can do anything you'd like.

Or, use the Forms Toolbar Button to draw a Forms Button, and
right-click on it to assign a macro you've already created earlier.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46945

Hi,
On developer tab, you have an option "Insert Control" option, and there you
can find "Form Controls"
You can add buttons and other control buttons from there. As soon as you
create a button, it will prompt to select the macro.

NA_AB wrote:
hey guys, just wanted to know if we can generate a button on an excel cell!
also, if we can, how to do we handle its actions like onButtonClick( ) etc?!

From your adjacent post I guess you mean you want a button on a sheet to
call code in your comaddin. You'd do that pretty much the same way as with
an Office.CommandBarButton. Ensure your addin has a reference to MSForms
object library and use WithEvents btn as MSForms.CommandButton.

Set c.btn = xlApp.ActiveWorkbook.Activesheet.OLEObjects(<name or
index).Object

See VBA help how to add an ActiveX button to a worksheet.

Unlike the CommandBarButton which only exposes a click event, the
CommandButton exposes a range of events as well as click.

Regards,
Peter T

"NA_AB" wrote in message
...
hey guys, just wanted to know if we can generate a button on an excel
cell!
also, if we can, how to do we handle its actions like onButtonClick( )
etc?!

Ensure your addin has a reference to MSForms object library

Should have said "Microsoft Forms 2.0" (FM20.dll)

Also, perhaps I should have clarified I was talking about a button from the
'Control toolbox' toolbar (not the Forms toolbar)

Peter T

"Peter T" <peter_t@discussions wrote in message
...
From your adjacent post I guess you mean you want a button on a sheet to
call code in your comaddin. You'd do that pretty much the same way as with
an Office.CommandBarButton. Ensure your addin has a reference to MSForms
object library and use WithEvents btn as MSForms.CommandButton.

Set c.btn = xlApp.ActiveWorkbook.Activesheet.OLEObjects(<name or
index).Object

See VBA help how to add an ActiveX button to a worksheet.

Unlike the CommandBarButton which only exposes a click event, the
CommandButton exposes a range of events as well as click.

Regards,
Peter T

"NA_AB" wrote in message
...
hey guys, just wanted to know if we can generate a button on an excel
cell!
also, if we can, how to do we handle its actions like onButtonClick( )
etc?!

hey Mike, JBeaucaire, aamerrasheed, thank you guys but my exact dealing is
with the excel addin as peter has rightly stated! thanks Peter :)

n btw, peter, could you look into this once please...
http://www.microsoft.com/communities...sloc=en-us&p=1

how to generate the command button on the active cell?!?!

I don't know the C# syntax but in VB/A -

Dim ole as Excel.OLEObject
With xlApp.ActiveCell
Set ole = xlApp.ActiveSheet.OLEObjects.Add("Forms.CommandBut ton.1", _
Left:=.Left, Top:=.Top, Width:=115#, Height:=24#)
End With
ole.Visible = True

' trap the button's events in a WithEvents class
Set c = New clsBtnEvents
Set c.btn = ole.Object

Regards,
Peter T


"NA_AB" wrote in message
...

how to generate the command button on the active cell?!?!

thanks a ton peter... thanks for your help!

"Peter T" wrote:

I don't know the C# syntax but in VB/A -

Dim ole as Excel.OLEObject
With xlApp.ActiveCell
Set ole = xlApp.ActiveSheet.OLEObjects.Add("Forms.CommandBut ton.1", _
Left:=.Left, Top:=.Top, Width:=115#, Height:=24#)
End With
ole.Visible = True

' trap the button's events in a WithEvents class
Set c = New clsBtnEvents
Set c.btn = ole.Object

Regards,
Peter T


"NA_AB" wrote in message
...

how to generate the command button on the active cell?!?!

I'm really in need of some help, could you guide me through the way I can get
its C# equivalent code?

I don't know C# so I'm afraid you'll have to translate the VB/A example I
gave you.

Regards,
Peter T

"NA_AB" wrote in message
...
I'm really in need of some help, could you guide me through the way I can
get
its C# equivalent code?

:) you are right peter, and once again, thanks a lot for your efforts!

Regardss,
nayan

"Peter T" wrote:

I don't know C# so I'm afraid you'll have to translate the VB/A example I
gave you.

Regards,
Peter T

"NA_AB" wrote in message
...
I'm really in need of some help, could you guide me through the way I can
get
its C# equivalent code?

Peter T,
I'm having some problems with your example code;
With this in a class module called "clsBtnEvents":

Option Explicit
Private WithEvents mbtn As MSForms.CommandButton

Friend Property Set Button(ByRef btn As MSForms.CommandButton)
Set mbtn = btn
End Property
Private Sub mbtn_Click()
MsgBox "Button with caption """ & mbtn.Caption & """ was clicked."
End Sub


An this in a normal code module:

Option Explicit
Dim objBtnEventHandlers() As New clsBtnEvents

Sub InsertButtonInActiveCell()
Static slngBtnNum As Long
Dim ole As Excel.OLEObject
Dim btn As MSForms.CommandButton
Dim appXl As Excel.Application: Set appXl =
ThisWorkbook.Application

If Not ActiveCell Is Nothing _
And TypeOf ActiveSheet Is Worksheet Then
With appXl.ActiveCell
Set ole = appXl.ActiveSheet.OLEObjects.Add( _
"Forms.CommandButton.1", _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
End With
With ole
slngBtnNum = slngBtnNum + 1
.Object.Caption = "Button " & CStr(slngBtnNum)
ReDim Preserve objBtnEventHandlers(1 To slngBtnNum)
Set objBtnEventHandlers(slngBtnNum).Button = .Object
End With
End If
End Sub

I can't seem to trap the click event, also the static variable
slngBtnNum doesn't increase resulting all buttons having the name
"Button 1".. I've tried to figure out whats the problem, but can't
seem to find it.. Do you see any obvious mistakes?

I've also found that I cannot set breakpoints below the line that adds
the button to the sheet, I get the message "Can't enter break mode at
this time".

best regards
Peder Schmedling

On Jan 7, 11:51*am, "Peter T" <peter_t@discussions wrote:
Dim ole as Excel.OLEObject
With xlApp.ActiveCell
Set ole = xlApp.ActiveSheet.OLEObjects.Add("Forms.CommandBut ton.1", _
* * * * Left:=.Left, Top:=.Top, Width:=115#, Height:=24#)
End With
ole.Visible = True

' trap the button's events in a WithEvents class
Set c = New clsBtnEvents
Set c.btn = ole.Object

Regards,
Peter T

Peter, a few comments -

Do you have the MS Forms 2.0 reference I mentioned earlier in this thread.
In VBA, easiest way is to (temporarily) add a Userform. After typing "mbtn
As MSForms." you should start seeing the intellisense after the dot and when
done the prefilled events in the drop down combo (first select mbtn in the
left combo).

If your code is in VBA and you are not automating another instance of Excel,
you don't need the xlApp stuff (though no harm that way).

If Not ActiveCell Is Nothing _
And TypeOf ActiveSheet Is Worksheet Then

Good stuff and looks like you are trying to be careful in case of chart
sheets or a chartobject.chart selected. But if say the activesheet is not a
worksheet the line might error with Activecell. It's a bit inconsistent
though, if you do need to use xlApp (eg automation), both ActiveCell &
ActiveSheet should be qualified with xlApp. Also if the code is not in Excel
use "Excel.Worksheet".

Not sure why your static counter is not incrementing, but probably better to
do something like this

Dim ole As OLEObject
For Each ole In ActiveSheet.OLEObjects
If InStr(ole.progID, "CommandButton") Then
counter = counter + 1
End If
Next
counter = counter + 1

It's normal not to be able to break into code after having added an ActiveX
control, don't try and don't worry about it.

Finally, best not to programmatically add worksheet controls to the same
workbook that contains the code that's adding the controls (risk of crashing
Excel as code recompiles, maybe that's why your static counter loses scope).
It's a good idea to trap the close event of the wb so you can destroy the
class objects.

Regards,
Peter T


wrote in message
...
Peter T,
I'm having some problems with your example code;
With this in a class module called "clsBtnEvents":

Option Explicit
Private WithEvents mbtn As MSForms.CommandButton

Friend Property Set Button(ByRef btn As MSForms.CommandButton)
Set mbtn = btn
End Property
Private Sub mbtn_Click()
MsgBox "Button with caption """ & mbtn.Caption & """ was clicked."
End Sub


An this in a normal code module:

Option Explicit
Dim objBtnEventHandlers() As New clsBtnEvents

Sub InsertButtonInActiveCell()
Static slngBtnNum As Long
Dim ole As Excel.OLEObject
Dim btn As MSForms.CommandButton
Dim appXl As Excel.Application: Set appXl =
ThisWorkbook.Application

If Not ActiveCell Is Nothing _
And TypeOf ActiveSheet Is Worksheet Then
With appXl.ActiveCell
Set ole = appXl.ActiveSheet.OLEObjects.Add( _
"Forms.CommandButton.1", _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
End With
With ole
slngBtnNum = slngBtnNum + 1
.Object.Caption = "Button " & CStr(slngBtnNum)
ReDim Preserve objBtnEventHandlers(1 To slngBtnNum)
Set objBtnEventHandlers(slngBtnNum).Button = .Object
End With
End If
End Sub

I can't seem to trap the click event, also the static variable
slngBtnNum doesn't increase resulting all buttons having the name
"Button 1".. I've tried to figure out whats the problem, but can't
seem to find it.. Do you see any obvious mistakes?

I've also found that I cannot set breakpoints below the line that adds
the button to the sheet, I get the message "Can't enter break mode at
this time".

best regards
Peder Schmedling

On Jan 7, 11:51 am, "Peter T" <peter_t@discussions wrote:
Dim ole as Excel.OLEObject
With xlApp.ActiveCell
Set ole = xlApp.ActiveSheet.OLEObjects.Add("Forms.CommandBut ton.1", _
Left:=.Left, Top:=.Top, Width:=115#, Height:=24#)
End With
ole.Visible = True

' trap the button's events in a WithEvents class
Set c = New clsBtnEvents
Set c.btn = ole.Object

Regards,
Peter T

Another thought - if you are only after a button-click event and you are
using VBA - might be simpler to add a button form the Forms toolbar and
assign its onAction property to a macro in your wb. In the macro, to
identify which button -
sButtonName = Application.Caller

Pros & Cons with both approaches.

Regards,
Peter T

Hi Peter,
thanks for your quick reply.

To summarize:
Yes I have the MS Forms reference, I'm heavily addicted to
intellisens :-P
I knew about the appXl/automation stuff, but thanks for pointing it
out.
Great tip about ActiveCell/ActiveSheet, I was trying to be careful but
I can now see that the code has flaws.

The final note you made, about not adding controls to the workbook
containing the "adding code" did the trick. When I modified the code
to create a new workbook it worked flawlessly.

As a side not I can tell you that I did not end up counting the number
of buttons in the sheet, I was worried that this would cause problems
with the RedDim statement.. Instead I implemented a check of bounds to
the event handler array.

Again thanks for your help,
Peder Schmedling

On Jan 9, 10:30*am, "Peter T" <peter_t@discussions wrote:
Peter, a few comments -

Do you have the MS Forms 2.0 reference I mentioned earlier in this thread..
In VBA, easiest way is to (temporarily) add a Userform. After typing "mbtn
As MSForms." you should start seeing the intellisense after the dot and when
done the prefilled events in the drop down combo (first select mbtn in the
left combo).

If your code is in VBA and you are not automating another instance of Excel,
you don't need the xlApp stuff (though no harm that way).

* *If Not ActiveCell Is Nothing _
* * * *And TypeOf ActiveSheet Is Worksheet Then

Good stuff and looks like you are trying to be careful in case of chart
sheets or a chartobject.chart selected. But if say the activesheet is not a
worksheet the line might error with Activecell. It's a bit inconsistent
though, if you do need to use xlApp (eg automation), both ActiveCell &
ActiveSheet should be qualified with xlApp. Also if the code is not in Excel
use "Excel.Worksheet".

Not sure why your static counter is not incrementing, but probably better to
do something like this

Dim ole As OLEObject
For Each ole In ActiveSheet.OLEObjects
If InStr(ole.progID, "CommandButton") Then
counter = counter + 1
End If
Next
counter = counter + 1

It's normal not to be able to break into code after having added an ActiveX
control, don't try and don't worry about it.

Finally, best not to programmatically add worksheet controls to the same
workbook that contains the code that's adding the controls (risk of crashing
Excel as code recompiles, maybe that's why your static counter loses scope).
It's a good idea to trap the close event of the wb so you can destroy the
class objects.

Regards,
Peter T

Thanks for the tip, but this was just a (self-inflicted) exercise to
see if I had gotten this stuff correctly :-P Might come in handy if I
was to handle different objects/events for many controls etc.

best regards
Peder

On Jan 9, 10:48*am, "Peter T" <peter_t@discussions wrote:
Another thought - if you are only after a button-click event and you are
using VBA - might be simpler to add a button form the Forms toolbar and
assign its onAction property to a macro in your wb. In the macro, to
identify which button -
sButtonName = Application.Caller

Pros & Cons with both approaches.

Regards,
Peter T

For the archives, NA_AB posted his C# translation here

http://groups.google.co.uk/group/mic...12ce98b3d2a22#

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
I don't know C# so I'm afraid you'll have to translate the VB/A example I
gave you.

Regards,
Peter T

"NA_AB" wrote in message
...
I'm really in need of some help, could you guide me through the way I can
get
its C# equivalent code?

atlast, figured out a way to handle the click etc. events:(C#)


Range r = Connect.myApplication.ActiveCell;
Worksheet sht = (Worksheet)Connect.myApplication.ActiveSheet;
Shape btn = sht.Shapes.AddOLEObject("Forms.CommandButton.1",
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value,
sht.get_Range(r, r).Left,
sht.get_Range(r, r).Top, 60,20);//sht.get_Range(r,r).Width, sht.get_Range(r,
r).Height);
OLEObject obj1 = (OLEObject)(sht.OLEObjects("CommandButton1"));
MSForms.CommandButton mbtn = (MSForms.CommandButton)(obj1.Object);
mbtn.Caption = "click me";
mbtn.Click += new
Microsoft.Vbe.Interop.Forms.CommandButtonEvents_Cl ickEventHandler(main_form_click);




void main_form_click()
{
System.Windows.Forms.MessageBox.Show("hi");
}