How to start Code via Cell Value

[utuber]

Hello

I have found a code that I would like to alter to monitor cell $FP$1.

Cell $FP$1 formula is =IF(TEXT(FQ1,"ddd")=BF5,1,0)

Cell $FP$1 formula change reads 1 - result - should run StartFlashing
Cell $FP$1 formula change reads 0 - result - should runStopFlashing

Here is the existing code to be altered.

Option Explicit

Public NextFlash As Double
Public Const FR As String = "Sheet1!A1:C1"
Sub StartFlashing()
If Range(FR).Interior.ColorIndex = 3 Then
Range(FR).Interior.ColorIndex = xlColorIndexNone
Else
Range(FR).Interior.ColorIndex = 3
End If
NextFlash = Now + TimeSerial(0, 0, 1)
Application.OnTime NextFlash, "StartFlashing", , True
End Sub
Sub StopFlashing()
Range(FR).Interior.ColorIndex = xlColorIndexNone
Application.OnTime NextFlash, "StartFlashing", , False
End Sub

Private Sub Workbook_Close()
StopFlashing
End Sub
Private Sub Workbook_Open()
StartFlashing
End Sub

Many thanks
Peter

 

[craig.penny]

Put this is the code module behind the worksheet.

Sub Worksheet_Change(ByVal Target As Range)
      Dim Rng As Range
      Set Rng = Range("FP1")
      
      Static Flag As Boolean
      Static Track As Integer
      
      If Flag Then
            If Track <> Rng.Value Then
                  If Rng.Value = 0 Then Call StopFlashing
                  If Rng.Value = 1 Then Call StartFlashing
                  Track = Rng.Value
            End If
      Else
            Flag = True
            Track = Rng.Value
      End If
     
End Sub

Let me know if it works.

 

[utuber]

Thanks Craig, I inserted your code but the StartFlashing/StopFlashing did not work. I was playing around with the worksheet and somhow invoked a run-time error '1004' method 'Ontime' of object _Application' failed.

After that the code worked. So I saved and re-opened w'sheet but the StartFlashing/StopFlashing never worked again.

I found this earlier
http://support.microsoft.com/kb/142154

Maybe I have to run a macro to start the proceedure when the sheet is opened? Being a beginner I have of course no idea...

I also failed to mention I am using excel 2003.

Thanks
Peter

 

[craig.penny]

Judging by the

[FONT=Tahoma]Private Sub Workbook_Close()
[/FONT]

and

Private Sub Workbook_Open()

All of your code is in the ThisWorkbook module. Am I right about that?

Did you put the code I gave you in the worksheet module?

As far as I can remember, there's nothing in what I gave you that would cause a problem with the 2003 version. BTW, it's always a good idea to mention what version you're working with on these boards. I went back and forth about 20 times with another member trying to pin down why something wouldn't work for him when it worked perfectly for me until we realized he was in 2003 and I was using 2007. D'oh!!!

 

[utuber]

Judging by the

[FONT=Tahoma]Private Sub Workbook_Close()
[/FONT]

and

Private Sub Workbook_Open()

All of your code is in the ThisWorkbook module. Am I right about that?

Did you put the code I gave you in the worksheet module?

Hi Craig, I opened the VBA editor then added a module and pasted your code there. The modules are titled
Cell flashing code.xls Module3 (Code) [the flashing code]
Cell flashing code.xls Module4 (Code) [your code]

There are no other modules there.

I got the flashing code from here
http://www.pcmag.com/article2/0,2817,2023987,00.asp

I can run the flashing code from two icons on the w'sheet assigned to startflashing and stopflashing but I can't run them from the cell change value at $FP$1

Thanks

 

[craig.penny]

When you are in the editor screen locate the icon for the ThisWorkbook module. Right above that you'll see icons for the worksheets in your workbook. Double click on the one that's the same name as the worksheet that has $FP$1 on it that you're trying to monitor. When that code window opens, that's where my code needs to go. It's an event triggered code that only applies to a worksheet. If you have it in the workbook module or a standard module it won't do anything.