Archive for April, 2010
Today’s author is Dennis Wallentin, XL-Dennis, of http://xldennis.wordpress.com/
A few weeks ago we posted a code example for creating a table of contents for your workbook.
Dennis offers another approach with this code sample for creating a TOC with hyperlinks. Dennis’ code uses the PageSetup.Pages().Count property, introduced in Excel 2007, to calculate the number of pages on each sheet. In addition, the entries in the TOC link to their respective sheets to improve on-screen workbook navigation.
Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet
Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long
Set wbBook = ActiveWorkbook
.DisplayAlerts = False
.ScreenUpdating = False
‘If the TOC sheet already exist delete it and add a new
On Error Resume Next
On Error GoTo 0
Set wsActive = wbBook.ActiveSheet
.Name = "TOC"
.Value = VBA.Array("Table of Contents", "Sheet # – # of Pages")
.Font.Bold = True
lnRow = 2
lnCount = 1
‘Iterate through the worksheets in the workbook and create
‘sheetnames, add hyperlink and count & write the running number
‘of pages to be printed for each sheet on the TOC sheet.
For Each wsSheet In wbBook.Worksheets
If wsSheet.Name <> wsActive.Name Then
.Hyperlinks.Add .Cells(lnRow, 1), "", _
SubAddress:="’" & wsSheet.Name & "’!A1", _
lnPages = wsSheet.PageSetup.Pages().Count
.Cells(lnRow, 2).Value = "’" & lnCount & "-" & lnPages
lnRow = lnRow + 1
lnCount = lnCount + 1
.DisplayAlerts = True
.ScreenUpdating = True
As we noted in the last post about inserting a screenshot, a lot of the work for Office 2010 has been about improving workflow for image insertion and editing.
I’m going to show you how to use the new Remove Background tool on pictures to make a slide like this:
The Remove Background button will isolate the foreground object in an image and remove the background elements. Here’s the original image of my skateboard:
If you click the “Remove Background” button on the Picture Tools Format tab, you’ll enter the Background Removal contextual tab:
You’ll also see that PowerPoint makes an initial guess about what to remove. In many cases this will be the end of the story, so you can just click “Keep Changes” and move on:
This all depends on the characteristics of the picture and which object you are trying to isolate. When the guess is incorrect, there are a couple of quick steps you can take to get the result you want. First, drag the rectangle to fit around the object you want:
Then, click the “Mark Areas to Remove” button and draw lines over the unwanted areas, such as the wood floor. If part of the object you want is under the purple mask, then use the “Mark Areas to Keep” to make sure they aren’t removed accidentally:
In a couple minutes you’ll have just the areas you want, and everything else in the image will become transparent. As a final step, you may wish to use the crop tool to remove transparent areas so that the bounding box fits tightly around the object. Since it’s just a picture, you can apply effects, rotation, and scale just as you would to any other picture in PowerPoint (notice how the shadows project only from the isolated skateboards):
This is a very powerful tool that opens the doors to a whole new realm of creativity. You’ll be pleased to find it in other Office apps such as Word, Excel and Outlook. Have fun.
Last week the beta of Docs for Facebook was announced at the f8 developer conference. Driven by Microsoft FUSE Labs, Docs is a great experiment on how the Office experience can come together in Facebook. Takeshi Numoto recently posted some thoughts on this, to check it out head over to The Office Blog: http://blogs.office.com/b/office_blog/archive/2010/04/27/cloud-creates-new-opportunities-for-office-thoughts-on-docs-com-for-facebook.aspx
Last week, as I was shopping on the Internet, I saw a shirt that I thought would be really great for my Dad’s birthday. Before ordering the shirt, I ran it by my Mom, just in case she had any other recommendations. So how did I get my Mom’s feedback? Usually, I just send her a link to the Web site and ask her to take a look. Recently, instead of sending her a link, I have been using the new Screenshot feature in Outlook 2010.
When I’m writing my e-mail message to my Mom, I go to the Insert tab, click the Screenshot icon, and then click Screen Clipping.
I use my mouse pointer to select the shirt and the price,
which is then automatically added into my message.
I can also resize the picture, and then send it to my Mom.
By using the Screenshot feature, I have been saving my Mom from doing extra work because she doesn’t have to open a link to see the shirt that I was looking at on the Web site. I’ve also found this handy when I’m trying to explain to my Mom how to do something on the Internet. Instead of describing on the phone what I’m seeing, I can easily take step-by-step screenshots of the current Web site that I’m browsing and then send the screenshots to her using Outlook 2010.
If you have already used Screenshots in your messages, let us know how by leaving a comment!
Outlook Program Manager
Part of our work in PowerPoint 2010 makes the process of adding and editing images easier. Many of your pictures come from your camera or the internet. Another common source is your computer screen. Taking a screenshot and pasting it into the application can be tedious, because in most cases you really only want to show a portion of the screen.
For Office 2010, we decided do something about it.
Introducing the Insert Screenshot tool. With just a couple clicks, you can insert a picture of any window, or you can take a screen clipping by dragging a marquee around the desired area. PowerPoint will then automatically place the image onto your slide:
Since the screenshot is really just a picture once it’s in PowerPoint, you can add shapes and annotations on top of it. You can even apply photo styles, corrections, and artistic effects. In this case a drop shadow is applied so that the picture appears to be floating above the slide:
You can also use the “Screen Clipping” button at the bottom of the Insert Screenshot drop-down to select any portion of the visible screen and convert that to a picture. For example, here is a screenshot of my Windows 7 taskbar:
The goal of this feature is to save you some time. It’s also available in other apps like Word and Excel. For those of you who didn’t previously know how to take a screenshot, now you have a simple solution. Enjoy!
In case you didn’t know, Britain will be electing its next government on May 6. In keeping with the spirit of the election, I am taking a look at the websites of the three main parties, starting with the incumbent, the Labour Party.
Labour Party home page
Labour’s slogan is “A future fair for all.” In case you missed it, they’ve repeated it three times on the home page. Surprisingly for what I imagine is a fairly content-heavy website, there is no site search.
Labour’s color is red, and the site does a reasonable job of using that core color, without making your eyes bleed.
I’m not sure why the header is separated from the main navigation. At first I thought it was a browser bug. I’m also not keen on the
large gray smudge graduated gray background behind the main body of the site — it just looks dirty.
The main navigation is straightforward but unimaginative, with the requisite social media icons easily accessible. It’s too bad that the “Support Us” drop down has some z-index issues with the content behind it.
The hero promo feels busy and complicated. Here is a great place for Labour to drive home its key policies through strong messaging. Instead visitors are expected to sit through minutes of tiny video.
Maybe it’s me, but I didn’t realize that the hero promo was actually a carousel and that the slides were controlled by the navigation section to its right. This appeared so disassociated with the main promo area that I thought these were simply shortcut links into the site.
As you move down the home page, the grid seems to get thrown out the window. The layout of the content boxes loses any sort of overall cohesion.
Seeing as many visitors to the website will be looking to learn more about Labour’s policies, I’m surprised that they’re listed so far down the page. I can’t believe that pics of Prime Minister Gordon Brown on Flickr are more important in helping voters to decide which way to cast their ballot.
The names of the policy links are pretty awful. “Standing up for the many” doesn’t give you much to go on, and using basically the same link for Health and Education is very unimaginative. What’s the difference between “Securing the recovery” and “New industries, future jobs?” As a visitor, I don’t have a clear idea of where either of those links will take me.
Over on the right sidebar the “How Can I Help?” section clearly outlines how you can get involved in Labour’s campaign with bold icons and clear labeling. It’s not clear to me why it’s almost immediately followed by the “Labour Doorstep” section with essentially the same links. Surely these two areas could have been combined into one?
The Facebook and Twitter social media sections further down on the right feel too large and are awkwardly implemented, especially with the lack of white space between these content areas.
Although small, the “Meet the Candidates” content box will be useless for most visitors. Unless that candidate is campaigning in your constituency, they have absolutely no relevancy to you.
I do like the sitemap-style footer, which makes it easy to access any part of the site.
Overall, the site feels rushed and lacks the polish I would expect from a major political party, especially with the experience of the 2008 US elections to draw from.
Next, the Conservatives.
Wanted to reach out to the community and find out more on how you use (or don’t use) a couple of our wizards in Access.
The Database Documenter creates a report that contains detailed data for each selected object, and then opens the report in Print Preview. For example, if you run the Database Documenter against a data entry form, the report created by the Documenter lists the properties for the form as a whole, the properties for each of the sections in the form, and the properties for any buttons, labels, text boxes, and other controls on the form, plus any code modules and user permissions that are associated with the form.
This wizard gives recommendations on how to increase the performance of your database.
What we would like to know
- How often do you use these wizards?
- How could they be more useful (What are they missing?)
- What do you like about the wizards?
- What don’t you like about the wizards?
You can contact us here.
Conditional formatting is a popular feature and is a great way to easily identify cells with a range that meet some criteria. However, users often want to create conditional formatting rules that go beyond comparing a cell’s value to a single value or a single cell reference – row or column comparisons are commonly requested operations. In this blog post, we will learn how to use relative references in conditional formatting rules to make such tasks easier.
For example, consider the spreadsheet below where we have sales data for different branches of an organization over the years 2005 to 2009:
Let’s say we want to highlight the cells where sales have decreased compared to the previous year. By default, conditional formatting inserts an absolute reference when we select another cell as a reference. (ExcellsFun has a great video that explains relative and absolute cell references). When absolute references are used in conditional formatting rules, every cell to which the conditional formatting rule is applied is compared to that single referenced cell.
Tedious Way – Lots Individual Conditional Formatting Rules
For complex conditional formatting rules, users often get around that problem by creating lots of individual rules applied to single cells. In our example, we select cell C2 and click on “Conditional Formatting -> Highlight Cell Rules -> Less Than” on the home tab. When the Less Than dialog pops up, we select the cell B2 against which we want to compare. Notice that the value is the dialog gets replaced by an absolute reference to cell B2 ($B$2).
To perform column comparisons in this fashion, the user ends up having to create a new rule for each cell and select the neighboring cell for comparison.
This small table consists of as many as 20 individual rules – each comparing the cell’s value to its neighbor on the left. Besides being a tedious task (users often automate such tasks by using subroutines), there are other problems with this approach:
- Performance hit: Having lots of individual conditional formatting rules can degrade performance.
- Difficult to Maintain: Such rules are hard to maintain. Besides having to edit multiple rules for any changes, the rules won’t adjust if you insert a new row or column.
Relative References – using a single Conditional Formatting Rule
There is an easier way to do this by using just one Conditional Formatting Rule applied to the entire range. To do this, select the entire range of cells to which you want to apply the rule (in our example: C2:F6) and click on “Conditional Formatting -> Highlight Cell Rules -> Less Than” on the home tab. When the Less Than dialog pops up, we again select the cell B2 to get an absolute reference ($B$2). However, this time we use change the reference to be a relative reference (B2).
TIP: you can use the F4 key to cycle through different types of relative references until you get B2 without the $ signs.
And there you have it; we have just one Conditional Formatting rules where every cell’s value is compared. We get exactly, the same result as before. And there’s more, the rules automatically adjust to if new rows are added in the middle of this range. For example, let us add information about a new branch in Boston.
How Relative References work in CF Rules?
In conditional formatting rules, cell references are relative to the top-left most cell in the Applies to range. To simplify things, I like to pretend as if I have only selected the top-left most cell and I am writing a formula in that particular cell. In our case, the top-left cell C2 was compared against the cell to its left (B2). Hence, all the other cells in the range were compared with the cell to their left.
The diagrams below are examples of how to setup two commonly used CF rules – row and column comparisons. The cells with blue borders indicate the range to which this CF rule is applied, the bold blue border is used to illustrate the top-left most cell in the CF range and the red border shows the cell referenced by the CF formula. The black arrow indicates the direct relationship between the top-left cell and the cell reference, while the gray arrows indicate the implied relationships that are formed as a result.
You can always verify this by using the Conditional Formatting Rules Manager (select any cell within this range and clicking on the “Conditional Formatting -> Manage Rules” on the home tab) as shown below:
Also, you can toggle between the different types of relative references to compare against a particular column or a particular row. Relative references can also be used inside formulas to create complex Conditional Formatting rules.
Anytime you need to conditional formatting using complex cell comparisons, consider using relative references to make things easier. Remember, rules are relative to the top-left most cell in the Applies To range. I would love to hear more feedback on Conditional Formatting and ideas that you might have for future versions of Excel.
Broadcast Slide Show enables you to share your presentation quickly with anyone, anywhere – all directly from PowerPoint 2010. Just open a presentation in PowerPoint, start a broadcast, and share the unique link PowerPoint provides with your audience.
Anyone listening to your presentation over the phone or on a conference call can open the link and see your slide show in their Web browser. Whenever you change slides or start an animation, their view updates automatically.
You can learn more about the feature in our introductory post, or watch this simple video:
· Is it your turn to present, and someone else’s computer is attached to the projector? Start a broadcast, send them the link to display, and control the presentation from your PC.
· Don’t have a projector or large display in your conference room? Create a broadcast and everyone can follow along on their laptops.
· Install the Office Web Apps on SharePoint and set up a private broadcast service for your own organization. As an administrator, you can create one or more broadcast services, choose who has permissions to create & view broadcasts, and even customize the message users see before starting a broadcast. Hop over to TechNet to learn more.
Try out Broadcast Slide Show in PowerPoint 2010. Download the Office 2010 Beta now.
Today’s guest writer is Kerry Westphal—our resident macro expert.
A common feature in well-designed applications is giving users the ability to quickly focus on the data they care about. A search or filter form allows users to specify criteria to limit the records returned. This also enhances performance because the entire recordset is not brought in.
A typical filter form provides unbound text boxes, combo boxes, and other controls where users can build and refine requirements for records to meet in order to be displayed on the form. Allen Browne has a great VBA example here where he uses a filter form to search for clients by name, type, city, level and/or start and end dates. In Allen’s example, the form Filter property is set to a string that is built based on user criteria, and the FilterOn action is invoked.
To accomplish the same goals using macros that run on the Web, let’s walk through an example together. This demonstrates using the BrowseTo action to swap out the subform, TempVars are used to store form criteria and If statements decide which criteria is relevant.
For tracking issues that arise at work, I have a database where I filter issues by words in the summary, person it is assigned to, and the state of their resolution. When I first browse to the form, instead of showing data, the subform is populated with a form which displays a message that says “Select a filter using the Summary, Assigned To and Resolution fields to view the open issues.”
In the example above, I have specified criteria to limit issues displayed to those with “macro” in the Summary that have been assigned to Kerry Westphal. When I click the search button my macro will run and the filtered records will show.
Before we consider all the criteria, let’s construct a simple macro that considers only the Summary criteria textbox (txtSummary). Once our macro is working, we will add in more logic.
The first thing we will do in the macro is set up the error handler using the OnError action to specify a Submacro named ErrorHandler to be called in case there is an error message to display. Next we will use SetTempVar to assign the value contained by the txtSummary control to a TempVar. If the txtSummary control contains a value, the Like operator is used with wildcards in the Where Condition argument of the BrowseTo action to show the proper records. Otherwise, the BrowseTo action will show all the records. More information about the BrowseTo action can be found here
You can grab the XML here (Note: to copy this XML to paste into the macro designer, use Page->View Source and copy the XML from there).
Now that our basic macro is tested (I did that for us and it works J), we will add another condition to see only the issues assigned to the person selected in the dropdown. We can use the same logic to filter on the Assigned To field that we used for filtering on the Summary field.
In our assignments section at the top of the macro, we will add another SetTempVar action to store the AssignedTo criteria on the form.
- If the AssignedTo criteria is not filled out, the logic we built above to consider Summary criteria for BrowseTo will be executed.
- If AssignedTo critieria is filled out but the Summary criteria is not, only AssignedTo will be considered in the BrowseTo Where Condition.
- If both AssignedTo and Summary criteria are specified, the Where Condition of BrowseTo concatenates them both.
Get the XML here.
We can do a similar check for Resolution criteria. This can continue for as many fields as we want to validate in our filter form to show as many or few records as needed. The macro is below and you can grab the XML for the entire macro here.
What macros have you been making?