In my life-changing post, Running a SQL-Stored Procedure from Excel, I showed you how to make Excel interact with a SQL server Stored Procedure.
.
Since that post, I’ve had several requests to cover the topic of passing dynamic parameters to a Stored Procedure. So today, I will FINALLY show you.
» More: Running a SQL Stored Procedure from Excel with Dynamic Parameters
Over the Thanksgiving holiday, I was ignoring the family and puttering around on the internet. There, I found a list of all the cool API calls you can do through VBA. An API call is essentially a piece of code you can use to make the Windows operating system do things – like open a directory, freeze the system, open a dialog box, etc.
.
Well, there is an API Call that essentially opens your CD Rom drive. That’s right – you can implement code so you don’t have to go through the painstaking task of pressing the Open button for your CD Rom drive. Wow …. AUTOMATION IS THE BEST!
.
OK…this gets my vote for the most useless API call. Second place is the API call to close the CD Rom Drive.
.
That being said, you could use this useless API to prank your coworkers.
» More: Open CD Rom – The Most Useless API Call
Last week, Debra over at Contextures posted an article that showed us lots of nifty ways to navigate a workbook that has many worksheets. Today, I’d like to add my two cents by sharing a small macro that will give you a fairly easy way to jump from sheet to sheet.
.
We all have them – those workbooks that have 10, 15, 20 or more tabs.
I personally have lots of training workbooks that have 15+ tabs.
When you’ve got a workbook that has this many sheets, it can be a pain to find the sheet you need.
» More: Easily Navigate Many Worksheets
One of my 12 fans (Lester) emailed to remind me that I have a blog. I almost forgot. So with a good kick in the pants, I’m back to blogging.
.
While I was away, Microsoft awarded me with the coveted Microsoft Excel MVP award for the fourth time. So I guess I better start earning that distinction here. Here we go.
.
Today, I’ll show you a nifty trick that allows you to actually see the AutoFilter criteria you have applied on an Excel table.
.
This is actually a pretty common request. When you’ve got an AutoFilter applied, it would often be nice to see how the table is actually AutoFiltered. Alas, Excel’s only accommodation is to give you a generic count of records on the status bar.
.
.
I’ve come up with a piece of code that will give you more details in the status bar. Like this:
.
.
Here is how you can implement this:
One of the best practices of dashboard building is to avoid overwhelming your customers with too much data at one time – especially in a chart, where they can lose sight of the primary message if focusing on inconsequential data. One of the common ways dashboard designers help focus the message of a chart is to limit the data labels to only the key points.
.
Once I have only two data labels, my eyes immediately take in the message of this chart (that we went from 11 to 53).
» More: Automatically Label First and Last Chart Points
While working on a dashboarding project here, I accidently developed a charting macro that will automatically set the series colors in a chart to match their source cell’s colors.
.
I’m not sure how useful this actually could be, but it’s charming enough to share with my 12 fans.
.
Let’s say I created this chart – and I want to apply specific coloring to each series without having to painstakingly change all the color options in each series.
» More: Automatically Set Chart Series Colors to Match Source Cell Colors
POLO (one of my 12 fans) asks a question:
“I want to control read and write access to an Excel file using either windows logins or a list in Excel. Like match userid to the list in Excel and then give either write or read only access to the spreadsheet. Is it possible?”
The answer is yes. You can capture a user’s Windows Domain\UserName (see my previous post on this) and throw that against a list in Excel to determine permissions and views.
» More: Automatically Unprotect Sheets Based on Domain Name
I was puttering around in Excel the other day when I noticed that the Name Box doesn’t ever show you the complete range you’ve selected.
.
For example, if I select Range A1:E8, it only shows the active cell (A1). I think it would be helpful to see some basic information on the selected range.
» More: Status Bar Fun – Show Cell Selection Info
How many times have you built a PowerPoint presentation with Excel data. We’ve all done it at some point – some of us do it on a weekly basis. The typical scenario is the weekly or monthly management review. You copy or link Excel tables and charts to a PowerPoint deck, and then refresh when it’s time to produce another exciting version of “Make the Data Look Good”.
.
In this post, I’d like to show you a technique that takes the concept of using Excel data in PowerPoint to the extreme. The basic idea is that you can build a workbook in such a way that it mimics a PowerPoint presentation. That is, the workbook is the presentation itself and each worksheet becomes a slide in the presentation.
.
Once you do that, you can easily convert that workbook into an actual PowerPoint presentation using a bit of VBA automation.
.
I’ve created a sample workbook so you can get an idea of how this works.
Download the Workbook
When developing your Excel or Access application, it’s not uncommon to need to capture your user’s domain/username for one reason or another. A user’s domain/username combination is the traditional unique identifier for a user - often captured by programmers to do things in like:
.
There are literally half a dozen ways to capture a user’s domain name and user name. In this post, I’ll give you a very easy method using the Environ function.
