Propblem setting OnAction property at runtime

[Guest]

Hi,

I am using Excel 2000.

I create command buttons at runtime like this:For iIndx = 2 To 24 Step 4
If Cells(14, iIndx).Value <> "" Then
iBtn = iBtn + 1
Set xBtnShape = Distribution.Shapes.AddOLEObject( _
Left:=Range(Cells(1, iIndx).Address).Left, _
Top:=Range(Cells(1, iIndx).Address).Top, _
Width:=150, _
Height:=24, _
ClassType:="Forms.CommandButton.1")

With Distribution.OLEObjects(Distribution.OLEObjects.Count).Object
.Caption = "Update Distribution Class " & iBtn
End With
end if

next
<<<< END CODE <<<<<

so, this works fine, I get a new button in the right column for each column that has a value in the cell checked by the If... statement.

It then changes the caption fine.

My problem is that I cannot set the OnAction property. Using this code I want to point all my buttons at one global procedure that will check the Application.Caller value and do what is required.

Unfortunately it always gives an Application or Object defined error...

anyone had this before?

thanks

Philip

 

[Tom Ogilvy]

You are adding commandbuttons which don't support the onaction property.
Their code is placed into events. If you want to have one event that
handles all the buttons, you would need to use the technique shown by John
Walkenbach:


http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine


Application.Caller is not supported either.

If you want to add buttons form the forms toolbar which do support onaction
and application caller:

Option Base 0

Sub AddButtons()
Dim btn As Button, Varr1
Dim cell as Range
Dim i as Long
Application.ScreenUpdating = False
' to remove previously added buttons -
ActiveSheet.Buttons.Delete
varr1 = Array("Date", "Amount", "Cus Num", _
"Other1", "Other2")

i = 0
For Each Cell In Range("A5:A9")
Set btn = ActiveSheet.Buttons.Add( _
Left:=Cell.Left, _
Top:=Cell.Top, _
Width:=Cell.Width, _
Height:=Cell.Height)
btn.OnAction = "Macro1"
btn.Caption = varr1(i)
btn.Name = varr1(i)
i = i + 1
Next

Application.ScreenUpdating = True
End Sub
Sub Macro1()
MsgBox Application.Caller
End Sub

as an example.

--
Regards,
Tom Ogilvy

Hi,

I am using Excel 2000.

I create command buttons at runtime like this:
For iIndx = 2 To 24 Step 4
If Cells(14, iIndx).Value <> "" Then
iBtn = iBtn + 1
Set xBtnShape = Distribution.Shapes.AddOLEObject( _
Left:=Range(Cells(1, iIndx).Address).Left, _
Top:=Range(Cells(1, iIndx).Address).Top, _
Width:=150, _
Height:=24, _
ClassType:="Forms.CommandButton.1")

With Distribution.OLEObjects(Distribution.OLEObjects.Count).Object
.Caption = "Update Distribution Class " & iBtn
End With
end if

next
<<<< END CODE <<<<<

so, this works fine, I get a new button in the right column for each

column that has a value in the cell checked by the If... statement.

It then changes the caption fine.

My problem is that I cannot set the OnAction property. Using this code I

want to point all my buttons at one global procedure that will check the
Application.Caller value and do what is required.

 

[Guest]

Thanks Tom, the second solution is what I wanted...

cheer

Phili

----- Tom Ogilvy wrote: ----

You are adding commandbuttons which don't support the onaction property
Their code is placed into events. If you want to have one event tha
handles all the buttons, you would need to use the technique shown by Joh
Walkenbach


http://j-walk.com/ss/excel/tips/tip44.ht
Handle Multiple UserForm Buttons With One Subroutin


Application.Caller is not supported either

If you want to add buttons form the forms toolbar which do support onactio
and application caller

Option Base

Sub AddButtons(
Dim btn As Button, Varr
Dim cell as Rang
Dim i as Lon
Application.ScreenUpdating = Fals
' to remove previously added buttons
ActiveSheet.Buttons.Delet
varr1 = Array("Date", "Amount", "Cus Num",
"Other1", "Other2"

i =
For Each Cell In Range("A5:A9"
Set btn = ActiveSheet.Buttons.Add(
Left:=Cell.Left,
Top:=Cell.Top,
Width:=Cell.Width,
Height:=Cell.Height
btn.OnAction = "Macro1
btn.Caption = varr1(i
btn.Name = varr1(i
i = i +
Nex

Application.ScreenUpdating = Tru
End Su
Sub Macro1(
MsgBox Application.Calle
End Su

as an example

-
Regards
Tom Ogilv

Hi
If Cells(14, iIndx).Value <> "" The
iBtn = iBtn +
Set xBtnShape = Distribution.Shapes.AddOLEObject(
Left:=Range(Cells(1, iIndx).Address).Left,
Top:=Range(Cells(1, iIndx).Address).Top,
Width:=150,
Height:=24,
ClassType:="Forms.CommandButton.1" Distribution.OLEObjects(Distribution.OLEObjects.Count).Objec
.Caption = "Update Distribution Class " & iBt
End Wit
end i
<<<< END CODE <<<<<>> so, this works fine, I get a new button in the right column for eac

column that has a value in the cell checked by the If... statementwant to point all my buttons at one global procedure that will check th
Application.Caller value and do what is required