Consulting

Results 1 to 6 of 6

Thread: Create a Macro button that waits!

  1. #1
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    122
    Location

    Create a Macro button that waits!

    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

  2. #2
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    122
    Location
    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?

  3. #3
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    122
    Location
    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.

  4. #4
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    598
    Location
    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

  5. #5
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    122
    Location
    This is fantastic Dave, thank you sir! Does exactly what I want it to do at this stage.

  6. #6
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    598
    Location
    It's not exactly a macro button that waits You are welcome. Thanks for posting your outcome. Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •