• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1603
  • Last Modified:

Blinking or Flashing command button

Folks,
I was wondering if there was a way for an ActiveX or forms command button to blink or flash to get the users attention that "you need to click here for more information"?
0
Frank Freese
Asked:
Frank Freese
  • 11
  • 11
1 Solution
 
Martin LissRetired ProgrammerCommented:
Here's one way using a forms control.

Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub CallBlink()
Dim intTimes As Integer
Dim intColorIndex As Integer
intColorIndex = 3
    For intTimes = 1 To 10  ' This should always be an even number
        Blink intColorIndex
        Sleep 400
        If intColorIndex = 3 Then
            intColorIndex = xlAutomatic
        Else
            intColorIndex = 3
        End If
    Next
End Sub
Sub Blink(ci As Integer)

    ActiveSheet.Shapes("Button 1").TextFrame.Characters.Font.ColorIndex = ci
    Application.Calculate
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
Would I place the module in the worksheet activate event?
0
 
Martin LissRetired ProgrammerCommented:
And if you want to do it continuously until the user clicks the button (which they may find annoying)
Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public gbStop As Boolean

Sub CallBlink()
Dim intTimes As Integer
Dim intColorIndex As Integer

    intColorIndex = 3
    
    Do
        DoEvents
        If gbStop Then
            ActiveSheet.Shapes("Button 1").TextFrame.Characters.Font.ColorIndex = xlAutomatic
            gbStop = False
            Exit Do
        End If
        Blink intColorIndex
        Sleep 400
        If intColorIndex = 3 Then
            intColorIndex = xlAutomatic
        Else
            intColorIndex = 3
        End If
    Loop
End Sub
Sub Blink(ci As Integer)

    ActiveSheet.Shapes("Button 1").TextFrame.Characters.Font.ColorIndex = ci
    Application.Calculate
End Sub
Sub Button1_Click()
gbStop = True
Application.Calculate
End Sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Martin LissRetired ProgrammerCommented:
Would I place the module in the worksheet activate event?
Sure you could do that, or it could be in a code module where you could call CallBlink based on some user action.
0
 
Frank FreeseAuthor Commented:
OK...you have two sub routines in this module. I suspect the second one would go in the click event to stop the blinking. True
0
 
Martin LissRetired ProgrammerCommented:
Which post are you referring to, 40253429 or 40253458?
0
 
Frank FreeseAuthor Commented:
Before I call in the cavalry let me try to figure out why I'm getting some errors (more likely it is something that I did!).
0
 
Frank FreeseAuthor Commented:
Posts: either one - they both have two sub routines in them
0
 
Martin LissRetired ProgrammerCommented:
Actually the second one has 3 but who's counting.

CallBlink - Should be in a code module if you want to specifically call it based on some user action, or in the worksheet activate event of you want it to run when the sheet becomes active.

Blink - should always be in a code module

Button1_Click - Which could be renamed, should also be in a code module and it should be assigned to, or it's code become part of, the button's assigned macro.
0
 
Frank FreeseAuthor Commented:
Let me work it that why now.
0
 
Martin LissRetired ProgrammerCommented:
Oh and both of the publicly declared items (Sleep and gbStop) should be in a module, and logically, but not necessarily, the same module where you put Blink..
0
 
Frank FreeseAuthor Commented:
To avoid ActiveX control two subs "CallBlink" and "Blink" require a specific named control? If that's the case then it appears I will need an ActiveX command button?
The worksheet in "Statistics" (which is attached because I added more yesterday that applies to this thread) is "Quartile"
There is a command button labeled "cmdQuartileExplained" that takes the user to a specific worksheet for a detailed explanation of quartile (this is actually getting interesting).  It is this command button that I want to blink to get the users attention because it is explains how quartile is generated (there are two more identical worksheets that will have the same functionality).
I said all that to affirm that in this instance an ActiveX command button is needed, not a form control button?
Excel-Statistics.xlsm
0
 
Martin LissRetired ProgrammerCommented:
Sorry but I assumed that when you saw "Shapes" in the code that you would recognize that I'm talking about a forms control button and not an ActiveS button.

Let me try to fix up your code. But first please tell me how the blinking should happen, and when it should happen.
0
 
Frank FreeseAuthor Commented:
I did see "shapes" that's why I wanted to mention the ActiveX control.
Blinking should happen about 20 seconds after worksheet is activated and every 20 seconds thereafter if that answers your question. I don't want it this to be an irritant. Thank you - I'll be looking forward to the seeing the code
0
 
Martin LissRetired ProgrammerCommented:
Sometimes we don't communicated very well. When you said "I said all that to affirm that in this instance an ActiveX command button is needed, not a form control button?" I thought you were saying that you assumed that my code was for an ActiveX button (which it's not), but then you said "I did see "shapes" that's why I wanted to mention the ActiveX control" and I have to ask  why to that second statement.
0
 
Martin LissRetired ProgrammerCommented:
My code was set up to blink the button every .4 seconds, alternating the color from red to black. Since it seems that you want to want something different let me sate what I see as the requirement.
1.    QUARTILE sheet is activated
2.    Wait 20 seconds
3.    Change to red
4.    Wait .4 seconds
5.    Change to black
6.    Repeat 2 to 5 until sheet is deactivated

I'm going to lunch now and assuming you've agree that that's what you want or tell me something different, I'll work on it when I get back.
0
 
Frank FreeseAuthor Commented:
The second statement was simply an acknowledgement that I did notice your were thinking form controls. I first thought that your code  was ActiveX driven. The problem I have is I'm so accustomed to labeling everything, just about everything, that I implied "Command1" was ActiveX. I didn't see a property to label the form button, although I did notice in the Name Manger next to the Formula Bar that where I select the form control it did have a label that I could change to Command1.
0
 
Frank FreeseAuthor Commented:
Agreed! Enjoy lunch
0
 
Martin LissRetired ProgrammerCommented:
OK here's the updated workbook.

I did several things:
On the QUARTILE page is a second "Quartile Explained" button named "btnQuartileExplained" and assigned the btnQuartileExplained_Click macro to it.
I've added a new module named "modBlink" and in that module are several declarations that I hope I've explained fully. If you have questions please ask.
I deleted the code associated with the old button and added the macro called "btnQuartileExplained_Click" that you'll find in modBlink.
In the QUARTILE sheet at the end of Worksheet_Activate I added ScheduleBlink
At in Worksheet_Deactivate I added
On Error Resume Next[/bullet]
Application.OnTime gvarTimeToRun, "Blink", , False

Open in new window


You can do the same for the other two sheets making sure to assign the btnQuartileExplained_Click macro to the new buttons.
Q-28494655.xlsm
0
 
Frank FreeseAuthor Commented:
Martin, Awesome - I'm so impressed. Great job.
0
 
Frank FreeseAuthor Commented:
Once again - you nailed it.
After looking at the code it makes sense.
Thank you - I am impressed
0
 
Martin LissRetired ProgrammerCommented:
YW
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now