Gavin wrote:Range("BD8").Value = ""
Range("BD9").Value = ""
Range("BA8").Value = "CANCEL ALL"
Range("BA9").Value = "GREEN ALL"
One small point to mention, it would always be better to clear the status cells as the last command, i.e. after filling in BA8 and BA9, in case there are previous commands left over in the command cells (such as BACK or LAY). You don't want to accidentally trigger those left over commands to repeat again when you clear the status cells (i.e. you would accidentally send another back or lay).
Kind regards,
Gavin
Thanks, Gavin, but that code has the same problem as mine - the formulae in BA8 and BA9 get overwritten. But I see canuk has a solution in his version 4 of the spreadsheet:
- Code: Select all
Range("BD8:BD33") = ""
Range("BA8") = "CANCEL ALL"
Range("BA9") = "GREEN ALL"
Application.Wait (Now + TimeValue("0:00:03"))
Range("BD8:BD33") = ""
Range("BA33").Select
Selection.Copy
Range("BA8:BA9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
In light of your earlier warning not to clear the status cells of any row other than those where the CANCEL ALL and GREEN ALL commands are being placed, I suggest it would be better to do:
- Code: Select all
Range("BD8:BD9") = ""
Range("BA8") = "CANCEL ALL"
Range("BA9") = "GREEN ALL"
Application.Wait (Now + TimeValue("0:00:03"))
Range("BD8:BD33") = ""
Range("BA33").Select
Selection.Copy
Range("BA8:BA9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
This copies the formula back into BA8 and BA9 after the commands have been issued and hopefully acted on during the Wait instruction.
I wonder if, instead of a Wait instruction, it would be possible to check on the value in BI9 (Report) being "OK"? Would this be sufficient confirmation that the CANCEL ALL and GREEN ALL commands had been completed?