Flashing field in Excel

I would like to implement a strategy to highlight a particular field on an Excel form so that people will fill it in every time.

At the moment, this field contains a drop-down list which defaults as empty when the Excel file is opened.  Selecting an item from the drop-down populates the field.

I would like to do something along these lines (not necessarily all of them, it depends on how difficult they are to implement):
1. Have the first item in the list say something like "Select Item" and have it flash on and off until the item is chosen.
2. Have some text sitting next to the drop-down which flashes on and off saying to "Select Item" until an item is chosen (and then the text goes blank).
3. Force this particular drop-down to be selected before any other field on the Excel form and be edited / updated, kind of like selecting the field unlocks the spreadsheet.

Many thanks!
uworldsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
All three are possible but I will go with this

>>>Have some text sitting next to the drop-down which flashes on and off saying to "Select Item" until an item is chosen (and then the text goes blank).

It is very simple :)

See Sample Attached. I have set the timer for 1 sec.

Code In Module

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long, TimerSeconds As Single, tim As Boolean
Dim Counter As Long

Sub StartTimer()
    '~~ Set the timer.
    TimerSeconds = 1
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
    If tim = False Then
        UserForm1.Label1.BackColor = &H8000000F
        tim = True
    Else
        UserForm1.Label1.BackColor = &HFF&
        tim = False
    End If
    If UserForm1.ComboBox1.Text <> "Please Select An Item" Then
        UserForm1.Label1.BackColor = &H8000000F
        EndTimer
    End If
End Sub

Open in new window


Code In Userform

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Please Select An Item"
    For i = 1 To 10
        ComboBox1.AddItem i
    Next i
    ComboBox1.ListIndex = 0
    StartTimer
End Sub

Open in new window

ComboFlash.xls
SiddharthRoutCommented:
Oops I missed 1 thing.

Just Add 1 line after Line 32 in the module.

UserForm1.Label1.Caption= ""

Sid
Curt LindstromCommented:
Using Sid's macros you can easily implement

<3. Force this particular drop-down to be selected before any other field on the Excel form and be edited / updated, kind of like selecting the field unlocks the spreadsheet.>

See file

Curt

ComboFlash-mod1.xls
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Rory ArchibaldCommented:
One strong word of caution - if you are going to use Windows Timers, always put an error handler in the TimerProc!
uworldsAuthor Commented:
Thanks very much, is there a way to make the item flash forever without the use of a timer?
Kind regards, Mike

uworldsAuthor Commented:
P.S. Of course I mean flash until the value is selected from the drop-down.  
SiddharthRoutCommented:
Without using timer... Yes there is. :)

Place a Webbrowser control on your userform and make it to fit your animated gif image(you will have to create one). Till the time there is no value in the drop down, the animated image will show.

Let me know if you want a sample?

BTW just curious. Why do you not want to use the timer?

Sid
SiddharthRoutCommented:
Here is an example of what I was talking about.

http://www.xcelfiles.com/AnimatedGif.html

Sid
uworldsAuthor Commented:
Hi Sid,
Basically I just want it to flash until the user selects the field as it is compulsory.
Cheers,
Mike
SiddharthRoutCommented:
Mike I understand that but did you see my last 2 posts?

Sid
uworldsAuthor Commented:
Hi Sid,
Sorry, I am just trying to understand but don't think I quite know how to implement that macro coding.  Is it possible to just make a certain text field flash until a non-blank item on the drop-down is selected?
Kind regards, Mike
SiddharthRoutCommented:
Mike, If you can upload your file, then maybe I can make changes directly?

Sid
uworldsAuthor Commented:
Hi Sid,
Thank you, I have attached.  I used conditional formatting so far and defined a Name and made a List however I would like to make the choice [Select a Location] flash forever until it is selected from a valid choice.
Many thanks for your assistance!
Kind regards, Mike  
form.xls
uworldsAuthor Commented:
Hi Sid,
Please let me know if it is not possible to make the flashing work for Excel 2000.
Kind regards,
Mike
SiddharthRoutCommented:
Mike it is possible :)

Sorry, got caught up in other trivial things.

Ok, I have used the same code based on my first post. It is working just fine :)

Here is a sample.

Hope this is what you wanted?

Sid
form.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
uworldsAuthor Commented:
Thanks very much Sid.  
How do I define "Label1" to Excel so it knows which cells I am referring to?
Many thanks,
Mike
SiddharthRoutCommented:
Follow these steps.

1) Select any value from the drop down in E3. That will stop the timer.
2) Go to 'Developer' Tab and click on 'Design Mode'
3) Right Click on Label and and click on 'Properties'
4) There under the 'Caption' Field amend the text that you want :)
5) Go to 'Developer' Tab and exit from 'Design Mode'
6) Reset the value of Cell E3
7) Save the workbook and exit :)

HTH

Sid
uworldsAuthor Commented:
Thanks Sid, what/where is the Developer tab?  Many thanks, Mike
SiddharthRoutCommented:
Excel 2007

To activate or display the developer tab follow these steps:

1) Click the Microsoft Office Button.
2) Click Excel Options, The options window will open.
3) Click Popular.
4) Select the Show Developer tab in the Ribbon check box.

Excel 2010

1) Click the Office button and then choose Options
2) The Excel Options dialogue box will be displayed, click the Customize Ribbon option
3) Now check the Developer option in the right sidebar and hit OK. See snapshot below.

Sid
Developer.png
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.