Macro not firing off

[Hiran de Silva]

I have an Excel 2000 workbook that creates a set of square shapes, which are
named 'ButtonNNN' where NNN is a number eg. 013. When clicked they are
supposed to fire off a macro and pass the parameter indicated by the NNN -
ie. 13 in this example.

This works fine. But on a few PCs the macro does not get fired off; nothing
happens. Can someone tell me why?

Here is my code, and a simplified test workbook can be downloaded here.
http://www.askhiran.com/excel/expandtroubleshoot.xls

Sub CreateSquares()
For i = 1 To 5
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 1, 1, 20#,
20#).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Name = "Button" +
Excel.WorksheetFunction.Text(i, "000")
'this is an undocumented Excel VBA syntax! see Tom Ogilvy Jan
20, 2003. Thanks Tom.
'eg. Selection.OnAction = "'ExpandSummarise 10'"
strAction = "'ExpandSummarise " + Trim(Str(i)) + "'"
Selection.OnAction = strAction

'show expand buttons
strButtonName = "Button" + Excel.WorksheetFunction.Text(i,
"000")
ActiveSheet.Shapes(strButtonName).Left = 200
ActiveSheet.Shapes(strButtonName).Top = (i * 30 + 5) + 75
ActiveSheet.Shapes(strButtonName).Fill.ForeColor.SchemeColor
= 51
ActiveSheet.Shapes(strButtonName).Fill.Visible = msoTrue
ActiveSheet.Shapes(strButtonName).Line.Visible = msoFalse
Next i
Range("A1").Select
End Sub

Sub ExpandSummarise(intLineToExpand As Integer)
'Debug code
MsgBox intLineToExpand
End Sub

Thanks,
Hiran

 

[Dave Peterson]

I don't open attachments or open other files. But your code worked fine for me.

Is there a chance that the others have disabled macros when they opened your
workbook?

And this makes absolutely no difference to your problem, but you can add those
rectangles without selecting the shapes:

Option Explicit
Sub CreateSquares()
Dim myRect As Rectangle
Dim i As Long
Dim strAction As String
Dim myName As String

For i = 1 To 5
myName = "Button" & Format(i, "000")
On Error Resume Next
ActiveSheet.Rectangles(myName).Delete
On Error GoTo 0
Set myRect = ActiveSheet.Rectangles.Add(0, 0, 0, 0)
With myRect
.Name = myName
'this is an undocumented Excel VBA syntax!
'see Tom Ogilvy Jan 20, 2003. Thanks Tom.
'eg. Selection.OnAction = "'ExpandSummarise 10'"
strAction = "'ExpandSummarise " & i & "'"
.OnAction = strAction
.Left = 200
.Top = (i * 30 + 5) + 75
.Width = 20
.Height = 20
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Fill.ForeColor.SchemeColor = 51
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Line.Visible = msoFalse
End With
Next i

End Sub

Sub ExpandSummarise(intLineToExpand As Integer)
'Debug code
MsgBox intLineToExpand
End Sub

 

[Hiran de Silva]

Thanks Dave. Macros are turned on (for the two users who have this problem).
It works for everyone else. Same version/build of Excel 2000.
A further clue is these two users are on laptops, but that shouldn't make
any difference I thought.

Thanks for any further ideas.

Hiran

 

[Dave Peterson]

I don't have any guess why the macros aren't being called. Maybe the user can
rightclick on the rectangle and see what macro (if anything) is assigned to it.