Use Wingdings to add graphics to Access forms and reports
Thursday, April 23, 2009 11:22 AM
You can insert a symbol or picture into a form or report, but embedded graphics consume a lot of resources.
Microsoft Access
Use Wingdings to add graphics to Access forms and reports
You can get a similar effect by using the Wingdings font.
This alternative is easy to implement and produces satisfactory results most of the time.
For instance, you might be surprised to learn that the phone icon in the following form is actually a combination of the Chr() function and the Wingdings font, not an embedded graphic.
To add the Wingding, do the following:
- Open the form in Design view and add a text box in the desired location.
- Enter =Chr(40) as the control's Control Source property.
- Set the following properties as follows:
- Back Style: Transparent
- Back Color: Enter the appropriate value to match the section's Back Color property.
- Special Effect: Flat
- Border Style: Transparent
- Font Size: Adjust as necessary; the example uses 20.
- Font Name: Wingdings
View the form in Form view. If the graphic isn't completely visible, return to Design view and resize the text box, accordingly.
If you don't disable the control (by setting the Enabled property to No), users can select the text box.
Depending on the effect you're after, you can combine the Back Color and other properties to change the appearance when selected. For instance, you might want the control's background color to turn red when selected. If you disable the control, users won't be able to select the text box, but Access will dim it.
This easy technique has potential:
- You can use the control's Click event to execute some action. For instance, clicking the Phone graphic might dial the current number.
- You can use a bound text box to update the Chr() function's argument and allow the record to determine the graphic.
Now, you're probably wondering which values produce specific Wingdings. Alan Wood has just what you're looking for--bookmark the page if you plan to use this technique. Use the appropriate Dec value in his chart as the Chr() function's value.
Microsoft Outlook
Quickly export Outlook e-mail items to Excel
Office makes collaborating between applications easy, which is good news when you have data in one application but you need it in another. For instance, you might want to export a list of e-mail messages about a specific project, or from a specific person, to Excel for quick sorting, formatting, or more likely, to share with a non-Office application. The good news is that the process is simple to automate--a bit of VBA code and a way to execute it gets the job done. (This article's sample code was written for Office 2003, but it should work as is or with minor adjustments in Office 2000 and 2002.)
Adding the export code
Working from Outlook, launch the Visual Basic Editor (VBE) by pressing Alt + F11. Choose Module from the Insert menu and enter the VBA code shown in Listing A. Next, reference the Excel object library. To do so, choose References from the Tools menu (while still in the VBE) and check Microsoft Excel 11.0 Object Library, as shown in Figure A. Then, click OK to return to the VBE.
Figure A: Update the library references to include Excel's object library.
Listing A: ExportToExcel()
Sub ExportToExcel()
On Error GoTo ErrHandler
Dim appExcel As Excel.Application Dim wkb As Excel.Workbook Dim wks As Excel.Worksheet Dim rng As Excel.Range Dim strSheet As String Dim strPath As String Dim intRowCounter As Integer Dim intColumnCounter As Integer Dim msg As Outlook.MailItem Dim nms As Outlook.NameSpace Dim fld As Outlook.MAPIFolder Dim itm As Object
strSheet = "OutlookItems.xls" strPath = "C:Examples\" strSheet = strPath & strSheet Debug.Print strSheet
'Select export folder
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder
'Handle potential errors with Select Folder dialog box.
If fld Is Nothing Then MsgBox "There are no mail messages to export", vbOKOnly, _ "Error" Exit Sub ElseIf fld.DefaultItemType <> olMailItem Then MsgBox "There are no mail messages to export", vbOKOnly, _ "Error" Exit Sub ElseIf fld.Items.Count = 0 Then MsgBox "There are no mail messages to export", vbOKOnly, _ "Error" Exit Sub End If
'Open and activate Excel workbook.
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
appExcel.Application.Visible = True
'Copy field items in mail folder.
For Each itm In fld.Items intColumnCounter = 1 Set msg = itm intRowCounter = intRowCounter + 1 Set rng = wks.Cells(intRowCounter, intColumnCounter) rng.Value = msg.To intColumnCounter = intColumnCounter + 1 Set rng = wks.Cells(intRowCounter, intColumnCounter) rng.Value = msg.SenderEmailAddress intColumnCounter = intColumnCounter + 1 Set rng = wks.Cells(intRowCounter, intColumnCounter) rng.Value = msg.Subject intColumnCounter = intColumnCounter + 1 Set rng = wks.Cells(intRowCounter, intColumnCounter) rng.Value = msg.SentOn intColumnCounter = intColumnCounter + 1 Set rng = wks.Cells(intRowCounter, intColumnCounter) rng.Value = msg.ReceivedTime Next itm
Set appExcel = Nothing Set wkb = Nothing Set wks = Nothing Set rng = Nothing Set msg = Nothing Set nms = Nothing Set fld = Nothing Set itm = Nothing
Exit Sub
ErrHandler: If Err.Number = 1004 Then MsgBox strSheet & " doesn't exist", vbOKOnly, _ "Error" Else MsgBox Err.Number & "; Description: ", vbOKOnly, _ "Error" End If Set appExcel = Nothing Set wkb = Nothing Set wks = Nothing Set rng = Nothing Set msg = Nothing Set nms = Nothing Set fld = Nothing Set itm = Nothing
End Sub
Using the code to export
To execute the code, position the cursor inside the procedure and press F5. Instruct your users to choose Macro from the Tools menu (inside Outlook), select Macros, select ExportToExcel in the resulting dialog box, and click Run. If they use this technique often, consider adding a button to the toolbar.
After declaring a few variables, the code displays the Select Folder dialog shown in Figure B.
Figure B: Select the folder that contains the messages you want to export.
Select a folder and click OK. Next, the code handles the following potential errors:
- The user clicks Cancel to close the Select Folder dialog box.
- The user selects a non-mail folder
- The mail folder contains no mail items
Then, the code identifies and opens an Excel workbook. In this sample code, the workbook must exist. You should update this code to accommodate your system and Excel workbook. This is also a good spot for further automating the technique by allowing users to select an existing workbook or to create a new one. For our purposes, hard coding the workbook simplifies the process.
The For Each loop is the heart of this exporting technique. There are two counters, intRowCounter and intColumnCounter. As the code inserts field values from the current message, the code updates intColumnCounter. Once the code has inserted all of the current message items, it updates intRowCounter. Without these counters, the code would write over each value in A1.
The code now inserts the first field item in A1. You can offset that by specifying a starting value for one or both counters to allow for headers or to append records instead of writing over existing values. In addition, this sample code copies only a few fields: To, SenderEmailAddress, Subject, SentOn, and ReceivedTime, as shown in Figure C.
Figure C: The macro has copied the items in the specified folder to Excel.
You can add as many fields as you need. Just be sure to include a column update statement for each field you want to copy. For instance, if you want to export the actual message text, you can add the following code:
Set rng = wks.Cells(intRowCounter, intColumnCounter) rng.Value = msg.Body intColumnCounter = intColumnCounter + 1
Note that this process doesn't transfer all the characters perfectly. You might see a few phantom characters in your Excel workbook. Also, keep in mind that the code doesn't do any formatting -- you'll have to adjust column widths and so on manually or add code to take care of the task.
If you notice that a specific field generates an error if the field is empty, use an If statement in the form:
If msg.field <> "" Then rng.Value = msg.field
to handle that error. None of the fields in the sample code poses a problem if the field is empty. The code simply leaves the appropriate cell in Excel blank.
After the Next statement and before setting all the object variables to Nothing, you might want to add code that handles the open Excel workbook in some way. For instance, you might save it and then close it.
The error handling routine is generic and simple. Be sure to test this code thoroughly and enhance it accordingly.
Microsoft Word
Disable the Insert key to prevent accidental overtyping in Word
Life is full of little annoyances, and sometimes we just have to grin and bear it. But that doesn't mean we have to put up with these little annoyances from our computer applications. For example, Word 2003 has a pesky habit of flipping to overtype mode when we're not looking. This is caused by an inadvertent touch of the Insert key on our keyboards. The functionality is a holdover from the mainframe/terminal era of computing and is not really necessary for personal computers.
To put an end to this aggravation, we're going to look at two ways to turn off the Insert key functionality in Word. (Don't worry: If you sometimes need to toggle overtype mode on and off, you'll still be able to double-click OVR in the status bar at the bottom the Word window.)
Turn it off
The first technique for turning off the Insert key overtype function comes from the Web site annoyances.org. This method uses a Word macro to circumvent the normal operation of the Insert key, which is a little heavy-handed, but it definitely works. Start by going to Tools | Macros | Record New Macro to open the dialog box shown in Figure A.
Figure A
Change the name of the new macro to something like DoesNothing and then click the Keyboard button. Click in the Press New Shortcut Key box and press the Insert key (Figure B). Click the Assign button and then click the Stop Recording button to stop recording the macro.
Figure B
Now when you press the Insert key, it does nothing.
Another way
You don't have to record a new macro to turn off the Insert key overtype functionality in Word; you can merely change the keyboard shortcut associated with the Insert key. Right-click on an empty part of any Word toolbar and choose Customize to open the dialog box shown in Figure C.
Figure C
Click on the Commands tab and then click the Keyboard button to open the Customize Keyboard dialog box (Figure D).
Figure D
In the Categories list box, choose All Commands. Then, in the Commands list box, choose Overtype. Note that the current keyboard shortcut is the Insert key. Click Insert in the Current Keys box and then click the Remove button. There you go: An inadvertent toggle of the overtype mode is impossible because there is no keyboard shortcut anymore.
If you want to retain the keyboard functionality but make it less likely that you'll accidentally toggle it on, you can assign an alternate shortcut, such as Ctrl + Shift + Insert. Instead of clicking Remove, just enter the desired shortcut and click Assign.
You may be wondering about Word 2007. Apparently, Microsoft got the message that the Insert key toggle for overtype was annoying and changed the default in 2007. The Insert mode is off.
There are currently no comments for this post.