Macro Does not Work on Button

[alanglloyd]

I have recorded a GoalSeek macro in Excel 97 SR2, which works when
called from the Tools | Macro menu, but when I call it from a button
on the spreadsheet then the macro fails in the GoalSeek command.

My macro is . . .

Sub BalancePC()
'
' BalancePC Macro
' Macro recorded 24/03/2009 by Alan G Lloyd
'

'
Worksheets("Financial Statement").Range("M37").GoalSeek _
Goal:=0, _
ChangingCell:=Worksheets("Financial Statement").Range("I25")

End Sub

Alan Lloyd

 

[Jacob Skaria]

It is difficult to comment without reviewing. Could you please right click
the button 'Assign Macro' and reassign the macro.

If this post helps click Yes

 

[ryguy7272]

Maybe this will help:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

Also, you may need this:
http://peltiertech.com/Excel/SolverVBA.html#Solver3


Good luck,
Ryan---

 

[alanglloyd]

RyGuy7272

I've looked at your referenced links but they re-interate whate I've
done in generating my macro.

Jacob

Couldn't see your post directly (don't know why) but only as
referenced by RyGuy7272

Couldn't find 'Assign Macro' anywhere in my Excel or VBA, but right-
clicked on button in Design Mode & selected View Code, deleted the
event code, saved & re-opened and re-entered the macro in the View
Code button event. No change

Macro runs if run in Excel | Tools | Macro or in VBA | Tools |
Macros.

When run from button click it fails with Error 1004, which does not
appear in the list of trappable errors. If Debug selected on fail then
yellow margin arrow is against bottom line of GoalSeek code - does
this mean the error is in that line, or only in the complete code
line.

I've tried more fully qualifying the ranges with Sheet1 instead of
Worksheet("Financial Statement"), but no difference.

Cell function trail is ...

I25 - constant
I27 = Sum(I4:I26)
C28 = C27 - I27
M36 = C28 + C36
C36 = SUM(C32:C35)
C32 - constant
M27 = I32 - M36
I32 - constant

I just cannot understand why the macro function code works fine as a
macro call, but the same code fails when called from a button event
calling that macro function.

Alan Lloyd

 

[ryguy7272]

Yeah, sometimes I have trouble seeing posts from other posters too. Do you
use Mozilla/Firefox? Sometimes new posts don't display in that browser (no
idea why). Try MS Explorer. As I said before.....

Maybe this will help:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

Also, you may need this:
http://peltiertech.com/Excel/SolverVBA.html#Solver3


Good luck,
Ryan---