This post is from GuestBuster Jeff Weir in our Chart Busters series.
Note: The post slightly longer, but worth every word. Just get a cup of coffee and soak in to this visualization goodness. (Also, click on any image to see its full version)
Over at the FlowingData blog, they’ve been talking about this pretty slick looking Choropleth Map that shows how Medicare returns vary across the United States:
The above shows total Medicare reimbursements in 2006, either by Hospital Referring Region or by State, depending on the radio button. Using the dropdown box, you can change it to this:
…which is how the data looks if you overlay it on a Giraffe. Oops, I forgot to rotate it before saying that. Bear with me a moment…
There. See the Giraffe now? Good.
A picture is worth a thousand words, or so they say. But is a Choropleth worth the many line charts and clowns that you could squeeze into the same valuable screen real estate? Let’s find out, by evaluating what this particular chart does well, and what it does poorly, and whether other charting methods might better convey its information.
Words and music.
Right off the bat, there’s a simple way that the authors could improve this chart. While they include a description below the chart to point out what the data is, and where it came from, they miss something just as important…what they concluded from all of this. So before we consider adding – say – bullet graphs, let’s consider adding some bullet points. A few sentences can tell readers important stuff that would otherwise remain hidden in an undownloaded PDF report. Insights like:
- Care is often better in low-cost areas.
- Growth in returns are only partly explained by advancing technology, and
- Differences in growth rates across regions seem largely due to discretionary decisions by physicians that are influenced by the local availability of hospital beds, imaging centres and other resources-and a payment system that rewards growth and higher utilization.
Straight off the bat, this would make the graph a better graph…without even messing with its form.
But mess we must…
…because lurking below the chlorophyll green of this Choropleth Map are a few serious charting oversights. Ready? Let’s check ’em out.
Scale? Fail!
First, check out the legend.
Crikey…its bands are as discrete as Bruno. Its scale is about as even as my temperament. It varies about as much as =RANDBETWEEN(PaydayBankBalance, UsualOverdraft).
If you fire up Excel and look at the spread covered by each range, you see just how arbitrary the different price bands are:
Whoa…the spread of that $9k to $16k band is nearly 15 times larger than two of the other bands. That can’t be good, can it?
Nice profile
If you were to graph financial spread of each group against the aggregated number of Hospital Referral Regions that fall within each spread, you get something like a histogram. The difference between the sizes of these bands is about as different as the number of performers on stage at a Bob Dylan concert in 1964 compared with 1974. See for yourself:
Oops, wrong graphic. Try this:
Normally histograms have equal widths for each band, but here I want to highlight just how unequal the bands used are. Plus, this lets us regroup the data into evenly spread $1k bands, and overlay it on the first distribution, to see how it compares. Here’s one that I prepared earlier, with the red line as the regrouped data…
Vastly different picture isn’t it. The red is kinda like Data Pig’s heart rate before he eats chocolate covered bacon on Saturdays, and the blue is how his ECG would look when he’s in the ambulance, on the way to the hospital.
This makes it very hard to answer that important question “…compared to what?” With such different sized bands, how can we compare one to another? How can we be sure that the distributions within each band will even allow us to?
For instance, take the highest band spread of $9k to $16k: without any further information to go on, we might assume that the median (i.e. middle) value for districts in this category is midway between the $9k to $16k boundaries, like this:
But that’s like assuming that Simon and Chartjunkle (oops, Garfunkel) have equal talent. We’d be wrong. Verywrong. In actual fact, there’s only three data points to the right of our guessed median line. And as for the 55 hospital regions in Group Five that fall to the left of it…well, they all get tarred with the same brush those worst three performers. The actual median for this group is a lot further left, as shown below:
This means that over half the data in this 5th band actually falls much closer to the far left of the graph than to the far right of the same group it’s been placed in.
You can see this better if you add a one-dimensional strip plot above the graph, which gives an idea of where the 300 odd values fall within the entire range:
Whoa…looks like we’ve got a few outliers to contend with.
What a State we’re in…
This seemingly arbitrary ‘bucketing’ effect is exacerbated when aggregating the different hospital regions into State-wide totals. Except this time regions are being penalised by arbitrary geographical boundaries, as well as the arbitrary financial ones above.
Take Texas for example. Aggregating everything up to the State level, Texas appears in that highest band. Yet at the Hospital Referral Region level, one third of its 22 different hospital fall below the national average, and the median for the whole State is around $8,800. So we better be careful making assumptions from a State-wide view, because the Choropleth averages some very diverse costs over some very large chunks of real estate.
To see just how diverse, let’s rank the entire US values from smallest to largest, and highlight where the Texas readings fall within that range:
What can we tell from this? Firstly, nearly all regions nationwide fall between $5k and $10k. Secondly, there are a few outliers that really skew the picture at the high end. Thirdly, in the Texas case, the State average is boosted somewhat by 3 Texan districts that happen to be among the worst 10 culprits nationwide – one of which is clearly an outlier at $15k. Unfortunately for the lower cost Texan regions, they’re guilty by geographical association…kinda like being kidnapped and held for a zillion dollar ransom, just because you happen to live in the same State as Bill Gates.
So what do we get by aggregating to State boundaries? Probably more blurring than insight. After all, what good would a weather report be to Texans if it only reported the average weather they could expect as a State! Instead, it’s better to keep the aggregation at the Hospital Referral Region level. That way, we can look at this:
…and ask things like “Wow, why such a difference between Waco and the surrounding bits of Texas?” and “What the hell is Alaska doing there?”
Legends in the making…
What’s far worse that this though is that when looking at the State-wide map, the legend is now really, really wrong.
Here’s the legend next to the actual State-wide figures, for comparison:
Whoops…the graph title has changed to reflect we’re now looking at Medicare spending per beneficiary per State; i.e. State averages. The legend is still looking at Hospital Referral Region averages, which have a much greater spread. For instance, the Choropleth shows six States as being dark green regions, and the legend says they fall somewhere within $9k to $16k. But the actual data shows they fall in a $9.4k to $9.6k range. Oops! Slight misrepresentation, there.
How to fix it
Obviously this graph really should use a quantitative scale with equal increments; one that changes to reflect the selection that users make. What’s more, colors should have just enough variation so as to highlight any important differences, without being overwhelming or mistaken for camouflage.
But is a Choropleth Map the best way to present this data in the first place? If you want something for people to play with online, then maybe…but if you want to compare things very closely to other things, then maybe not.
For sure, a Choropleth Map looks cool, and it has what Tusha Metha calls “natural context”. But from an analytical perspective, a Choropleth only really reports how one thing changes with regards to geography. If geography is a major determinant – or if you want to show people how things look in their own back yard compared to others – then perhaps this is the piece of kit you need. But if there’s other factors that have much more sway on your data than geography, then perhaps not. For instance, we might want to see whether population density plays a significant part in Medicare returns, given the likely economies of scale from providing healthcare to densely populated regions vs. urban regions. Nows the time to break out a scatter plot:
Hmmm…looks promising. (Note: I’ve used State-wide data for the above…ran out of time to track down densities in the different Hospital Referral Regions, which is what I’d prefer to do.)
Or we might want to zoom in on the best or worst offenders, and see just how different they are to each other, and to the median value:
Conclusion
I think a better, fairer Choropleth Map at the Hospital Referral Region level would be interesting. But I don’t think it would be enough. To quote from Stephen Few’s latest book Now you see it: “Color is good at drawing your attention to something if used sparingly, but is one of the ‘pre-attentive attributes’ that is not quantitatively perceived in and of themselves”.
Whereas lines and 2D precision are very precise ways to encode quantitative values.
So when it comes to answering the ‘Compared to what’ question, I don’t think you can beat this:
Choropleth Maps in Excel
For information on the implementation of Choropleth Maps in Excel, check out Tushar Mehta’s excellent resources.
For more information on the pros and cons of Choropleth Maps, check out the Clearly and Simply blog, where Robert has built on Tushar’s excellent approach to produces some great downloadable templates. He also offers advice on potential drawbacks of Choropleth Maps, such as:
- No visualization of development over time
- No information on exact values (unless you are implementing tooltips including the data)
- Very limited direct comparability of the regions
- Possible perception problems with regards to the size of regions (e.g. Rhode Island on a US map)
- Possible misinterpretation because the size of a region may have a greater impact on the user’s visual perception than the intensity of the fill color
- Requirement of real estate on a dashboard
His recommendation: carefully consider whether or not a Choropleth Map is the best visualization for your purposes. Check out his dashboard of Lithuania at a glance to see how he mitigates some of the potential problems by incorporating other graphs into the display.
I used Robert’s template to produce this State-wide Choropleth Map of total Medicare spending per enrollee, 2006 using the same Medicare ranges as the Choropleth that’s the subject of this post:
…
…then I replotted the graph using data that had been regrouped $1k bands:
While I don’t advocate this approach, it’s interesting that even though this is aggregated to State-wide totals, you can see significant differences between the graphs.
Right, that’s it. I’m off to the Hospital to see someone about my writers cramp…
About the Author
Jeff is a Business Analyst from Wellington, New Zealand who has recently discovered a strong interest in Data Visualization. He swears by Edward Tufte and Stephen Few as much as he swears at Excel 2007. He’s so new to advanced Excel, that 2 years ago he had to ask a work friend what the dollar signs in $A$1 meant. Now that he knows that, he’s trying to find out what the dollar signs in $A$2 mean.
Note from PHD:
Thank you Jeff. Your passion and knowledge is truly outstanding. I have a whole pack of donuts waiting for you.
53 Responses to “Conditionally play sounds when a cell value changes”
There is another alternative of doing this quite easily: Yes, Excel is able to talk to you!
Assume you are having a calculated or otherwise changing value in cell A1 (e.g. =RANDBETWEEN(0,100)), a threshold in cell B1 (e.g. 50) and a text in cell C1 (e.g. "Threshold exceeded").
Go to the VBA-editor (ALT-F11) and copy the the following procedure into the corresponding sheet:
Private Sub Worksheet_Calculate()
If Range("A1").Value > Range("B1").Value Then
Application.Speech.Speak (Range("C1").Value)
End If
End Sub
Excel will read the text in cell C1, whenever the value in cell A1 is large than the threshold in cell B1. Turn your speakers on and play with cell C1. It sounds a bit funny sometimes, especially when you try to make Excel read texts in other languages than English. But it works...
I'm trying to use UDF and the speech aspect. I want a cell to calculate character total and when it reaches a limit it will speak the value. For some reason I can't get any UDF to show up in the list or work. I've tried simple ones such as:
Function BeepOnce()
Beep
End Function
with: =If (B3>30, BeepOnce(),"") in the cell but it gives errors. Confused.
When a cel char count reaches 30 and I'm typin gfast, it will report the number.
Im a blind writer and this would be very helpful. I can't get any to work though. Can't get any UDF to show up anywhere.
I tried keying in the code exactly as you have:
Private Sub Worksheet_Calculate()
If Range("cx15").Value > Range("cy15").Value Then
Application.Speech.Speak (Range("cz15").Value)
End If
End Sub
When I try to run the condition, I get a compliation error.
The formula in my excel spreadsheet in cx 15 is: =IF(CO15>120,1,0)
cy 15 = 0
cz 15 = "Less than 120 days"
I'm driving myself crazy - HELP!!
@Mary
The code you supplied and below works as it should in Excel 2007
===
Sub Worksheet_Calculate()
If Range("CX15").Value > Range("CY15").Value Then
Application.Speech.Speak (Range("CZ15").Value)
End If
End Sub
===
There are 2 reasons it maynot be working
1. Make sure it is on a Worksheet Object in VBA for the page you are working on not in a general code module
2. Make sure the " characters are real, often you can copy and paste text from here and although the " mark look ok, they are not the correct characters, If it isn't working retype them all
Hi Hui,
The SPeech VBA is working...but it reacts to all the changes that is taking place in various cells in the sheet. I need this speech vba to react to only when changes happen in a particular cell (A1) and particular sheet...is there a vba that can help me find a solution. Thanks in advance for your assistance.
@Anil
Some of the comments above discuss that requirement
Please read those first
YEAH - thanks so much Hui. I had it in the general code module.
Another question?
Can I have this function check a range of cells in Excel?
The code as it is now is checking cells CX15, CY15 and CZ15
I need it to check from rows 15 - 45?
@Mary
Sub Worksheet_Calculate()
Dim myText as String
For Each c In Range("CX15:CX45")
If c.Value > c.Offset(0, 1).Value Then
myText = "Row" + Str(c.Row) + " " + c.Offset(0, 2).Text
Application.Speech.Speak (myText)
End If
Next
End Sub
I keyed in the code exactly as above and I get a runtime error 1004 - application defined or object defined error.
Sub Worksheet_Calculate()
Dim myText as String
For Each c In Range(”CX15:CX45?)
If c.Value > c.Offset(0, 1).Value Then
myText = “Row” + Str(c.Row) + ” ” + c.Offset(0, 2).Text
Application.Speech.Speak (myText)
End If
Next
End Sub
The formula in my excel spreadsheet in cx 15 is: =IF(CO15>120,0,1)
cy 15 = 0
cz 15 = “Less than 120 days”
The code is in a VBA worksheet - any thoughts as to what I'm doing wrong?
Mary
you need to be very careful copying text of the web
There are times where a character " will copy and look like a " but is in fact not a "
If you have copied the above go through the code and manually replace all the " characters with a " character
Also make sure the code is on the code page for the sheet you are working on, not a code generic module page in VBA
The above string works wonderfully - however, every keystroke in Excel initiates the function. Is there a way I can set it to run on command?
Mary
The following will run whenever there is only a change in the area CX15:CZ45
.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myText As String
Dim TR As Integer
Dim TC As Integer
TR = Target.Row
TC = Target.Column
If TC >= 102 And TC = 15 And TR c.Offset(0, 1).Value Then
myText = "Row" + Str(c.Row) + " " + c.Offset(0, 2).Text
Application.Speech.Speak (myText)
End If
Next
End If
End Sub`
.
Otherwise you can go back to the following code and add a Button or a Shape to your worksheet and link the macro to it so that it only runs when you click on it
.
Sub Speak()
Dim myText As String
For Each c In Range("CX15:CX45")
If c.Value > c.Offset(0, 1).Value Then
myText = "Row" + Str(c.Row) + " " + c.Offset(0, 2).Text
Application.Speech.Speak (myText)
End If
Next
End Sub
Hey Hui,
I came across this site by mistake.
Man this is so COOL.
Will this also work as
Column A has all Names, Column B is Blank.
When a Cell in Column B is not Blank ("") then speak the corresponding name in Column A
It doesn't make any sense to write multiple if-then statements to make it work.
I know I caught this few months late, but I could really use this
Thanks
@Amin
Try the following
Copy and paste it into a Worksheet Module for the Worksheet in VBA you are working on
Hui,
It works but it repeats all name as:
Cell A1=John, A2=David, A3=Lisa.
When Cell B1 is not Blank then is speaks John.
Next when Cell B2 is not Blank it speaks John and David. Second time it should only speak David (fyi B1 and B2 both are not blank)
example: I want it to speak when certain individual logs in. But procedure, when someone logs in it speaks all the people who have already logged in.
how should I change that?
Thanks for assistance.
@Amin
Try this modification
Thanks Hui that worked perfectly.
How do I make it work with 4 sets of columns
Same as before but with 8 columns
Columns(A,C,E,G) have names.
Columns(B,D,F,H) are blank to start with.
I tried to few ideas my way but nothing worked
@Amin
I need a Excel Speaking code for the below condition
If I3 ="Buy Alert" ' If I3 cell has Buy alert Text then, sound should be like " Vivek Buy Alert For Infosys(next cell text) and
If I3 ="Sell Alert" ' If I3 cell has Buy alert Text then, sound should be like " Vivek Sell Alert For Infosys(next cell text)
Please help me to fix this in my excel
This is soo cool. Im looking for a similar thing
Source B1:B200 have an output in Range C1:C200 every cell in the range C1:C200 checks the condition in D1:D200 and with conditional formating change color when value in C1>D1
How can I play a standard windows sound when C1>D1 and another sound when C1<D1
hi hui,
i am a trader in stock market
i can export stock rate to excel sheet from my trading software..
is it possible that when stock rate changes the cell speaks means, e.g.( if stock rate is 25 and if it changes to 26 then cell should speak twenty six,if it further changes to 26.50 it speaks twenty six point fifty)
hope u can understand my problem.
waiting for your reply
@Viral
Have a look at: http://chandoo.org/wp/2008/08/04/play-sound-when-cell-value-changes/
HI
I HAD A LOOK AT YOUR SUGGESTIONS BUT UNABLE TO UNDERSTAND
I AM USING OFFICE 2003,
I TRIED THIS
"""""Assume you are having a calculated or otherwise changing value in cell A1 (e.g. =RANDBETWEEN(0,100)), a threshold in cell B1 (e.g. 50) and a text in cell C1 (e.g. “Threshold exceeded”).
Go to the VBA-editor (ALT-F11) and copy the the following procedure into the corresponding sheet:
Private Sub Worksheet_Calculate()
If Range(“A1?).Value > Range(“B1?).Value Then
Application.Speech.Speak (Range(“C1?).Value)
End If
End Sub""""""
BUT I DON'T UNDERSTAND WHAT TO CALCULATE IN ("C1")?? WHICH RANGE TO BE GIVEN TO ("A1")
BASICALLY I DONT GETTING IT PROPER
WHAT I WANT IS IF LAST TRADED PRICE (LTP) IN CELL CHANGES THE EXCEL SHOULD SPEAK THE VALUE WHICH IS IN THE CELL
MEANS IF LTP IS 26.60 AND IF CHANGES TO 26.70 IT SHOULD SPEAK TWENTY SIX POINT SEVENTY AND IF IT CHANGES FURTHER TO 26.80 IT SHOULD SPEAK TWENTY SIX POINT EIGHTY
PLEASE HELP ME....
I am using the following code for sound. It was working well but has suddenly stopped respondig. Could u help relating to the matter
Private Declare Function PlaySound Lib "winmm.dll" _ Alias "PlaySoundA" (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long
Function Alarm(Cell, Condition) Dim WAVFile As String Const SND_ASYNC = &H1 Const SND_FILENAME = &H20000 On Error GoTo ErrHandler If Evaluate(Cell.Value & Condition) Then WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit this statement Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME) Alarm = True Exit Function End If ErrHandler: Alarm = False End Function
Hey Hui, Great stuff really helpful, Although I am having the same issue as Amin, where any keystroke entered prompts the text to speech. I tried fitting in your fix for Amin but I didn't have success, below is the code I am successfully using, however each keystroke prompts the voice, and the voice lists each cell that qualifies the formula I am using.
Sub Worksheet_Calculate()
Dim myText As String
For Each c In Range("bf4:bf45")
If c.Value = 1 Then
myText = c.Offset(0, -57).Text
Application.Speech.Speak (myText)
End If
Next
End Sub
I tried
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Application.Speech.Speak (Target.Offset(0, -57).Text)
End Sub
any Mods to limit the speech to only what was most recently qualified?
@Elliot
Try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = 1 And _
Target.Column = 58 And _
Target.Row >= 4 And _
Target.Row <= 45 Then _ Application.Speech.Speak Target.Offset(0, -57).Text End Sub
Hi Hui,
I tried that code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = 1 And _
Target.Column = 58 And _
Target.Row >= 4 And _
Target.Row
Thanks
Seems like the previous cut off the rest of my message, anyways above code the object module didnt allow me to step inot it, I keep getting a pinging, while on the same object module I inserted the previous code I had running and it allows me to step in! Confusing.
Thanks.
Hey Hui,
So the code works, I discovered the code does not recognize the column because each cell in the column is an if then formula with a returned value, such as "1" for true "0' for false, it only works when I remove the formula and insert a 1 in a cell, any vba fixes for this, or I can try a temp. workaround.
Hey Hui,
Disregard the above. The code works, however only after I hit enter in the target column, When the column updates by itself the code doesn't fire.
Hi Hui,
Amaze to see all the above information. I hope even I get a solution to my problem.
I am trying to use this excel as a token machine. I have linked up this token sheet with other sheet which shared by four users. If any off the users changes the token number, it gets change in our main display but the problem it doesnt say the token number. WHAT I EXACTLY WANT IS AS THE VALUE GETS CHANGED IN ANYONE OF THIS SPECIFIED CELLS (B4, B5, B6 & B7 THE EXCEL MUST ANNOUNCE THE NUMBER. For example If in cell B4 the number 7 comes, so the excel should tell " TOKEN NUMBER 7", after that in cell B7 is updated 21, so its should again tell as " TOKEN NUMBER 21"... and so on it should keep doing the same.
I hope I get my solution at the earliest.
Thanks
Raj
Dear Sir,
I read your blog, it's very nice. I am a trader, & need a help from your side.
If my stock goes below -5.5 % ( column is = L,M,N,O) then it has to automatically change it's color & make a beep sound or alarm. Please write the code for this query.
Thanks in advance,
Warm Regards,
Nagesh
@Nagesh
Try the following
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value < -0.055 And Target.Column >= 12 And Target.Column <= 15 Then Application.Speech.Speak Target.Text Target.Interior.Color = 123456 'Change to suit End If End Sub
hi sir,
i tried your coding today,It works for color and sound didn't come for below -0.055!
but getting the below error
Application.Speech.Speak Target.Text ( it's in yellow color in vb code sheet). Also got error like run time error, application defined or object error! I am using ms excel 2003.
please advice me
@Nagesh
That won't work in 2003
I'll see what options there are tomorrow
It's sleeping time here
ok sir,
See you tommorrow, Sleep tight, sweet dreams, bye
nagesh
@Nagesh
I think the blog software removed some details
The code as posted was
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value LTE -0.05 And Target.Column GTE 12 And Target.Column LTE 15 Then
Application.Speech.Speak Target.Text
Target.Interior.Color = 123456 'Change to suit
End If
End Sub
Replace GTE with >=
Replace LTE with <=
Hi Hui,
Thanks for your kind reply. I will try this ..
Regards,
nagesh
Hi Hui Sir,
I tried your updated coding, again it's showing same error. Sir, i will make you things clear as follows"
1) I am using MS EXCEL 2003 & It's a part of MS OFFICE STANDARD EDITION 2003.
2) I'm new to this excel, I need formula for the excel and coding for vb .
3) Sir, Below is the Data needs to play sound when the Number comes down -5.5%. It's in L,M,N,O, P Column of Excel Respecitively!
%Total 1 = L Column; Optional = M Column; Calc=N Column, Final=O Column, Additn = P Column in Excel Respectively!
%Total 1 Optional Calc Final Additn
1.0664851 -5.567 -0.499205809 -0.198547765 1.76
4.44350551 -0.1267561 -0.348579275 -0.237667688 2.66
-2.2927545 -2.651762402 -2.472258486 -2.562010444 -2.24
1.194029 0.608955224 0 0.304477612 0.1
Sir please advice me, It's very impt to me to play sound when it comes down -5.5 %, Once it's done, i'll be very happy.
Also, i want to learn online excel vb from your end if you providing excel training, please tell me fees structure and discounts for the excel training.
Expecting Solutions for my queries and hope you solve.
Thanks for your kind reply for my previous mails.
Warm Regards,
Nagesh
Hi Hui Sir,
The code you adviced me is :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 12 And Target.Column < = 15 Then
Application.Speech.Speak Target.Text
Target.Interior.Color = 123456 'Change to suit
End If
End Sub
It's working fine, but only problem is :-
1) It's speaking for all numbers like, -1 %, -2 %, -5%.etc.., I need only for -5.5%.
2) In Cell "A" , I HAVE LIST OF MEDICINES like "Ranbaxy - A2 ", "Cipla - A3", "Micro Labs - A4" upto "A 168". If the data in "L,M,N,0,P" Column comes down in b/w -5.5 % to -20.5 % then , Ms Excel 2003 has to Say: Ranbaxy -5.5 %, Cipla - 6.6 %, Micro Labs - -10 % etc..
@Nagesh
The code I posted is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value LTE -0.05 And Target.Column GTE 12 And Target.Column LTE 15 Then
Application.Speech.Speak Target.Text
Target.Interior.Color = 123456 ‘Change to suit
End If
End Sub
Replace GTE with >=
Replace LTE with <= If this isn't clear, please post a file in the Forums http://chandoo.org/forum/
Hi Hui Sir,
i'm trying to post file in the forum : http://chandoo.org/forum/, it's not taking there. Please Help me the below, Please sir, please
The code you adviced me is :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 12 And Target.Column < = 15 Then
Application.Speech.Speak Target.Text
Target.Interior.Color = 123456 'Change to suit
End If
End Sub
It's working fine, but only problem is :-
1) It's speaking for all numbers like, -1 %, -2 %, -5%.etc.., I need only for -5.5% to -20.5%
2) In Cell "A" , I HAVE LIST OF MEDICINES like "Ranbaxy – A2 ", "Cipla – A3", "Micro Labs – A4" upto "A 168". If the data in "L,M,N,0,P" Column comes down in b/w -5.5 % to -20.5 % then , Ms Excel 2003 has to Say: Ranbaxy -5.5 %, Cipla – 6.6 %, Micro Labs – -10 % etc..
Requesting last last help from your end, my work almost done with your valuable time and advice. Please help me sir,
nagesh
@Nagesh
This is the code, I have posted 3 times
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value LTE -0.05 And Target.Column GTE 12 And Target.Column LTE 15 Then
Application.Speech.Speak Target.Text
Target.Interior.Color = 123456 ‘Change to suit
End If
End Sub
Replace GTE with >=
Replace LTE with <= If the Forum isn't working your file is too large Compress it or save to a dropbox etc style account or email me directly, click on Hui and email is at bottom of the page
Here is a copy of part of my Dash Board:
Alert Summary
EM 22.8%
FIN 22.1%
ENERGY 18.1%
SPX 24.0%
NDX 27.0%
RUSSELL 23.5%
Each of the percentages is a link to a separate page in the work book. For example, the 22.8% is actually =+EM!D10 which is the answer to the following formula =((ABS(G7/((F7+F6)/2))))
I want some sound alert that will tell me when the value is greater than 35%. Since the spread sheet dlynamically update every second, I would assume that a ding ding ding repeating would work fine.
Can you give me a step by step on creating an appropriate macro that I can run when I am afc. I have no programming skills but can cut and paste or retype with the best of them 😉
Thanks in advance for any help you can give me. BTW the range on the Dash board that I want to cover is b10...b15
Dear Huai,
is there a way to have a vba code to make a sound alert dynamically when a condition is met without running the module or have a button? I tried the options above and beep now commands but no luck.
More explicit instruction are required. Where exactly does one put:
Function beepNow()
Beep
End Function
??
Dear Sir (Hui),
Pls help me to provide such a excelmacro code so that I can easily heard a specified Sound of the text which I want when the criteria or the condition happened.It may be Hello/Congratulations etc.
Suppose A120=SUM(A5:A119) &
D120=SUM(D5:D119)
G120=A120-D120
Condition:IF(G120=0,"hello"/"congratulations "
Otherwise "Sorry" / "be patience"
Please help if possible.
@Nazmul
Please ask this in the Chandoo.org Forums http://forum.chandoo.org/
Please attach a sample file to simplify the task
@Hui,
I can not find the attachment option & message option in chandoo.org site after sign up.Please help.
@Nazmul
You have to be registered
Then goto: http://forum.chandoo.org/forums/ask-an-excel-question/ and select Post a New Thread
Complete the form
Next to the Submit button is an Add Attachment button, or simply drag the file onto the screen
Hi,
I need a code for the cells speak
auto matically with a regular interval of 10 min.means after every 10 min my cells speak.
Hi Hui,
Hope you are in good health and doing well. I was surfing the internet for some excel solutions for my workbook and I came across some very good solutions by you. Hence, I am writing to you to get some insights to my problem
So here it goes,
I have a software which gives me live stock prices in excel and I have added all my analysis to that file so that I can get Long and Short Calls for the scrips I analyze. Now, there are around 200 companies that I analyse and the calls appear as the stock price change and reach my buying or selling levels.
I need my excel to say the companies name which is in cell C2 when "Long" / "Short" text appears in my cell B2 which has my analysis formulas. Current market price is in A2
Also I need this for a range as I mentioned I have 200 companies.
I really hope you can help me with this.
This is what I have tried uptill now, but this works for one cell and not a range
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("B2").Value = "50" Then
Application.Speech.Speak (Range("C3").Value)
End If
End If
End Sub
Please Find me a Solution
Thank You,
Best Regards,
Nishant Shah
@Nishant
Can you please ask this question in the Chandoo.org Forums http://forum.chandoo.org/
Please attach a sample file to simplify the solution