N
NA_AB
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?!
also, if we can, how to do we handle its actions like onButtonClick( ) etc?!
M
Mike H
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
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
P
Peter T
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
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
P
Peter T
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
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
N
NA_AB
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/communitie...0ef-2c6ee77837e1&lang=en&cr=US&sloc=en-us&p=1
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/communitie...0ef-2c6ee77837e1&lang=en&cr=US&sloc=en-us&p=1
N
NA_AB
how to generate the command button on the active cell?!?!
P
Peter T
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.CommandButton.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
Dim ole as Excel.OLEObject
With xlApp.ActiveCell
Set ole = xlApp.ActiveSheet.OLEObjects.Add("Forms.CommandButton.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
N
NA_AB
thanks a ton peter... thanks for your help!
Peter T said:
N
NA_AB
I'm really in need of some help, could you guide me through the way I can get
its C# equivalent code?
its C# equivalent code?
P
Peter T
I don't know C# so I'm afraid you'll have to translate the VB/A example I
gave you.
Regards,
Peter T
gave you.
Regards,
Peter T
N
NA_AB
you are right peter, and once again, thanks a lot for your efforts!Regardss,
nayan
G
got.sp4m
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
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
P
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).
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
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
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).
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
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
P
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
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
G
got.sp4m
Hi Peter,
thanks for your quick reply.
To summarize:
Yes I have the MS Forms reference, I'm heavily addicted to
intellisens
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
thanks for your quick reply.
To summarize:
Yes I have the MS Forms reference, I'm heavily addicted to
intellisens
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
G
got.sp4m
Thanks for the tip, but this was just a (self-inflicted) exercise to
see if I had gotten this stuff correctly Might come in handy if I
was to handle different objects/events for many controls etc.
best regards
Peder
see if I had gotten this stuff correctly
Might come in handy if Iwas to handle different objects/events for many controls etc.
best regards
Peder
P
Peter T
For the archives, NA_AB posted his C# translation here
http://groups.google.co.uk/group/mi...ogramming/browse_frm/thread/3af12ce98b3d2a22#
Regards,
Peter T
http://groups.google.co.uk/group/mi...ogramming/browse_frm/thread/3af12ce98b3d2a22#
Regards,
Peter T
N
NA_AB
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_ClickEventHandler(main_form_click);
void main_form_click()
{
System.Windows.Forms.MessageBox.Show("hi");
}
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_ClickEventHandler(main_form_click);
void main_form_click()
{
System.Windows.Forms.MessageBox.Show("hi");
}