How to do the impossible (almost)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
   If Target.Value > 10 Then
      MsgBox "Put your macro code here in place of the MsgBox line"
   End If
End If
End Sub

When A1 is changed to a value greater than 10, the macro code will run.

To get to the Worksheet Event code, right-click the sheet tab and choose View Code.




From CPearson.com

Also see:
Change Events

Also:
Microsoft KnowledgeBase:
How to Run a Macro When Certain Cells Change

After posting this, Ross Mclean of Methodsinexcel.co.uk came up with a great work around using a User Defined Function.

Public Function RMAC(ByVal Macro_Name As String,  _

ByVal Arg1 As Variant)

RMAC = Application.Run(Macro_Name, Arg1)

End Function

You can see the simple coding here:

Running a macro from a cell.

Thanks Ross!

Keep in mind that some commands will be ignored. A macro run from the worksheet like this will not change the Excel environment.

For example (watch line wrap), this VBA code:

Public Function RMAC _
(ByVal Macro_Name As String, _
ByVal Arg1 As Variant)
RMAC = Application.Run _
(Macro_Name, Arg1)
End Function

Sub MyMacro(arg As String)
ActiveCell.Interior.ColorIndex _
= 3
Beep
End Sub

=rmac("MyMacro","yada")

Labels: Macros, VBA

Security levels

High:

A computer user can open without a prompt a digitally signed project from a trusted publisher. Otherwise, the application blocks opening signed projects from untrusted publishers as well as unsigned projects.

Medium:

A computer user can open without a prompt a digitally signed project from a trusted publisher. In addition, you can also designate the publisher of a signed project as trusted so their projects will open without a prompt in the future. Unsigned projects are always prompted with a reminder that the file may contain potentially harmful code, but users can elect to open them anyway.

Low:

A computer user can open an unsigned project without a prompt. When users make a Low security setting, they're reminded that they aren't protected from potentially unsafe macros.

"If you've used Access 2003, you've probably seen several security warning messages - Access 2003 cares about your security. An important part of Access 2003 security is digitally signing your code. As Rick Dobson shows, you can do it, but preparing for digital signing is critical.

A digital signature acts like shrink-wrap on your project: Clients know that they're getting a copy directly from you that no one else modified. Clients will also know that they're working with "your" code and not any version of it modified by a third party. As computing moves forward into a "security conscious" era, learning how to acquire and use a digital certificate is also important for interfacing with organizations that adopt policies of only running digitally signed Access 2003 projects: Your users may refuse to accept software from you that isn't shrink-wrapped."

Also:
Signing Access 2003 Projects

Other links:

How to make sure that your Office document has a valid digital signature in 2007 Office products and in Office 2003

Also:
HAL-PC MS Office & Excel SIG in Houston, Texas:
Digital Certificates and Trusted Sources for running Excel Macros under High Macro Security



See all Topics
excel

Labels: Macros, VBA

VLOOKUP

"Excel will adjust cell references in formulas when you insert or delete rows or columns.

For example, if the cell C1 contains the formula =A1/B1 and you insert a column to the left of column A; the formula will change to =A1/C1.

The problem then occurs with VLOOKUP. Its column index number argument is a simple number, not a reference.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup

For Example:

1. Choose a blank worksheet
   
2. In cells A1 and A2, enter the values 1 and 2.
   
3. In B1 and B2, enter Jan and Feb.
   
4. Select all four cells and drag the fill handle at the bottom right-hand corner of the selection downward to row 12.

You should now have the numbers 1 through 12 in column A and the months Jan through Dec in column B.

In cell D1 enter the formula

=VLOOKUP(C1,A1:B12,2).

Now enter any number from 1 to 12 in cell C1. The formula will select the corresponding month name.

To demonstrate the problem, right-click on the heading of column B and choose Insert.

The formula changes to =VLOOKUP (D1,A1:C12,2), which returns 0.

Excel correctly changed the cell reference from C1 to D1 and expanded the lookup range to include the inserted column, but it cannot change the column index number.

Press Ctrl-Z to undo the column insertion.

The solution is to modify the formula so that the column index number is not hard-coded but instead is calculated from cell references.

You could use the COLUMN() function that returns the column number of the reference and, as in this example, compute the number of columns between the first and last columns:

=VLOOKUP (C1,A1:B12,COLUMN(B1)-COLUMN(A1)+1).

A more esthetically pleasing, or sophisticated, function might be:

COLUMNS(array)

This returns the number of columns in an array or reference.

The modified lookup function looks like this:

=VLOOKUP (C1,A1:B12,COLUMNS(A1:B1)).


Either way, now if a column is inserted in the middle of the range, the column index will be adjusted."

From a PC Magazine article
By Neil J. Rubenking

Also:

Allan Wyatt's ExcelTips:
Using the Column Function

OzGrid:
Copy Rows
(Scroll down to about the middle of the page)



See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials, VBA

Display properties

Sub CommentThem()
 Dim cell As Range
 On Error Resume Next
 Selection.ClearComments
 On Error GoTo 0
 For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
   If cell.Formula <> "" Then
    cell.AddComment
    cell.Comment.Visible = False
    On Error Resume Next 'fails on invalid formula
    cell.Comment.Text Text:=" Address:  " & cell.Address(0, 0) & Chr(10) & _
      " Value:  " & cell.Value & Chr(10) & _
      " Format:  " & cell.NumberFormat & Chr(10) & _
      " Formula: " & cell.Formula
    On Error GoTo 0
   End If
 Next cell

End Sub

by David McRitchie

Also:
Show FORMULA of another cell in Excel

[Edited entry from 3/12/2005]



See all Topics
excel

Labels: Formulas, Tips, VBA

Formatting

Sub PutCurrentDateInCenterFooterAllSheetsInWorkbook()
For Each oSheet In ActiveWorkbook.Sheets
oSheet.PageSetup.CenterFooter = Format(Now(), "dddd mmmm dd, yyyy")
Next
End Sub

You can change the word CenterFooter to CenterHeader. You could also use LeftHeader, RightHeader, LeftFooter, or RightFooter.

Microsoft KnowledgeBase:
Macro to Change the Date/Time Format in a Header/Footer



See all Topics
excel

Labels: Customize, Formats, VBA

Only what you want

Contextures.com:
AutoFilter Tips

"Some tips and techniques for working with AutoFilters, and some workarounds for problems you may encounter."

Microsoft Assistance:
All about AutoFilters

Jay Walkenbach:
Displaying AutoFilter criteria

The University of North Carolina at Charlotte:
Using Custom Views with Autofilter

"If you frequently use Autofilter to view portions of your worksheets, you might find Custom Views to be a useful tool. Custom Views can be easily set up based on your Autofilter criteria. Once that is done, the worksheet views that you have created display in a drop down list so you (or others) can select them."

Labels: Formulas, Tips

Macro control

Change the default printer

Change the default printer with a macro.

Insert headers and footers

This example macro inserts a header/footer in every worksheet in the active workbook. It also inserts the complete path to the workbook.

Print all workbooks in a folder

With these macros you can print all workbooks in a selected folder. You have more control with what is printed than you have if you do this from Windows Explorer.

Print multiple selections on one sheet

If selected multiple cell ranges is printed out on different sheets, you can use this macro example to print all the selected areas on one sheet.

Select a printer tray before printing

In Excel you don't have the opportunity to set the properties FirstPageTray or OtherPagesTray like you can in Word. It's possible to create a simple solution by using SendKeys.

Labels: Macros, Tips

List maker

1. Open Word.
   
2. Use Alt+F11 to open the Visual Basic editor.
   
3. Choose Insert>Module from the Menu.
   
4. Copy and Paste this code in the module.
   
5. Return to Word and go to: Tools>Macro>Macros.


6. Select and run "InstalledFonts".

Sub InstalledFonts()

Dim F As Integer
Dim InstalledFonts As Table

  'Open a fresh document

Set FreshDoc = Documents.Add

   'Create a table and define the header

Set InstalledFonts = FreshDoc.Tables.Add(Selection.Range, FontNames.Count + 1, 2)
With InstalledFonts
.Borders.Enable = False
.Cell(1, 1).Range.Font.Name = "Arial"
.Cell(1, 1).Range.Font.Bold = 1
.Cell(1, 1).Range.InsertAfter "Font Name"
.Cell(1, 2).Range.Font.Bold = 1
.Cell(1, 2).Range.InsertAfter "Example"
End With

   'Loop through all the fonts and add them to the table

For F = 1 To FontNames.Count
With InstalledFonts
.Cell(F + 1, 1).Range.Font.Name = "Arial"
.Cell(F + 1, 1).Range.Font.Size = 10
.Cell(F + 1, 1).Range.InsertAfter FontNames(F)
.Cell(F + 1, 2).Range.Font.Name = FontNames(F)
.Cell(F + 1, 2).Range.Font.Size = 10
.Cell(F + 1, 2).Range.InsertAfter "ABCDEFG abcdefg 1234567890 &$@"
End With
Next F
   'Sort the names of the fonts

InstalledFonts.Sort SortOrder:=wdSortOrderAscending

End Sub

Labels: Fonts

Outlook Contacts in Access

1. Choose File >Open from the menu bar.
   (Office button>Open in 2007)
   
2. Under Files Of Type choose Outlook().
   
3. Locate your Outlook PST files.
   
4. Choose Contacts, or if you have set up separate files for different groups choose an appropriate one.
   
5. The wizard walks you through the process of creating an Access database with a linked Contact table.

Labels: Lists, Tutorials

More data

An introduction to the benefits of online analytical processing (OLAP)

"Every day we create reams of data in customer relationship management applications, order entry applications, and warehouse management systems. We're drowning in a sea of data. However, even with all that data we don't have a large amount of information. We have the ones and zeros of the transactions, but we don't have the answers we need to simple questions like:

• "Why was March better than February?"
  
• "Where is the sales force having the most success?"
  
• "In what conditions does the sales team struggle with making sales?"

• Oracle 9i makes data warehousing easy to implement
  
• Seven highly effective steps to a smooth data warehouse implementation
  
• Business intelligence is just a few steps away for SAP R/3 users
  
• Resources for designing, planning, and implementing a data warehouse strategy
  
• Making the operational case for data warehousing
  
• TechRepublic Tutorial: Data warehousing defined

Also see:
OLAP Cube

Data Warehousing and OLAP
A Research-Oriented Bibliography

FAQ Excel 2007 – OLAP

Microsoft:

OLAP



See all Topics
excel

Labels: General, Lists, Tips

Project Charts

Here's an example of a Gantt chart being constructed using an open source product called GanttProject :
Flash Demo

John F. Lacher has created a nice example of how to create a Gantt chart.
This one is appropriate for scheduling rooms, resources, and time.

A sample can be downloaded and customized.
Lacher24.XLS




Also see:

Simple Gantt chart
Gantt-type chart on a worksheet using conditional formatting

And

Microsoft Knowledgebase:
How to Create a Gantt Chart Using Hours as the Scale
Visual Basic Module to Create Gantt Chart

Jon Peltier:
Gantt Chart Links

Gantt Googled

You can use Microsoft Project to create PERT and Gantt charts.



See all Topics
excel

Labels: Charts

Double click the handle

F. David McRitchie:
Fill Handle

Microsoft Office Online:
Fill data in worksheet cells

If you don't see the Fill handle:
Click Options on the Tools menu and click the Edit tab.

(Use the Office button in the upper left corner in 2007)
Select or clear the Allow cell drag and drop check box.



See all Topics
excel

Labels: General, Shortcuts, Tutorials

Group sheets

Also:

John Walkenbach:
Making an exact copy of a range of formulas

Microsoft KB:
Run a macro on multiple sheets in Group mode



See all Topics
excel

Labels: General, Tips, Tutorials, VBA

Stick it in Word

Arrange the windows so that both applications can be seen.
(Right-click an empty area of the Task bar and choose "Tile Windows Vertically."

Type your introductory text in the Word document.

Switch to PowerPoint and open the PowerPoint file.

In Slide Sorter View, hold down the Ctrl key and select the slides you want to include.

Drag the selected group of slides onto the Word document.

You will only see the first slide in the document, but if you double-click on the image, the PowerPoint show will run.

It will also work in Excel.

(This, of course assumes that the target machine has PowerPoint or PowerPoint Viewer installed)



See all Topics
excel

Labels: General, Link, Tips

If it's Tuesday, it must be mauve

Conditional formatting is one of Excel's better features. It allows you to preset certain font styles, colors, and cell-background colors based on cell values.

This can be very useful for highlighting important information and values outside an accepted range or providing a visual cue to associate value ranges with color codes.

The best part is that conditional formatting is very easy to set up.

Just click the cells you'd like to format and select Format >Conditional Formatting. The Conditional Formatting dialog box lets you set up the conditions by which the formatting of the cell will occur.

You pick the operator (between, equal to, less than, etc.) and the value or range of values. Click Format to open the Format Cells dialog box, where you can select the colors and styles to be used.

Each cell can have several conditional formats. For example, you might say that if a certain cell's value is between 20 and 50, the text should be blue on a yellow background.

However, you can format that same cell to exhibit red, bolded text on a green background if it contains a value between 51 and 100.




Before 2007, you could use up to three conditions, but earlier versions of Excel can be tricked to use more if it should become necessary.

Oz Grid:
Excel VBA Macro Code to Get Around Excel's 3 Criteria Limit in Conditional Formatting

GR Business Process Solutions:
Graham Barrow and Ray Blake
Highlight the current or past month in Excel with conditional formatting

Chip Pearson:
Conditional Formatting

Also

Compatibility in Excel 2007

Conditional Format Functions in Excel 2007

Adding Customized Rules to Excel 2007

Demo:
Data takes shape with conditional formatting



See all Topics
excel

Labels: Customize, Formats, Tips, Tutorials

Excel the way you've wanted it

You can download a 30 day trial at PUP V6 Home

Here are some features just in the area of Chart & Graphics Tools:

Chart Data Labeler: IMPROVED

Lets you apply labels (contained in a range) to a chart data series -- a feature that is surprisingly missing from Excel. Enhancement: Now ignores data that is hidden as a result of autofiltering.

Resize Charts: NEW

Makes it easy to make a chart an exact size, or make all charts the same size.

Convert Chart To Picture: NEW

Makes a copy of a chart as a static picture. This is useful for saving charts that depict various what-if scenarios.

Chart Report: NEW

Produces a detailed report that documents a chart, or produces a report that documents all charts.

Object Align, Size & Space: NEW

Back by popular demand. This utility was removed from PUP v5, but now it's back. It provides an easy way to precisely align, size, and space a group of objects or embedded charts.

Object Hide / Unhide: NEW

An interactive way to hide or unhide charts and other objects on a worksheet.

There is, of course, PUP v7 Power Utility Pak v7

You can also see his blog at J-WalkBlog.com.

The blog has little or anything to do with Excel.
It covers everything else in all the other worlds.

Labels: Addins

Formatting slip

The formula is filled down the column as long as there is data in an adjacent column.

But wait! I don't see the value. I see the formulas!
In addition, the formulas are still in lower case and the relative references have not been updated.

Easy to fix, I hear you say. Just reformat the column as General.

Nothing happens.

To fix the problem, make sure the column is formatted as General.

Select the first cell. Click in the formula bar and hit the Enter key.

Now double click the Fill handle.

(You could also use Edit>Replace to replace = with =. However, Relative references will be incorrect and unless you have reformatted the whole column as General, any new formulas will still display as text.)

The reverse also causes a problem. In a column formatted as General, enter some formulas.

Now reformat the column as Text. The formulas still work, but if you edit one of them, it reverts to a text display.

Microsoft KB:
Cell Linked to Text-Formatted Cell Shows Formula Not Value

Formulas can, of course, be toggled using CTRL+~ (Tilde)
(Though it really should be called CTRL+` (Grave Accent), since the Shift key is not used.)



See all Topics
excel

Labels: Formulas, General, Troubleshooting

Month Day, Day Month

"QDE is a fully-functional Excel Add-in that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."

Also see:

Chip Pearson:
Date and Time Entry

MathTools.net:
Time and Date

And:
Date Arithmetic




See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials

Who's counting?

2004 has 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.

Chip Pearson is the Date and Time guy:
Week Numbers In Excel

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."

The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.

Wikipedia:
Week Dates

If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.

Also this from ExcelTip.com:
Weeknumbers using VBA in Microsoft Excel

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."

In Access:
DatePart Function

If your work week is always Saturday through Friday then

datepart("ww",[DateField],7,1)

will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.

The last number sets these parameters:

1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.



See all Topics
excel

Labels: Formats, Functions, Tips

Choose your own location

Word

you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.

Excel

Tools>Options. On the "General" tab change the default location.

PowerPoint

uses Tools>Options and the "Save" tab.

Access

Tools>Options and the "General" tab for Databases and Projects

Publisher

Tools>Options "General".

Outlook

will make you take an underground tour into the Registry to change the location to save e-mail attachments.

FrontPage/Expression Web

appears to require the same sort of spelunking.

Labels: Customize, General

Free money (tracker)

Your income

Just your take-home pay, which is what you control and spend.

Cash reserve

A reserve equal to 3-6 months of take-home pay.

Savings

A portion of your income set aside to fund your retirement.

Fixed expenses

Expenses that you must pay over the course of a year.

Replacements

You also must pay to replace things that eventually wear out.

Weekly allowance

Partly discretionary, partly not; paid by cash from your pocket.

Periodic purchases

Discretionary purchases paid by check periodically.

Future purchases

Big-ticket discretionary purchases that you must save for.

Summary

A summary of your income and where it goes—and where you are.

QuickForm

You can switch to this format once you're familiar with the method.

Tips

Ideas to help pare your expenses.

Labels: General

Shake hands with a worksheet

• Add a Get Started tab to the Ribbon
  
  
• Take advantage of the Quick Access Toolbar
  
  
• Minimize the Ribbon, maximize your workspace
  
  
• Choose your own default font
  
  
• Restore missing worksheet tabs
  
  
• Freeze panes here, there, and everywhere

Labels: 2007, Tutorials

Smile!

1. Edit>Copy the Cell or Range
   
2. Choose the target cell
   
3. Hold down the Shift key and choose Edit>Paste Picture Link

If you're going to do this frequently, go to Tools>Customize. Choose the Commands tab and locate Camera in the Tools category. Drag to place the Camera button on your toolbar.

If the Camera tool is on your toolbar:

1. Select the Cell/Range
   
2. Click the Camera tool
   
3. Move the cursor to the target position and click to insert the linked picture

To create a static picture from cells, or a chart to use in another part of your workbook, or another program:

1. Clear cell gridlines if you do not want them displayed in your picture.
   (Click Options on the Tools menu, click the View tab, and then clear the Gridlines check box")
   
   
2. On the worksheet or chart sheet, select the cells or click the chart you want to copy as a picture
   
3. Hold down SHIFT and click Copy Picture on the Edit menu
   (For the best picture quality, make sure As shown on screen and Picture are selected)
   
4. Click OK
   
5. Select the worksheet or other document where you want to paste the picture
   
6. Edit>Paste.

You can use the Picture toolbar to change the image.
(to display the toolbar, right-click any toolbar and choose Picture)

To paste information you've copied from another program as a picture in Microsoft Excel, hold down SHIFT and click Paste Picture or Paste Picture Link on the Edit menu.


You can, also, create a dynamic linked text box by using the tool on the Drawing toolbar to place the object on the worksheet.

Now click to the Formula box. Type = and the click on the cell you wish to link.

Also see: Copy - Paste Methodology
by Jon Peltier

The process is a little different in 2007. Either add the Camera tool to the Quick access toolbar, or just select the range and drop down the Paste options.

Choose copy as Picture.

Next pick a location and choose Paste as picture.






See all Topics
excel

Labels: General, Link, Tips