Blinking Cell in Excel VBA

Question

I have VBA code to make Column F to blink

1. When I click on macro I can't see CommandButton1
2. I have an error code
3. I click OK and the macro starts running with the below error and I have to repeat that action 5 times

3. My macros aren't disable

VBA Code

When I click on command button Color 3 and I click OK and run the command button again

Color 8

Color 10

Color 5

Color 0

Answer 1

Check whether you have created two macros named StopNow. There should be only one.

Also: make sure that you haven't named the module StopNow. That would confuse VBA.

Answer 2

CommandButton1 is the name of a command button, it is not a macro. Hence the error message.

1. Select Insert > Module in the Visual Basic Editor to create a standard module.

Copy the following code into it, and change Sheet1 to the name of the worksheet for which you want to toggle the color.

Dim d As Date

Sub ChangeColor()

    Static i As Long

    i = (i + 1) Mod 5

    ThisWorkbook.Worksheets("Sheet1").Range("F2:F300").Interior.ColorIndex = _

        Choose(i + 1, 3, 8, 10, 5, 0)

    d = DateAdd("s", 3, Now)

    Application.OnTime d, "ChangeColor"

End Sub

Sub StopNow()

    On Error Resume Next

    Application.OnTime d, "ChangeColor", , False

End Sub

2. Change the code for the command button (in the worksheet module) as follows:

Private Sub CommandButton1_Click()

    ChangeColor

End Sub

3. Create a Workbook_BeforeClose event procedure in the ThisWorkbook module to stop the scheduling when the workbook is closed. Otherwise, Excel will reopen the workbook after you close it.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    StopNow

End Sub

4. You may also want to create a command button that executes StopNow, so that the user can end toggling the color.

Answer 3

Your event is CommandButton1_Click. You don't have a sub named CommandButton1. But we can fix that:

Insert the code below between the lines

Private Sub CommandButton1_Click()

and

Dim d As Date

CommandButton1

End Sub

Sub CommandButton1()

So that your code ends up looking like this:

Private Sub CommandButton1_Click()

CommandButton1

End Sub

Sub CommandButton1()

Dim d As Date

......

End Sub

Then everything should work fine

Answer 4

Hi Hans I inserted in module 3 your code and I did assign it to my CommandButton1 it works perfectly

but it does not stop. I created a command button Stop Now

When I right on Stop Button and assign "Button2_Click

It does not stop blinking

Answer 5

When I run the Stop button in Module 4. Module 3 refers to your code