Archive of Mr Excel Message Board

Back to Controls in Excel archive index
Back to archive home

command button

Posted by brad on December 21, 2001 10:28 AM

I need help with a macro. How do you code a command button (that's in a cell on the worksheet) so it can adjust a certain cell to make a formula equal to zero?
Any help?!

Re: command button

Posted by Damon Ostrander on December 21, 2001 1:02 PM

Hi Brad,

This can be done using the Excel Goal Seek function. Just add this line of code to you commandbutton Click event code:

[B5].GoalSeek Goal:=0, ChangingCell:=[A5]

where B5 is the cell containing the formula, and A5 is the cell that you want adjusted until the value of B5 is zero.

Happy computing.

Damon

Re: command button

Posted by brad on December 21, 2001 1:57 PM

Damon,
Thanks. what I need with that same code is instead of only adjusting one cell by making one cell with the formula zero, I need the whole column to do it. Make sense? for example instead of only c3 with the formula but c3:c22 and then adjust not only e3 but e3:e22
Thanks

Hi Brad,

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.