Setting .OnAction of a command button terminates my macro.....

[thfc1882]

I am adding buttons to a spreadsheet programmatically and stepping
through the macro, it gets to the line

ActiveSheet.Buttons("Survey").OnAction = "SCI"

and the macro ends. The OnAction has been set but the macro will not
carry on after that. No error message, nothing.

I need to set the onactions of many buttons but cannot get past the
first one.

Anyone got any ideas?

Using Excel 2002.

Many thanks,
Ian

 

[Norman Jones]

Hi Ian,

I see nothing contentious in the code snippet.

Post the following code which does not run.

 

[ruff]

Hi Norman,

Here is the full code:

Sub Splash_Screen()

Sheets.Add before:=Sheets(1)
Sheets(1).Name = "Navigation"
Cells.Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

ActiveSheet.Buttons.Add(50, 50, 100, 50).Name = "Survey"
ActiveSheet.Buttons("Survey").Caption = "Survey"
ActiveSheet.Buttons("Survey").OnAction = "SCI"

ActiveSheet.Buttons.Add(200, 50, 100, 50).Name = "Prices"
ActiveSheet.Buttons("Survey").Caption = "Prices"
ActiveSheet.Buttons("Survey").OnAction = "Prices"

Range("A1").Select

End Sub

 

[Norman Jones]

Hi Ruff,

Your code ran for me.

You should, however, change:

ActiveSheet.Buttons("Survey").Caption = "Prices"
ActiveSheet.Buttons("Survey").OnAction = "Prices"

to:

ActiveSheet.Buttons("Prices").Caption = "Prices"
ActiveSheet.Buttons("Prices").OnAction = "Prices"

Subject to this change, your macro achieved, for me, all that you asked of
it.

 

[ruff]

Thanks Norman. I restarted Excel and it appears to work now. Very
strange!