assign button to macro to only run once

[billfinn]

I have assigned a macro to a button that will operate once. It is creating an estimate number using date, customer last name and a random number. Once it has run, subsequent presses of the button do generate the Already Run message which is what I was trying to do. I need to be able to run it once for every time the Buyer Last Name (Cell F11) changes, in other words when someone begins a new estimate the button (and macro) should operate once. Not sure how to pull that one off. Any thoughts would be appreciated.


I have the macro button assigned to the following macro

Option Explicit
Sub capture_cell_value()
'Copy Estimate number once from hidden and protected cell


  Static HasRun As Boolean


  If HasRun = True Then Exit Sub  'If  the macro has run exit
  MsgBox "Already run!", vbExclamation   'message to screen 


Range("F10").Copy


Range("F14").Select


Range("F14").NumberFormat = "General"


Range("F14").Font.Bold = True


Range("F14").Font.Color = vbRed


End Sub

This works well but if the last name is changed I'd like it to be able to run again.
I tried adding this line just below Option Explicit thinking if the cell was changed it would reset and got an error stating that the variable Target.Address is not defined.

If Target.Address = "$F$11" Then HasRun = False




Thanks much
Bill

 

[Roderick_E]

Store the last name of the previous run then check that cell to see if different than current.

 

[Fluff]

Is that all of your code?
because there is nothing to stop the macro running multiple times & you will always get the message box, even on the first run.

 

[billfinn]

Fluff
That is all my code. I want the macro to execute only if cell F10 has changed. I am creating the estimate number elsewhere and dumping it in this cell because the random number generator changes output every time something gets typed, deleted or whatever.
The macro operates from a button on the worksheet
I do realize that it the macro will run multiple times, as long as it sends the message and doesn't update F14 unless F10 has changed that is fine. I don't want the estimate number to change if someone fat fingers a cell value once it's been assigned and logged.
Likewise I know that it will always give the message if F10 has not changed, and that is also fine. What I am unable to do is have the macro operate again when the value of cell F10 changes. If I can get that to operate then I will have what I need.
I'm a out of practice and a long way from an expert and am trying to figure things out before requesting help. I've spent a couple of hours on this one including research on line and in the Excel help.
Thanks much for your help
Bill

 

[billfinn]

Roderick,
I did try that and the name is the same
thanks,
Bill

 

[Fluff]

The only thing your macro is doing is formatting F14
It is not changing anything.

 

[mikerickson]

You could use something like

Sub mySub()
    static BuyerName as String

   If BuyerName = Range("F11").Text Then
        MsgBox "already run"
        Exit Sub
   End If

' code

    BuyerName = Range("F11").Text
End Sub

 

[billfinn]

Mike,
Thanks very much, that took care of my issue. I very much appreciate your assistance.
thanks,
Bill