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?
 
tureConnect With a Mentor Commented:
webstudio1,

Try adding this line just before the goalseek code:

ActiveCell.Activate

Any better?

Ture Magnusson
Karlstad, Sweden
0
 
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
 
webstudio1Author Commented:
You are pure genius.
0
 
tureCommented:
Thank you! *blush*

/Ture
0
All Courses

From novice to tech pro — start learning today.