I was reviewing someone's workbook the other day way I saw some code similar to this:
Sheets("Sheet1").Unprotect Password:="MeatFace"
PROCEDURE
Sheets("Sheet1).Protect Password:=" MeatFace "
This code unprotects the sheet, runs a procedure, and then protects the sheet again. The reason this guy wrapped his procedure with Unprotect/Protect is because his procedure changes a worksheet in some way. And you cannot run a macro that changes a worksheet while that worksheet is protected.
Sandra asks:
"Any way to add a rank to a pivot table"?
DataPig Answers:
In Excel 2010, it's really easy. In Excel 2007/2003, it's a bit clunky.
So I decided to record a macro, and for some reason, I forgot to hit Stop Recording. By the time I realized I was still recording, I had created a macro that represented a half a day's work.
Don't laugh…you've done this too.
If you're not certain if you're in Macro Record mode or not, you can simply look at the bottom left corner of your screen.
» More: Handy Macro and Career Tip
I read somewhere once that your ability to draw a perfect circle is directly related to your IQ. That is to say, the more perfect your circle, the smarter you are. Well that’s bad news for me. Every time I try to draw a circle I comes out like this…
In another show of the old passive aggressive spirit, here's a new way to limit what the end users can do.
The Subtotal feature in Excel often comes in handy when I need a quick way to inject 'Total' lines into your tables. But I don't really want them to use the drilldown thingies that Subtotals come with.
Here is the simple fix. Simply press Ctrl+8 on your keyboard to hide the drilldown thingies.
Your subtotals remain but the user cannot change the drill level. Neato!
To make them come back, simply press Ctrl+8 again.
One of my clients asked me if there was a way to quickly select non-blank cells in an Excel worksheet – similar to the way you would use GoTo->Special->Blank Cells. As I was explaining the VBA technique that would allow him to do this, I could see by his 1000-yard stare that he was allergic to VBA programming. So I gave him my quick and easy shortcut key trick.
I'm back from our version of summer vacation with the family. We spent most of our time around by the pool. The kids insisted on being in the pool and Mrs. Pig doesn't want them to die, so apparently, this means I have to sit in the sun and watch them do suicidal aquatic tricks. Needless to say, I'm cooked like a piece of well-done bacon. It's nice to be back in an environment where the temperature is less than the boiling point of water.
Going through my post-vacation email exercise, I noticed an email from Vaughn. He writes to ask:
"Is there a way I can force the active cell to always show up in the up left hand corner?"
Ok Vaughn, I'm not sure why you would do this, but I could use some blog topics.
Every now and then, you encounter a table where color is supposed to mean something. In this table, each color represents a certain attribute (red = below target, yellow = in danger). If you're thinking that tagging records with a color is stupid, I agree. But we live in a world full of goofballs that think it's clever to do things like this.
I'm pretty busy today, so here's a quick one.
Did you know you can quickly jump to the last cell in a column or a row with a simple double-click of the mouse?
So I'm teaching this Dashboarding class with Jon Peltier, when he mentions that if you format your dates with 5 m's ("mmmmm") you get the first letter of the month name.
What?! Do you know how long I've been typing (J, F, M, A, M, J, J, A, S, O, N, D)? It's beyond me how I never found this nifty trick.
Here's the deal:
