Excel 97 Visual Basic

I am trying to create a simple form using the Visual Basic Editor in Excel 97.
The idea of this form is to automate a simple Goal Seek, with an option to also create a set of Scenario’s using the Goal Seek data.
The Goal Seek is designed to forecast how much pay rise the firm can afford, with a limit that the Annual wages bill must not exceed 3.85% of the Annual Turnover.
If I run the form from within the VB Editor it works fine, but as soon as I place a Command Button on the actual worksheet to run the form I get the following Error.
Run-time error ‘1004’
Goal Seek method of range class failed

The highlighted code is:
Range("I19").GoalSeek Goal:=myNum, ChangingCell:=Range("I17")

The Command Button code is : usfGoal_Seek.Show.
Typical Data used:
Proposed Pay increase             = 3.50%
Annual Wages Bill                    = £183,419.56
Estimated Annual Turnover       = £4,603,012.99

Private Sub chkYes_Click()  'Create Scenario option
  Clear                     'Sub Routine to clear Text Boxes and Setfocus
End Sub

Private Sub cmdAdd_Click()  'New Goal Seek Button
  Clear                     'Sub Routine to clear Text Boxes and Setfocus
  cmdSeek.Visible = True    'Show Goal Seek Button
  cmdView.Visible = False   'Hide View Scenario Button
  cmdAdd.Visible = False    'Hide New Goal Seek Button
End Sub

Private Sub cmdSeek_Click()         'Goal Seek Button
If txtTitle.Text = "" Then          'Check for Text in Title Box
  Dim Msg1, Style1, Title1          'Error Message
  Msg1 = "PLEASE ENTER A TITLE !"
  Style1 = vbCritical
  Title1 = "CANNOT GOAL SEEK"
  Response = MsgBox(Msg1, Style1, Title1)
  txtTitle.SetFocus                  'Return Focus to Title Box
ElseIf txtTurnover.Text = "" Then    'Check for Turnover amount
  Dim Msg2, Style2, Title2           'Error Message
  Msg2 = "PLEASE ENTER THE TURNOVER AMOUNT !"
  Style2 = vbCritical
  Title2 = "CANNOT GOAL SEEK"
  Response = MsgBox(Msg2, Style2, Title2)
  txtTurnover.SetFocus               'Return Focus to Turnover Box
Else
  cmdAdd.Visible = True              'Show New Goal Seek Button
  cmdView.Visible = True             'Show View Scenario Button
 If chkYes.Value = True Then         'If Check Box is Checked
   myNum = txtTurnover.Text          'Assign Turnover amount to "myNum"
 
  'Range("I19").GoalSeek Goal:=myNum is Turnover amount'
  'ChangingCell:=Range("I17") is Percentage amount'
 
   Range("I19").GoalSeek Goal:=myNum, ChangingCell:=Range("I17")
   txtPercent.Text = Range("i17")    'Percentage
   txtBill.Text = Range("i18")       'Wages Bill
 
   'Add the results to the Scenarios sheet'
 
   ActiveSheet.Scenarios.Add Name:=txtTitle.Text, ChangingCells:=Range("d12,d13"), Values _
        :=Array(txtPercent.Text, txtBill.Text), Comment:="Created by K V Jones", _
        Locked:=True, Hidden:=False
   cmdView.Visible = True            'Show View Scenarios Button
  Else
   myNum = txtTurnover.Text          'Assign Turnover amount to "myNum"
   
   'Range("I19").GoalSeek Goal:=myNum is Turnover amount'
   'ChangingCell:=Range("I17") is Percentage amount'
 
   Range("I19").GoalSeek Goal:=myNum, ChangingCell:=Range("I17")
   txtPercent.Text = Range("i17")    'Percentage
   txtBill.Text = Range("i18")       'Wages Bill
  End If
End If
End Sub
Private Sub UserForm_Activate()
  Worksheets("Negotiation Scenario").Activate
  Clear                     'Sub Routine to clear Text Boxes and Setfocus
End Sub
Private Sub Clear()         'Sub Routine to clear Text Boxes and Setfocus
txtPercent.Text = ""
txtTurnover.Text = ""
txtBill.Text = ""
txtTitle.Text = ""
txtTitle.SetFocus
End Sub
webstudio1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

blakeh1Commented:
you may want to specifically tell it what sheet it is supposed to be using because maybe the activesheet is not what you might think

i.e. sheets("Sheet1").Range("I19").GoalSeek

if you step thru it line by line what line does it error out on?
0
webstudio1Author Commented:
Thanks I did try your method but no luck.
The Error line is:
Range("I19").GoalSeek Goal:=myNum, ChangingCell:=Range("I17")
0
tureCommented:
webstudio1,

Try adding this line just before the goalseek code:

ActiveCell.Activate

Any better?

Ture Magnusson
Karlstad, Sweden
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
webstudio1Author Commented:
You are pure genius.
0
tureCommented:
Thank you! *blush*

/Ture
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.