Kill the Script

[BazzaBoy]

I have a button activated macro, which does a bit of Goal Seeking.

Sub Solve()

   Range("DR19").GoalSeek Goal:=Range("DR22"), ChangingCell:=Range("DQ7")
   Application.Wait (Now + TimeValue("0:00:01"))
   Run macro:="Delete_Trim"

End Sub

But I want this Macro to work, only if cell EY3 has the value of 2. If cell EY3 has the value of 1, and the user clicked the button that activated the macro, I want the script to warn the user and then die a natural death. So I amended the code to:

Sub Solve()
With Target
   If .Address = "$EY$3" And .Formula = "1" Then
   MsgBox "You are trimming in one hold only. This button is deactivated"
Exit Sub
Else:
   Range("DR19").GoalSeek Goal:=Range("DR22"), ChangingCell:=Range("DQ7")
   Application.Wait (Now + TimeValue("0:00:01"))
   Run macro:="Delete_Trim"
   End If
End With
End Sub

But the code does not want to work. I have tried a few combinations, but no joy. It is no secret that I am not a VBA person.

So can someone amend this code so that, if the value of cell EY3 is 1 and some clicked the button, he/she would get the warning (MsgBox), and after clicking 'OK' on the warning, the script would die.

Thanks in advance.

[JBeaucaire]

You can't use "target" in a regular manually activated macro, at least it's not standard to try. You need to name your objects directly.

Sub Solve()
   If Range("E3") = 1 Then
       MsgBox "You are trimming in one hold only. This button is deactivated"
       Exit Sub
   Else
       Range("DR19").GoalSeek Goal:=Range("DR22"), ChangingCell:=Range("DQ7")
   Application.Wait (Now + TimeValue("0:00:01"))
   Run macro:="Delete_Trim"
   End If
End Sub

[Leith Ross]

Hello BazzaBoy,

Unless you have declared the variable Target as Public at the top of your module, this code won't work. I am assuming this cell $EY$3 is on the ActiveSheet. If so change your code to this...

Sub Solve()

   If Range( "$EY$3").Value  = "1" Then
      MsgBox "You are trimming in one hold only. This button is deactivated"
      Exit Sub
   Else:
      Range("DR19").GoalSeek Goal:=Range("DR22"), ChangingCell:=Range("DQ7")
      Application.Wait (Now + TimeValue("0:00:01"))
      Run macro:="Delete_Trim"
  End If

End Sub

[BazzaBoy]

Thanks Leith,

It worked fine. Couln't understand what you meant by

Unless you have declared the variable Target as Public at the top of your module

but that's OK, as long as the script works (and dies with dignity).

Well, if we couldn't succeed in making that button disappear, I had to tackle it some other way.

Best Regards.