Posts Tagged ‘VBA’

« Older Entries
Newer Entries »

Automatically Unprotect Sheets Based on Domain Name

April 13th, 2011

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

5 Comments »

Posted in Excel VBA, Worksheet Protection

Tags:

Status Bar Fun – Show Cell Selection Info

March 22nd, 2011

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

18 Comments »

Posted in Excel VBA

Tags:

Creating a PowePoint Deck in Excel

March 7th, 2011

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

. » More: Creating a PowePoint Deck in Excel

14 Comments »

Posted in Excel VBA, Visualizations

Tags:

Capture a User’s Domain\UserName

February 28th, 2011

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:

  • Help manage user login and permissions
  • Create a history of who used the application
  • Capture the name of each user who made the changes to data
  • Personalize outputs and program messages for users

.
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.

. » More: Capture a User’s Domain\UserName

10 Comments »

Posted in Access VBA, Excel VBA

Tags:

Adding Check Boxes without Form Controls

February 9th, 2011

Need to add a bunch of check boxes to your spreadsheet without messing with CheckBox Form Controls?
It’s easy with a bit of VBA code!
.

» More: Adding Check Boxes without Form Controls

16 Comments »

Posted in Excel VBA, Visualizations

Tags:

Running Crosstab Queries in Excel

February 7th, 2011

A few days ago, Kusleika posted an article that showed how to code a Dynamic Matrix in Excel. He later came back with code to achieve a matrix using ADO.

.

This inspired me to consider applying a Jet Crosstab query to an Excel Table.
» More: Running Crosstab Queries in Excel

6 Comments »

Posted in Excel VBA

Tags:

Using Flash Progress Indicators

February 3rd, 2011

Besides being an Excel and Access guru, I moonlight as an Xcelsius expert. Because Xcelsius is so closely tied to Excel, I’ve always seen Xcelsius as an extension of the Excel BI stack. But that’s a conversation for another time.

.

A while ago, Ryan Goodman created and posted a few Flash progress indicators on his Xcelsius blog. These are Flash .swf files designed to be used as loaders for Xclesius dashboards.

Â

.

I recently had an opportunity to work on a project where I could implement these loaders into an Excel dashboard as progress indicators – a lazy man’s progress bar if you will.
» More: Using Flash Progress Indicators

4 Comments »

Posted in Access VBA, Excel VBA, Visualizations

Tags:

Recordset Tricks in Excel 2 – Cascading ListBoxes

December 2nd, 2010

I’ve decided to dedicate this week to using Recordsets in Excel. Not to connect to external data sources, but to specifically using them to simplify internal Excel tasks. 

.

Today, I’ll show you how to use Recordsets to help create a set of List Boxes that cascade values.
» More: Recordset Tricks in Excel 2 – Cascading ListBoxes

8 Comments »

Posted in Excel VBA

Tags:

Recordset Tricks in Excel 1 – Filling a ComboBox with Unique Values

November 29th, 2010

I’ve decided to dedicate this week to using Recordsets in Excel. Not to connect to external data sources, but to specifically use them to simplify internal Excel tasks.

Today, I’ll show you how I typically use a Recordset to fill a ComboBox with unique values from a table.
» More: Recordset Tricks in Excel 1 – Filling a ComboBox with Unique Values

17 Comments »

Posted in Excel VBA

Tags:

For God Sake Please Use End If

November 15th, 2010

I think I’m turning into a curmudgeon. Yesterday, I was reminded how much I hate when programmers don’t use end if.  That is to say, they use the IF…THEN statement, but leave off END IF. Some of you beginners are thinking “How can you even do that”?

. » More: For God Sake Please Use End If

8 Comments »

Posted in Access VBA, Excel VBA

Tags: