E
Esra Dekan
OK, i know its kind of frowned on, but I need to have a cell that
flashes text.
How do i do that in Office 2007?
Any help appreciated
TIA
Esra
flashes text.
How do i do that in Office 2007?
Any help appreciated
TIA
Esra
H
Héctor Miguel
hi, Esra !
see: http://www.cpearson.com/excel/BlinkingText.aspx
or... there is a wise-tricky procedure (posted by KL in spanish ng sometime ago)...
(and with minor changes based on Thomas Jansen -1999- post)...
that let's you to preserve the undo-levels, and you might want to give it a try...
if any doudbts (or further information)... would you please comment ?
hth,
hector.
assuming 'a cell' es [B1] and you need it to 'flash' if it's value is greater than 5
1) select [B1]... -> [menu] format / conditional format...
formula: -> =(b1>5)*(mod(second(now()),2)=0)
format: -> apply formats as needed/wished/...
2) copy/paste the following code-lines:
===
a) in a general code module:
===
Option Private Module
Public Sequence As Date
Sub StartBlinking()
Sequence = Now + TimeSerial(0, 0, 1)
Worksheets(1).Range("b1").Calculate ' modify/adapt/... worksheet's index/name as appropriate
Application.OnTime Sequence, "StartBlinking"
End Sub
Sub StopBlinking()
On Error Resume Next
Application.OnTime Sequence, "StartBlinking", Schedule:=False
End Sub
===
b) in 'ThisWorkbook' code module:
===
Private Sub Workbook_Open()
StartBlinking
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlinking
End Sub
see: http://www.cpearson.com/excel/BlinkingText.aspx
or... there is a wise-tricky procedure (posted by KL in spanish ng sometime ago)...
(and with minor changes based on Thomas Jansen -1999- post)...
that let's you to preserve the undo-levels, and you might want to give it a try...
if any doudbts (or further information)... would you please comment ?
hth,
hector.
assuming 'a cell' es [B1] and you need it to 'flash' if it's value is greater than 5
1) select [B1]... -> [menu] format / conditional format...
formula: -> =(b1>5)*(mod(second(now()),2)=0)
format: -> apply formats as needed/wished/...
2) copy/paste the following code-lines:
===
a) in a general code module:
===
Option Private Module
Public Sequence As Date
Sub StartBlinking()
Sequence = Now + TimeSerial(0, 0, 1)
Worksheets(1).Range("b1").Calculate ' modify/adapt/... worksheet's index/name as appropriate
Application.OnTime Sequence, "StartBlinking"
End Sub
Sub StopBlinking()
On Error Resume Next
Application.OnTime Sequence, "StartBlinking", Schedule:=False
End Sub
===
b) in 'ThisWorkbook' code module:
===
Private Sub Workbook_Open()
StartBlinking
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlinking
End Sub
E
Esra Dekan
No that doesnt seem to work for me.
When I open or close the workbook with the private subs in there, it
says compiling error, sub or function not defined
Same thing when i close the workbook
When i use conditional formatting, it says "You cannot use relative
references in Conditional Formatting criteria for colour scales, data
bars and icon sets."
Am I doing something wrong?
ty
Esra
G
Gord Dibben
Hector's code works fine for me.
You may have mis-read his directions or when copying code left something out.
Note: B1 won't blink the color on/off unless there is data in the cell,
although the blinking code is running. Even a <space> will start it blinking.
Gord Dibben MS Excel MVP
You may have mis-read his directions or when copying code left something out.
Note: B1 won't blink the color on/off unless there is data in the cell,
although the blinking code is running. Even a <space> will start it blinking.
Gord Dibben MS Excel MVP
E
Esra Dekan
I have huh?
I open the file after i have installed the VBA as per instructions,
(copied and pasted) and when i open it, it tells me
"Compile error. Sub or function not defined"
Also says the same when I close it after clicking off that message.
I copied and pasted the formula as provided, so I dont see how that
can be wrong.
Not sure what is going on here??,
(
Need help please.
TIA
Esra
I open the file after i have installed the VBA as per instructions,
(copied and pasted) and when i open it, it tells me
"Compile error. Sub or function not defined"
Also says the same when I close it after clicking off that message.
I copied and pasted the formula as provided, so I dont see how that
can be wrong.
Not sure what is going on here??,
(Need help please.
TIA
Esra
D
Dave Peterson
Maybe you got hit by linewrap in the newsgroup post.
Sub StartBlinking()
Sequence = Now + TimeSerial(0, 0, 1)
' modify/adapt/... worksheet's index/name as appropriate
Worksheets(1).Range("b1").Calculate
Application.OnTime Sequence, "StartBlinking"
End Sub
That "appropriate" line is the end of the comment. It's not really part of the
code.
Sub StartBlinking()
Sequence = Now + TimeSerial(0, 0, 1)
' modify/adapt/... worksheet's index/name as appropriate
Worksheets(1).Range("b1").Calculate
Application.OnTime Sequence, "StartBlinking"
End Sub
That "appropriate" line is the end of the comment. It's not really part of the
code.
Esra said:I have huh?
I open the file after i have installed the VBA as per instructions,
(copied and pasted) and when i open it, it tells me
"Compile error. Sub or function not defined"
Also says the same when I close it after clicking off that message.
I copied and pasted the formula as provided, so I dont see how that
can be wrong.
Not sure what is going on here??,
Need help please.
TIA
Esra
E
Esra Dekan
Maybe by chance, could someone email me a workbook with a flashing
cell? and I should be able to work it out from there.
I just cant seem to get it for some reason.
My email address is in the message i think, minus the underscore.
I wouls appreciate it
Esra
cell? and I should be able to work it out from there.
I just cant seem to get it for some reason.
My email address is in the message i think, minus the underscore.
I wouls appreciate it
Esra
Maybe you got hit by linewrap in the newsgroup post.
Sub StartBlinking()
Sequence = Now + TimeSerial(0, 0, 1)
' modify/adapt/... worksheet's index/name as appropriate
Worksheets(1).Range("b1").Calculate
Application.OnTime Sequence, "StartBlinking"
End Sub
That "appropriate" line is the end of the comment. It's not really part of the
code.
Esra said:I have huh?
I open the file after i have installed the VBA as per instructions,
(copied and pasted) and when i open it, it tells me
"Compile error. Sub or function not defined"
Also says the same when I close it after clicking off that message.
I copied and pasted the formula as provided, so I dont see how that
can be wrong.
Not sure what is going on here??,
Need help please.
TIA
Esra
G
Gord Dibben
What you refer to as "formula" is either a macro or event code.
As Dave points out, you could have been hit by line wrap.
My reader did not wrap but yours may have.
Here is a cleaned up set with a revision. I used a sheetname. You will edit
that to your sheetname
a) Copy these two macros to a general code module:
=================
Option Private Module
Public Sequence As Date
Sub StartBlinking()
Sequence = Now + TimeSerial(0, 0, 1)
Worksheets("MySheet").Range("B1").Calculate
Application.OnTime Sequence, "StartBlinking"
End Sub
Sub StopBlinking()
On Error Resume Next
Application.OnTime Sequence, "StartBlinking", Schedule:=False
End Sub
===================
b) Copy these two events to 'ThisWorkbook' code module:
===================
Private Sub Workbook_Open()
StartBlinking
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlinking
End Sub
======================
Gord
As Dave points out, you could have been hit by line wrap.
My reader did not wrap but yours may have.
Here is a cleaned up set with a revision. I used a sheetname. You will edit
that to your sheetname
a) Copy these two macros to a general code module:
=================
Option Private Module
Public Sequence As Date
Sub StartBlinking()
Sequence = Now + TimeSerial(0, 0, 1)
Worksheets("MySheet").Range("B1").Calculate
Application.OnTime Sequence, "StartBlinking"
End Sub
Sub StopBlinking()
On Error Resume Next
Application.OnTime Sequence, "StartBlinking", Schedule:=False
End Sub
===================
b) Copy these two events to 'ThisWorkbook' code module:
===================
Private Sub Workbook_Open()
StartBlinking
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlinking
End Sub
======================
Gord
G
Gord Dibben
Have sent one to you.
Gord
Gord
E
Esra Dekan
That doesnt work here either.
maybe something wrong with the program? Do I need an add=in or
something?
Esra
maybe something wrong with the program? Do I need an add=in or
something?
Esra
G
Gord Dibben
Worked fine before I sent it to you.
Did you get a message about enabling macros when you opened the workbook I sent?
If not, your macro security is set too high and Excel automatically prevents
code from running and puts the workbook in the Help>About MS Excel>Disabled
Items.
Have a look there first and re-enable it then reset your security level.
Tools>Options>Security>Macro Security.
Set it to "Medium", OK out, then close out Excel and re-open.
Enable macros when you get the message about workbook contains macros.
Gord
Did you get a message about enabling macros when you opened the workbook I sent?
If not, your macro security is set too high and Excel automatically prevents
code from running and puts the workbook in the Help>About MS Excel>Disabled
Items.
Have a look there first and re-enable it then reset your security level.
Tools>Options>Security>Macro Security.
Set it to "Medium", OK out, then close out Excel and re-open.
Enable macros when you get the message about workbook contains macros.
Gord
E
Esra Dekan
Still not working,
When I open the file, it comes up saying "Compile Error. Sub or
Function Not Defined"
and goes to this macro
Private Sub Workbook_Open()
StartBlinking
End Sub
When closing does the same too, goes to the macro StopBlinking
Any help??
Esra
When I open the file, it comes up saying "Compile Error. Sub or
Function Not Defined"
and goes to this macro
Private Sub Workbook_Open()
StartBlinking
End Sub
When closing does the same too, goes to the macro StopBlinking
Any help??
Esra
D
Dave Peterson
Did you put the StartBlinking and StopBlinking subroutines into a General module
in that same workbook's project?
in that same workbook's project?
G
Gord Dibben
Does this occur in the workbook I sent you?
If so, have you changed anything at all?
Was working fine when I emailed the workbook to you.
"asdfg" in B1 was blinking a green color.
I still have the original if you want it re-sent.
Gord Dibben MS Excel MVP
If so, have you changed anything at all?
Was working fine when I emailed the workbook to you.
"asdfg" in B1 was blinking a green color.
I still have the original if you want it re-sent.
Gord Dibben MS Excel MVP
E
Esra Dekan
Yes, happens in the workbook you sent, and cell b1 is just green with
text in, not blinking.
All i get in MY workbook here, is the compile error thing. I dont
know enough to know what that even means.
The Private sub thing, I right clicked "this Workbook",,,and inserted
a module, pasted the Private Sub into there.
It must be something with my excelt surely, coz it happens on the book
you sent me,,,maybe?
(
TIA
Esra
text in, not blinking.
All i get in MY workbook here, is the compile error thing. I dont
know enough to know what that even means.
The Private sub thing, I right clicked "this Workbook",,,and inserted
a module, pasted the Private Sub into there.
It must be something with my excelt surely, coz it happens on the book
you sent me,,,maybe?
(TIA
Esra
D
Dave Peterson
Rightclick on ThisWorkbook and choose View code.
Don't insert a module.
Don't insert a module.
E
Esra Dekan
That doesnt work either.
Going to give up I think.
Thanks
Esra
Going to give up I think.
Thanks
Esra
G
Gord Dibben
Other than send you another working copy of a workbook, I see nothing else to
help you.
Well........maybe try opening the Immediate Window from VBE and copy this in and
hit ENTER
Application.EnableEvents = True
Gord
help you.
Well........maybe try opening the Immediate Window from VBE and copy this in and
hit ENTER
Application.EnableEvents = True
Gord
E
Esra Dekan
Now I am even more confused. Im so dumb when it comes to a lot of
things in Excel.
Where is the "immediate" window in VBE?
Esra
things in Excel.
Where is the "immediate" window in VBE?
Esra
D
Dave Peterson
Open the VBE and hit ctrl-g (or view|Immediate window)