Create a Macro button that waits!

[CPerry]

Hi community, hopefully somebody can help me out with this super simple problem I have.

I have inputted a basic button (well in actual fact there will be about 18 of these in total) that individually make an order based on numbers on another sheet. These 18 buttons are on a tab named 'Dashboard', they are to reference cells in the tab 'Bet Angel'.

Basically I'd like my first button to write the word "BACK" in cell L9 but then (this is the part I'm having trouble with) WAIT until cell O9 shows "PLACED" and then wipe both cells ready to be placed again. I have 6 "BACK" buttons, 6 "LAY" and 6 "CLOSE_TRADE" that affect the cells L9:19 and O9:O19 if the code someone comes up with works best as 3 separate rules instead of 18 in total.

Looking forward to your replies!
CPerry :)

[CPerry]

Sub Back_One ()

Cell(12, 9) = “BACK”
If Cell(15,9)=”PLACED”
Cell(12,9).clearcontents
Cell(15,9).clearcontents
Else

End Sub


The problem with this is the code does't wait, there is no "PLACED" in cell O9 so it ends, I need the code to WAIT until "PLACED' appears in cell O9 and then clear L9 and O9, any ideas how this can be achieved please?

[CPerry]

Ok I've done a little research and got this but still doesn't work (and when I go to assign it to a button it doesn't even show as an option??) I have a tab called "Dashboard" where the buttons are and the tab "Bet Angel" is where the commands need to take place, specifically in cells L9 (The word "BACK") and O9 (The word "PLACED")



Private Sub Worksheet_Change (ByVal Target As Range)
‘ BACK ONE

Sheets(“Bet Angel”).Cell(12, 9) = “BACK”

If Target.Address = “$L$9” Then
Application.EnableEvents = False

If Sheets(“Bet Angel”).Cell(15,9)=”PLACED” Then
Sheets(“Bet Angel”).Cell(12,9).ClearContents
Sheets(“Bet Angel”).Cell(15,9).ClearContents
Else

Application.EnableEvents = True

End if

End Sub


Please can anybody see why this wouldn't be working for me?

Cheers,
CPerry.

[Dave]

U can trial this. Dave

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean
If Target.Address = ActiveSheet.Range("O" & 9).Address Then
If Target.Text = "PLACED" Then
If Not Flag Then
ActiveSheet.Cells(9, 12).ClearContents
ActiveSheet.Cells(9, 15).ClearContents
Flag = True
End If
End If
Flag = True
End If
End Sub

[CPerry]

This is fantastic Dave, thank you sir! Does exactly what I want it to do at this stage.

[Dave]

It's not exactly a macro button that waits :) You are welcome. Thanks for posting your outcome. Dave