Can Excel announce a status when someone opens the workbook? This macro will let someone know if they are over or under quota. Episode 848 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Great question from the road-- I was out doing a seminar and I always showed the text-to-speech feature and someone said, "Hey, could I have a Macro automatically say something when someone opens the workbook?" And, in this case, he said, "We want to take a look at this status number, and if it's positive, say on thing; if it's negative say another thing." And so that particular cell happens to have the Range name of 'Status'.
Here's what we're going to do, we're going to switch over to the VBA editor with Alt+F11, and this isn't a Macro that we're going to put on a module; this is a Macro that we're going to put in this workbook.
So, over here in VBAProject, I'll find this workbook; double-click and i get the code.
Basically, i want to.. in the left drop-down, choose Workbook and then the right drop-down there's a whole bunch of items here, but Open is the one that pops up.
I'm going to take a look at that cell.
We're going to say: If range ("Status").Value > 0 Then Application.Speech.Speak "You are currently meeting quota" Else --if it's negative, we can have it say something else-- "You're currently below quota" End If.
And what's going to happen is, every time that we open this workbook, it's going to take a look at that number.
So here we're above quota, I'll save it, File Close, and then open it again-- "You are currently meeting quota".
There we go.
And if it was negative-- so I'm making that -1,000-- we'll save that, close it, are you open it.
So there you have it.
Great little tool.
Basically, have Excel say something to the person who opens the workbook as they open it, using Application.Speech.
Well, hey, thanks for stopping by, I'll see you next time for another netcast from MrExcel.
[ music ]
Great question from the road-- I was out doing a seminar and I always showed the text-to-speech feature and someone said, "Hey, could I have a Macro automatically say something when someone opens the workbook?" And, in this case, he said, "We want to take a look at this status number, and if it's positive, say on thing; if it's negative say another thing." And so that particular cell happens to have the Range name of 'Status'.
Here's what we're going to do, we're going to switch over to the VBA editor with Alt+F11, and this isn't a Macro that we're going to put on a module; this is a Macro that we're going to put in this workbook.
So, over here in VBAProject, I'll find this workbook; double-click and i get the code.
Basically, i want to.. in the left drop-down, choose Workbook and then the right drop-down there's a whole bunch of items here, but Open is the one that pops up.
I'm going to take a look at that cell.
We're going to say: If range ("Status").Value > 0 Then Application.Speech.Speak "You are currently meeting quota" Else --if it's negative, we can have it say something else-- "You're currently below quota" End If.
And what's going to happen is, every time that we open this workbook, it's going to take a look at that number.
So here we're above quota, I'll save it, File Close, and then open it again-- "You are currently meeting quota".
There we go.
And if it was negative-- so I'm making that -1,000-- we'll save that, close it, are you open it.
So there you have it.
Great little tool.
Basically, have Excel say something to the person who opens the workbook as they open it, using Application.Speech.
Well, hey, thanks for stopping by, I'll see you next time for another netcast from MrExcel.
[ music ]
