Monday, June 30, 2014

Data Validation

Control input



Mark Rowlinson provides a discussion by Kid Van Ouytsel that does an excellent job explaining data validation. He has also constructs a sample workbook that you can download and play with.

Data Validation
"Data validation is a tool that can help you control the input/changes someone can make in a spreadsheet. It can help you or your users to make choices, guide them to make relevant input/changes, or restrict input to a specific type of data or structure. It can help you or your users to save time and to keep formulae working properly."

Also:

Contextures:
Excel -- Data Validation

Ozgrid:
Data Validation and Conditional Formatting

Microsoft Office Online:
Overview and samples of data validation



See all Topics

Sunday, June 29, 2014

Keyboard Shortcuts

Extra pilcrows?



Here is a list of keyboard shortcuts I once or never knew.

The one that struck me was:

Ctrl Alt K — Remove extraneous paragraph marks.

This removes doubled pilcrows (¶¶).

Word-Tips


  • Alt F6 — Swap open documents
  • Alt Shift D — Insert date
  • Alt Shift Up/Down — Move table rows or paragraphs up or down
  • Shift F5 — Go back to last position
  • Shift F3 — Change case
  • Ctrl Space — Remove direct formatting
  • Ctrl Q — Remove paragraph formatting
  • Ctrl Shift N — Apply Normal style
  • Ctrl Y — Repeat action
  • Ctrl ] — increase font size by 1pt
  • Ctrl [ — decrease font size by 1pt
  • Ctrl Shift > — Increase font to next size up
  • Ctrl Shift < — Decrease font to next size down




See all Topics

Saturday, June 28, 2014

Split Access Database

Separate tables



You don't need to keep all of your data in one file. You can split your MDB file into data and application files.

"Even if all your data is in Access itself, consider using linked tables. Store all the data tables in one MDB file - the data file - and the remaining objects (queries, forms, reports, macros, and modules) in a second MDB - the application file.
In multi-user situations, each user receives a local copy of the application file, linked to the tables in the single remote data file."

  • Maintenance: To update the program, just replace the application file.
    Since the data is in a separate file, no data is overwritten.

  • Network Traffic: Loading the entire application (forms, controls, code, etc)across the network increases traffic making your interface slower.
In some cases you will link additional files:
  • Static look-up data such as postal codes might be kept in its own file.
  • Linked temporary tables might avoid the need to compact the application file.
From Allen Brown's tips for Access users

Access has a tool to do the splitting for you, go to: Tools>Database Utilities Database Splitter
In Access 2007+:
  1. On the Database Tools tab, in the Move Data group, click Access Database.
  2. In the Database Splitter dialog box, click Split Database.
  3. Type a name for the back-end database, and then click Split.
Also:
Knowledgebase:
How to manually split a Microsoft Access database

MSDN:
About sharing an Access database on a network  



See all Topics

Friday, June 27, 2014

Backgrounds for You

A large number of designer slides


PP'ers are always on the lookout for colorful, useful backgrounds.

Camtasia Studio, a maker of video capture software has a lot of PowerPoint backgrounds that are yours for the downloading.


"Based on the popularity of our MenuMaker background templates, we've created background templates for use with Microsoft PowerPoint. These free templates are designed for use with PowerPoint presentations recorded with TechSmith's Camtasia Studio. However, they are standard templates and can be used for any PowerPoint presentation."

Camtasia Backqrounds



See all Topics

Thursday, June 26, 2014

Open to Contacts

Your choice


To create a shortcut for your Outlook Contacts folder on the desktop


  1. Right click on an open area of the desktop

  2. Select New / Shortcut

  3. Enter in the path to your outlook program followed by outlook:contacts. Note the quotes are necessary:
C:\Program Files\Microsoft Office\Office12\OUTLOOK.EXE outlook:contacts





See all Topics

Wednesday, June 25, 2014

Runtime for Access

Free Download !


Access is not included in the basic Office 2007+ suites. If you want to distribute your database projects to others in the office, you'll need this download.

The Access 2003 Developer Extensions cost about $500. The 2007-10 downloads are free!


"Microsoft Office Access 2007 provides a rich platform for developing database management solutions with easy-to-use customization tools.

If no end-user customization is required (including report modifications), you can choose to distribute those Access 2007 solutions so that they run without requiring a full installation of Access 2007.

To do so, you must package and distribute your application with the Access Runtime.

The Access Runtime is similar to previous runtimes in that all design-related UI is either removed or disabled.

You do not need to buy any special SKU in order to redistribute the Access Runtime. You can freely redistribute it or point users to this download."


Access 2007 Runtime

Access 2010 Runtime

Access 2013 Runtime



See all Topics

Tuesday, June 24, 2014

Move a Column

No cut, No Paste


With a little practice you can quickly move columns or rows.

  1. Select the entire column or row you want to move
    The move will effect whatever is selected; cells, or columns, or rows

  2. Move your mouse pointer to the edge of the selection until it changes from a normal pointer cross to an arrow (four headed arrow in 2007+).
    (Put the pointer on the selected cells, not the Row or Column indicators.)

  3. Hold down the shift key and click the left mouse button and drag the column/row to the new position.

  4. Release the mouse button before releasing the shift key




See all Topics

Monday, June 23, 2014

DOCTYPE

Customize



At the top of an HTML file source, you may see some code that indicates the version of HTML that is going to be used.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">

The O'Reilly Net.com has a discussion:
DOCTYPE Explained.

"There are three variants on HTML 4.01, for example:
  • HTML 4.01 Strict, which describes the structural portions of HTML 4.01 and does not provide any frame-related markup.
  • HTML 4.01 Transitional, which includes the presentational markup such as >B< and >FONT<, but does not include frame-related markup.
  • HTML 4.01 Frameset, which is the same as HTML 4.01 Transitional except that it adds a description of frame-related markup."
From Thomas Brunt's Outfront.net Editing the FP 2003 Default Template
"I've heard some folks complaining that FrontPage doesn't include a doctype declaration when you create a new page. If that's a problem for you then why not just edit the default template? That's the file that FP copies every time you create a new blank page. Here's the location of the file. Change it you like. C:\Program Files\Microsoft Office\Templates\1033\Pages11\normal.tem\normal.htm FP 2003 gives you a choice of doctypes to enter somewhat automatically, however. You can edit what's available and add your own stuff too."
Also:
Fix Your Site With the Right DOCTYPE!
Choosing a DOCTYPE  
The global structure of an HTML document  



See all Topics

Sunday, June 22, 2014

Fuzzy Preview

Fix the resolution


If the preview image is not clear, this Knowledgebase article may help:

Because of changes in how the preview image is saved in Microsoft Office PowerPoint 2007, in Microsoft Office PowerPoint 2003, and in Microsoft PowerPoint 2002, the preview image File Open dialog box is more difficult to read than in PowerPoint 2000 and earlier versions.


In PowerPoint 2007
  1. Click Start, click Run, type regedit, and then click OK.

  2. Locate and then click the following key in the registry:

    HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\PowerPoint\Options

  3. On the Edit menu, point to New, and then click DWORD value.

  4. Type Preview Picture Precision, and then press ENTER.

  5. On the Edit menu, click Modify.

  6. Click Decimal in the Base area.

  7. Type 320, and then click OK.

  8. Exit Registry Editor.
Support.Microsoft.com:
Preview image is fuzzy



See all Topics

Saturday, June 21, 2014

Attention Grabber

For Print Screen or lost spectacles


Those Apple type people have a gadget called "Mouseposé". You might have seen it in videos demonstrating applications. It darkens the screen and puts a spotlight on the point of interest.

Those of us who use real machines have a similar add-in.



It's not from Boinx.
It's the magnifier or "Dragnifier" from Ed Halley.



See all Topics

Friday, June 20, 2014

Indent Code

Realign a bunch


Indenting blocks of VBA code, such as statements within loops or If...Then statements, makes reading a procedure much easier.

You probably indent a code statement using the [Tab] key, and outdent by using [Shift][Tab].

However, you may not be aware that the [Tab] and [Shift][Tab] techniques also work when multiple code lines are selected.

The Visual Basic Editor also provides Indent and Outdent buttons on the Edit toolbar that allow you to easily reposition blocks of code.



See all Topics

Thursday, June 19, 2014

Convert List Numbers to Text

Pesky lists


Applying the list numbering style to paragraphs is easy. The problem is that if the style is removed, the numbers disappear as well.

The same thing is true with bullets.

The following macro will change the list numbers and LISTNUM fields to text and the bullets to a symbol font.


Sub NoAutoNum()
ActiveDocument.ConvertNumbersToText
End Sub

You can now do such things as individually format numbers and bullets.
The action is not reversible, so either use Undo right away, or use it on a copy of the original.

ConvertNumbersToText



See all Topics

Wednesday, June 18, 2014

Tips and Formulae

Functions and Macros



I'm always looking for Excel sites. A fresh perspective can make the view more clear.
While he does approach from a Mac angle, the Excel world welcomes those of all persuasions.

J.E. McGimpsey's XL Pages

Here are some of the tips:

  • Remove internal worksheet/workbook protection
  • Why your sum is a penny off...
  • Highlight row without losing color formatting
  • Why use -- in SUMPRODUCT formulae
  • Using SUMPRODUCT() to calculate variable rates and commissions
  • Three-dimensional SUMIF()s
  • Bitwise Logical Functions
  • Sampling from a range
  • Summing every Nth column or row
  • Worksheet and Workbook names using the CELL() function




See all Topics

Tuesday, June 17, 2014

Color Code Appointments

Red letter day



Outlook 2002+ allows you to assign colors to appointments and meetings.
To format a single appointment:
  1. Simply right click on the event
  2. Select Label
  3. From here you can select from a variety of pre-selected colors
Appointments can also be Automatically formatted.
  1. Right-click a meeting entry and choose Automatic Formatting...
  2. Click on Add and name the Rule.
  3. Next, click Condition and set up a Filter such as having the word "Dr" in the subject or note field.
      In the future, whenever you set up an appointment that has "Dr" in the text, the entry will be color-coded.

      New Folders

       Also see:

    1. Slipstick.com To add color coding to Calendar items
      "… if you have ten or fewer main categories that you use on a regular basis, you might want your label names to be the same as your categories. To change the default label names, go to the Edit menu from the Calendar view and click on the Labels option."
      University of Wisconsin-Eau Claire: Color Coding Appointments - 2007 




    See all Topics

Monday, June 16, 2014

Folder Tree

DOS is back


"To find out how many folders there are on your hard disk, you can open a Command Prompt and use the Tree command. You'll get a very nice looking graphical tree structure showing all the folders on your hard disk.

The only problem is that the display will scroll by your screen so fast and exceed the buffer size, so you'll never be able to see it."


Import Tree command into Word

(Open Command Prompt as Administrator. Rather than "Insert>File", open the file with Word and choose the MS-DOS format))



Tree command



See all Topics

Sunday, June 15, 2014

Loop the Beginning

Then start the show


It can be effective to have an opening segment run before the actual presentation begins.

We all know how to set up a show that will run in kiosk mode until you hit escape.

Here are instructions about how to set up the loop so that you can seamlessly start the show without an interruption.

Creating & Running an Opening Loop



See all Topics

Saturday, June 14, 2014

World War I

Color pictures


"Louis Lumière had already invented instant photographic plates and the Cinematographe when, in late 1903, he and his brother Auguste patented a new process for producing colour photographs : the Autochrome.

Before the invention of the Autochrome, colours were separated using a complex three-colour process whereby three successive exposures had to be taken and then superimposed onto each other.

Louis Lumière, however, devised a method of filtering light by using a single three-colour screen made up of millions of grains of potato starch dyed in three different colours.

This mixture was then laid out on a varnished glass plate, which would be ready for use once it was coated in a black and white emulsion.

Developing the plate entailed applying the same process as was used for black and white photographs at the time, with the impression being processed to reversal.


Institut-Lumiere.org
Here are some examples:


"It looks like a painting by impressionist Edouard Manet, but it is a real color picture, made in 1914, by Jean-Baptiste Tournassoud, Commander of the Photography and Cinematography Section of the French Army.

When the Great War broke out, in 1914, French poilu's (common soldiers) still wore their Napoleontic uniforms with red trousers. They made perfect targets.

Here are some more:
World War I Photos

The Great War



See all Topics

Friday, June 13, 2014

Access Field Highlighting

More code


This technique can also be applied to controls like option groups.

Instead of using OnGotFocus and OnLostFocus events you must use the OnEnter and OnExit events.

In addition, the control group's BackStyle property must be set to Normal to take advantage of the Windows color scheme:

Function Highlight(Stat As String) As Integer
Dim ctrl As Control
On Error Resume Next
Set ctrl = Screen.ActiveControl
If Stat = "GotFocus" Then
ctrl.BackColor = vbHighlight
ctrl.ForeColor = vbHighlightText
ElseIf Stat = "LostFocus" Then
ctrl.BackColor = vbWindowBackground
ctrl.ForeColor = vbWindowText
End If
End Function

Take advantage of global constants. Just add the following two statements to a module:

Global Const Orange = 39423
Global Const LightBlue = 16776960

Then, set the OnGotFocus and OnLostFocus events for the controls in the following format:
Private Sub controlName_GotFocus()
controlname.BackColor = Orange
End Sub
Private Sub controlName_LostFocus()
controlname.BackColor = LightBlue
End Sub




See all Topics

Thursday, June 12, 2014

Spies in the Mailbox

Who'd read my mail?



"Espionage and email: as the Pentagon discovered, the two often go hand in hand. Following the arrests of two U.S. servicemen suspected of spying, the Pentagon is broadening its investigation of possible espionage activities at Guantanamo Bay, Cuba. According to CNN, at least one of the security cases has involved suspicious emails that were discovered as part of a routine monitoring process."


OutlookPower magazine has an article on the problem:

Espionage and email: tips for managing your own email archives
By Ann James




See all Topics

Wednesday, June 11, 2014

Tuva or Bust

Richard Feynman's Last Journey


By Ralph Leighton
ISBN 0-393-32069-3
W.W.Norton & Company, Inc. 2000, 1991


Tuva or Bust

There has been a lot made of the PowerPoint contribution to the failure of the Challenger shuttle (see Edward Tufte.)

Before that was the Columbia disaster. Richard Feynman found the problem with the "O" rings, He too complained about PowerPoint like presentations:
"Then we learned about bullets — little black circles in front of phrases that were supposed to summarize things. There was one after another of these little goddamn bullets in our briefing books and on the slides."

This book however is about something altogether different.
As a stamp-collecting boy always fascinated by remote places, Nobel Prize-winning physicist Richard Feynman was particularly taken by the diamond-shaped stamps from a place called Tannu Tuva. He hoped, someday, to travel there. In 1977, Feynman and his sidekick — fellow drummer and geography enthusiast Ralph Leighton — set out to make arrangements to visit Tuva, doing noble and hilarious battle with Soviet red tape, befriending quite a few Tuvans, and discovering the wonders of Tuvan throat-singing. Their Byzantine attempts to reach Tannu Tuva would span a decade, interrupted by Feynman's appointment to the committee investigating the Challenger disaster, and his tragic struggle with the cancer that finally killed him. Tuva or Bust! chronicles the deepening friendship of two zany, brilliant strategists whose love of the absurd will delight and instruct. It is Richard Feynman's last, best adventure.



Quote
"Sure enough, occupying a notch northwest of Mongolia was a territory that could well once have had the name Tannu Tuva.
"Look at this," remarked Richard, "The capital is spelled K-Y-Z-Y-L."
"That's crazy," I said. "There's not a legitimate vowel anywhere!"
"We must go there," said Gweneth.
"Yeah!" exclaimed Richard. "A place that's spelled K-Y-Z-Y-L has got to be interesting."



More Tuva:
Tuva Movies and Sounds
The Tuva Trader
Friends of Tuva

Also:
Listen to the music of Tuva on this CD. Willie Nelson is on one track, but it does demonstrate two toned throat singing:

Tuva Throat Singing

Here's another great Tuva story:

Genghis Blues

" Paul Pena is a blind San Francisco blues singer who has played with the likes of John Lee Hooker and Jerry Garcia (he also penned "Jet Airliner," which Steve Miller covered). One night while listening to his shortwave radio, he picked up a Radio Moscow broadcast and heard the mesmerizing, gutteral sound of throat singing, which is peculiar to Tuva's region of upper Mongolian. Enthralled, he became a master of this obscure art form. Enter Friends of Tuva, a curious group that included Nobel Prize-winning physicist Richard Feynman, who likewise had become fascinated with Tuva. In 1993 they sponsored a San Francisco appearance by Tuvan singers. Pena was in the audience and met with the singers afterward. Pena so impressed the Tuvans that he was encouraged to come to Tuva and participate in its annual festival competition. Genghis Blues chronicles this incredible journey."




See all Topics

Tuesday, June 10, 2014

Location Indicator

Point to the spot


Here's a link to the code that produces conditional formatting on the fly to the cells in the current row and column.



Color banding location



See all Topics

Monday, June 09, 2014

BIOS Bias

A bootstrap script


Way down deep in the innards of a PC there lives a tiny control freak named the Basic Input/Output System.

And that's just the beginning.

"The BIOS software has a number of different roles, but its most important role is to load the operating system. When you turn on your computer and the microprocessor tries to execute its first instruction, it has to get that instruction from somewhere.
It cannot get it from the operating system because the operating system is located on a hard disk, and the microprocessor cannot get to it without some instructions that tell it how."

Here are a couple of links for more information:

How BIOS Works

Wikipedia-BIOS

What is BIOS



See all Topics

Sunday, June 08, 2014

No Black Slide

End the show


After creating a presentation, save it as a PowerPoint show. This allows you to run the show and not be faced with all the paraphernalia that was used to create it.

One negative is that at the end of the show a black slide is displayed.

To eliminate this last distraction, so that the show will run and then just return to the desktop, Go to PowerPoint Options (Tools>Options or Logo PowerPoint options in 2007+).

Remove the check from "End with black slide". Resave and carry on.



This will hold for every show until the setting is changed. It is not saved with the file.


"To force the presentation to end without the black screen on every computer, add an action button or autoshape on your last slide within the presentation.

You can now set the . . .action setting to "end show". This will force your presentation to end without displaying the final black screen and end of slide show message.

123PPT.com



See all Topics

Saturday, June 07, 2014

Learn to Draw

No match cover required


Do you want to learn how to draw? Now you can online! Learn how to draw like an artist, from a professional artist. Begin by learning the fundamentals of drawing with easy to follow interactive instruction.

Learn how to draw a person and make it actually look like the person! Take drawing people further and learn how to draw a caricature of a person!

  • DRAWING BASICS
    You don't know how to draw? Begin learning how to draw.

  • DRAWING PEOPLE
    You know how to draw, but you can't draw people.

  • DRAW CARICATURES
    You want to draw funny people, but you don't know how?

LearntoDraw.com



See all Topics