Event code to give message box and change color of auto-shape

[bh24524]

Hello, I was trying to see if it would be possible to have an event procedure that would give a message box when someone types any value in cell C34 and also change the color of one of the auto-shapes pictured below. The layout of the sheet looks like this:

What I want to do is when they type a value into C34 have a message box that says "REMINDER: After filling out info for this row, click the Archive and Reset Sheet button" and then I want the auto-shape which has a different macro assigned to change colors in a flashing manner to supplement the reminder message. (That button has a macro that clears the sheet with the exception of the last row of data which gets moved up to the first row of data to start a new sheet.) I tried experimenting with some code for this but it isn't quite doing what I want, so I was sure there has to be a better way. The code I tried was:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Archive_Reset As Shape

If ThisWorkbook.Sheets("Bump Sheet").Range("C34").Value <> "" Then
    MsgBox "REMINDER: After filling out info for this row, click the Archive and Reset Sheet button", vbOKOnly
End If
    Shapes("Archive_Reset").Fill.ForeColor.RGB = vbBlue
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive_Reset").Fill.ForeColor.RGB = vbRed
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive_Reset").Fill.ForeColor.RGB = vbBlue
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive_Reset").Fill.ForeColor.RGB = vbRed
    Application.Wait (Now + TimeValue("0:00:01"))
    Application.EnableEvents = False
End Sub

I know it probably seems like a crude approach but it wasn't something I've really ever tried. At first, I didn't have the flashing colors code lines, only the message box and things were working although I had to put the application enable events = false line because every time I was clicking somewhere else, I was getting the message pop-up box. Ideally, I only want the message to pop up one time and have the blue auto-shape named Archive_Reset flash different colors to show the user what they need to click. I had the code changing colors every second but ideally I'd kinda like it to be faster if at all possible - like maybe a half second between flashes or even a quarter second. Is there perhaps a different code that I can use to accomplish this?

 

[mumps]

Place this code in the worksheet code module. Enter a value in C34 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C34")) Is Nothing Then Exit Sub
    MsgBox "REMINDER: After filling out info for this row, click the Archive and Reset Sheet button", vbOKOnly
    Shapes("Archive and Reset Sheet").Fill.ForeColor.RGB = vbBlue
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive and Reset Sheet").Fill.ForeColor.RGB = vbRed
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive and Reset Sheet").Fill.ForeColor.RGB = vbBlue
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive and Reset Sheet").Fill.ForeColor.RGB = vbRed
    Application.Wait (Now + TimeValue("0:00:01"))
End Sub

 

[bh24524]

Place this code in the worksheet code module. Enter a value in C34 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C34")) Is Nothing Then Exit Sub
    MsgBox "REMINDER: After filling out info for this row, click the Archive and Reset Sheet button", vbOKOnly
    Shapes("Archive and Reset Sheet").Fill.ForeColor.RGB = vbBlue
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive and Reset Sheet").Fill.ForeColor.RGB = vbRed
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive and Reset Sheet").Fill.ForeColor.RGB = vbBlue
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive and Reset Sheet").Fill.ForeColor.RGB = vbRed
    Application.Wait (Now + TimeValue("0:00:01"))
End Sub

Hi Mumps, thanks for that, I think it should work. Just out of curiosity, is one second the lower limit for this or is there some way to make it under that?

 

[mumps]

You are very welcome. I believe that you can set it for less than one second. Do a little research on setting a timer for less than one second using VBA.

 

[bh24524]

I had tried looking yesterday but kept coming up short. I'll wait a little bit and see if someone might be able to add to this otherwise I'll just close it with yours as the solution.